Manual Completo de SQL

PL-SQL
Identificadores
Un identificador es un nombre que se le pone a un objeto que interviene en un

programa, que puede ser variable, constante, procedimientos, excepciones,
cursores… Debe tener un máximo de 30 caracteres que empiece siempre por una
letra, y puede contener letras, números, los símbolos $, #, _, y mayúsculas y
minúsculas indiferentemente. Los identificadores no pueden ser palabras
reservadas (SELECT, INSERT, DELETE, UPDATE, DROP).
Operadores
Operador de asignación
:= (dos puntos + igual)
Operadores aritméticos + (suma)
- (resta)
* (multiplicación)
/ (división)
** (exponente)
Operadores relacionales o de comparación
= (igual a)
<>, != (distinto de)
< (menor que)
> (mayor que)
>= (mayor o igual a)
<= (menor o igual a)
Operador de concatenación || Comentarios /* comentario de dos o más líneas */
– comentario de una línea
Variables
Las variables son nombres para procesar los elementos de los datos.
Declaración:
Nombre_variable tipo [NOT NULL] [:= valor | DEFAULT valor]
:= y DEFAULT son lo mismo. Si ponemos NOT NULL es obligatorio

inicializar la variable.
Ejemplos:
num_dep NUMBER(2) NOT NULL :=20
num_emple VARCHAR2(15) DEFAULT ‘Pedro’
También se puede definir una variable a partir de un campo mediante los

atributos %TYPE y %ROWTYPE, con esto damos el tipo y longitud a la
variable de otra variable u objeto ya definido.
%TYPE es la que se utiliza normalmente, %ROWTYPE es para claves de

registro. El NOT NULL y el valor inicial no se heredan, sólo el tipo de dato y
longitud de ese dato.
Por ejemplo:
num_dep emple.dept_no%TYPE
Constantes
Las constantes son como las variables pero no puede modificarse su valor. Se
declaran de la siguiente manera:
nombre_constante CONSTANT tipo_de_dato := valor
Por ejemplo, el IVA es un valor fijo, y para declararlo lo haríamos de la siguiente

manera:
Imp_iva constant number(2,2) := 12,5
Funciones integradas de PL/SQL
PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A

continuación vamos a ver algunas de las más utilizadas.
SYSDATE
Devuelve la fecha del sistema:
SELECT SYSDATE FROM DUAL;
NVL
Devuelve el valor recibido como parámetro en el caso de que expresión sea
NULL,o expresión en caso contrario.
NVL(<expresion>, <valor>)
El siguiente ejemplo devuelve 0 si el precio es nulo, y el precio cuando está

informado:
SELECT CO_PRODUCTO, NVL(PRECIO, 0) FROM PRECIOS;
DECODE
Decode proporciona la funcionalidad de una sentencia de control de flujo if-

elseif-else.
DECODE(<expr>, <cond1>, <val1>[, ..., <condN>, <valN>],

<default>)
Esta función evalúa una expresión “<expr>”, si se cumple la primera condición

“<cond1>” devuelve el valor1 “<val1>”, en caso contrario evalúa la siguiente
condición y así hasta que una de las condiciones se cumpla. Si no se cumple
ninguna condición se devuelve el valor por defecto.
Es muy común escribir la función DECODE identada como si se tratase de un

bloque IF.
SELECT
DECODE
(co_pais, /* Expresion a evaluar */
‘ESP’, ‘ESPAÑA’, /* Si co_pais = ‘ESP’ ==> ‘ESPAÑA’ */
‘MEX’, ‘MEXICO’, /* Si co_pais = ‘MEX’ ==> ‘MEXICO’ */
‘PAIS ‘||co_pais)/* ELSE ==> concatena */

FROM PAISES;
TO_DATE
Convierte una expresión al tipo fecha. El parámetro opcional formato indica el

formato de entrada de la expresión no el de salida.
TO_DATE(<expresion>, [<formato>])
En este ejemplo convertimos la expresion ’01/12/2006′ de tipo CHAR a una
fecha (tipo DATE). Con el parámetro formato le indicamos que la fecha está
escrita como día-mes-año para que devuelve el uno de diciembre y no el doce de
enero.
SELECT TO_DATE(’01/12/2006′,
‘DD/MM/YYYY’)
FROM DUAL;
Este otro ejemplo muestra la conversión con formato de día y hora.
SELECT TO_DATE(’31/12/2006 23:59:59′,
‘DD/MM/YYYY HH24:MI:SS’)
FROM DUAL;
TO_CHAR
Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el

formato de salida de la expresión.
TO_CHAR(<expresion>, [<formato>])
SELECT
TO_CHAR(SYSDATE, ‘DD/MM/YYYYY’)
FROM DUAL;
TO_NUMBER
Convierte una expresion alfanumérica en numerica. Opcionalmente podemos

especificar el formato de salida.
TO_NUMBER(<expresion>, [<formato>])
SELECT TO_NUMBER (’10′)

FROM DUAL;
TRUNC
Trunca una fecha o número.

Si el parámetro recibido es una fecha elimina las horas, minutos y segundos de
la misma.
SELECT TRUNC(SYSDATE)FROM DUAL;
Si el parámetro es un número devuelve la parte entera.
SELECT TRUNC(9.99)FROM DUAL;
LENGTH
Devuelve la longitud de un tipo CHAR.
SELECT
LENGTH(‘HOLA MUNDO’)FROM DUAL;
INSTR
Busca una cadena de caracteres dentro de otra. Devuelve la posicion de la

ocurrencia de la cadena buscada.
Su sintaxis es la siguiente:
INSTR(<char>, <search_string>, <startpos>, <occurrence> )
SELECT
INSTR(‘AQUI ES DONDE SE BUSCA’, ‘BUSCA’, 1, 1 )
FROM DUAL;
REPLACE
Reemplaza un texto por otro en un expresion de busqueda.
REPLACE(<expresion>, <busqueda>, <reemplazo>)
El siguiente ejemplo reemplaza la palabra ‘HOLA’ por ‘VAYA’ en la cadena

‘HOLA MUNDO’.
SELECT REPLACE (‘HOLA MUNDO’,‘HOLA’, ‘VAYA’)– devuelve

VAYA MUNDO
FROM DUAL;
SUBSTR
Obtiene una parte de una expresion, desde una posición de inicio hasta una
determinada longitud.
SUBSTR(<expresion>, <posicion_ini>, <longitud> )
SELECT SUBSTR(‘HOLA MUNDO’, 6, 5) – Devuelve MUNDO

FROM DUAL;
UPPER
Convierte una expresion alfanumerica a mayúsculas.
SELECT UPPER(‘hola mundo’) – Devuelve HOLA MUNDO

FROM DUAL;
LOWER
Convierte una expresion alfanumerica a minúsculas.
SELECT LOWER(‘HOLA MUNDO’) – Devuelve hola mundo

FROM DUAL;
ROWIDTOCHAR
Convierte un ROWID a tipo caracter.
SELECT ROWIDTOCHAR(ROWID)
FROM DUAL;
RPAD
Añade N veces una determinada cadena de caracteres a la derecha una

expresión. Muy util para generar ficheros de texto de ancho fijo.
RPAD(<expresion>, <longitud>, <pad_string> )
El siguiente ejemplo añade puntos a la expresion ‘Hola mundo’ hasta alcanzar

una longitud de 50 caracteres.
SELECT
RPAD(‘Hola Mundo’, 50, ‘.’)
FROM DUAL;
LPAD
Añade N veces una determinada cadena de caracteres a la izquierda de una

expresión. Muy util para generar ficheros de texto de ancho fijo.
LPAD(<expresion>, <longitud>, <pad_string> )
El siguiente ejemplo añade puntos a la expresion ‘Hola mundo’ hasta alcanzar

una longitud de 50 caracteres.
SELECT
LPAD(‘Hola Mundo’, 50, ‘.’)
FROM DUAL;
RTRIM
Elimina los espacios en blanco a la derecha de una expresion
SELECT
RTRIM (‘Hola Mundo ‘)
FROM DUAL;
LTRIM
Elimina los espacios en blanco a la izquierda de una expresion
SELECT
LTRIM (‘ Hola Mundo’)
FROM DUAL;
TRIM
Elimina los espacios en blanco a la izquierda y derecha de una expresion
SELECT
TRIM (‘ Hola Mundo ‘)
FROM DUAL;
MOD
Devuelve el resto de la división entera entre dos números.
MOD(<dividendo>, <divisor> )
SELECT MOD(20,15) – Devuelve el modulo de dividir
20/15
FROM DUAL
Bloque PL/SQL
Bloque es la unidad de estructura básica en los programas PL/SQL. Supone una

mejora en el rendimiento, pues se envían los bloques completos al servidor para
ser procesados en lugar de enviar cada secuencia SQL.
Partes de un bloque:
 Zona de declaraciones: zona opcional. Se declaran los objetos locales

(variables, constantes…).
 Zona de instrucciones: zona obligatoria.
 Zona de tratamiento de excepciones: zona opcional. Se tratan
excepciones en el programa.
Forma de crear un bloque:
[ DECLARE | IS / AS ]
<declaraciones>
BEGIN
<instrucciones>
[ EXCEPTION ]
<tratamiento de excepciones>
END;
La barra “/” siempre se pone al final para ejecutar el bloque.
Excepciones en PL/SQL
En PL/SQL una advertencia o condición de error es llamada una excepción.
Las excepciones se controlan dentro de su propio bloque.La estructura de

bloque de una excepción se muestra a continuación.
DECLARE
– Declaraciones
BEGIN
– Ejecucion
EXCEPTION
– Excepcion
END;
Cuando ocurre un error, se ejecuta la porción del programa marcada por el

bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias.
El siguiente ejemplo muestra un bloque de excepciones que captura las

excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion
será capturada en el bloque WHEN OTHERS THEN.
DECLARE
– Declaraciones
BEGIN
– Ejecucion
EXCEPTION
WHEN NO_DATA_FOUND THEN
– Se ejecuta cuando ocurre una excepcion de tipo
NO_DATA_FOUND
WHEN ZERO_DIVIDE THEN
– Se ejecuta cuando ocurre una excepcion de tipo
ZERO_DIVIDE
WHEN OTHERS THEN

– Se ejecuta cuando ocurre una excepcion de un tipo no tratado
– en los bloques anteriores
END;
Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque

EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez
finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el
bloque anterior.
Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta

dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo
de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si
existe!). WHEN OTHERS debe ser el último manejador de excepciones.
Las excepciones pueden ser definidas en forma interna o explícitamente por el

usuario. Ejemplos de excepciones definidas en forma interna son la división por
cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones
excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos:
ZERO_DIVIDE y STORAGE_ERROR.
Las excepciones definidas por el usuario deben ser alcanzadas explícitamente

utilizando la sentencia RAISE.
Con las excepciones se pueden manejar los errores cómodamente sin necesidad
de mantener múltiples chequeos por cada sentencia escrita. También provee
claridad en el código ya que permite mantener las rutinas correspondientes al
tratamiento de los errores de forma separada de la lógica del negocio.
Excepciones predefinidas
PL/SQL proporciona un gran número de excepciones predefinidas que permiten

controlar las condiciones de error más habituales.
Las excepciones predefinidas no necesitan ser declaradas. Simplemente se

utilizan cuando estas son lanzadas por algún error determinado.
La siguiente es la lista de las excepciones predeterminadas por PL/SQL y una

breve descripción de cuándo son accionadas:
Excepcion
Se ejecuta … SQLCODE
El programa intentó
ACCESS_INTO_NULL asignar valores a los
-6530
atributos de un objeto no
inicializado
COLLECTION_IS_NULL asignar valores a una
-6531
tabla anidada aún no
inicializada
El programa intentó abrir

un cursor que ya se
encontraba abierto.
Recuerde que un cursor
CURSOR_ALREADY_OPEN
de ciclo FOR -6511
automáticamente lo abre
y ello no se debe
especificar con la
sentencia OPEN
almacenar valores
duplicados en una
DUP_VAL_ON_INDEX
columna que se mantiene -1
con restricción de
integridad de un índice
único (unique index)
INVALID_CURSOR
El programa intentó -1001
efectuar una operación
no válida sobre un cursor
En una sentencia SQL, la

conversión de una
cadena de caracteres
INVALID_NUMBER
hacia un número falla -1722
cuando esa cadena no
representa un número
válido
LOGIN_DENIED conectarse a Oracle con
-1017
un nombre de usuario o
password inválido
Una sentencia SELECT

INTO no devolvió
NO_DATA_FOUND valores o el programa
100
referenció un elemento
no inicializado en una
tabla indexada
El programa efectuó una

NOT_LOGGED_ON
llamada a Oracle sin -1012
estar conectado
PROGRAM_ERROR PL/SQL tiene un

-6501
problema interno
Los elementos de una

asignación (el valor a
asignar y la variable que
lo contendrá) tienen
ROWTYPE_MISMATCH tipos incompatibles.
-6504
También se presenta este
error cuando un
parámetro pasado a un
subprograma no es del
tipo esperado
El parámetro SELF (el

SELF_IS_NULL primero que es pasado a
-30625
un método MEMBER)
es nulo
STORAGE_ERROR La memoria se terminó o

-6500
está corrupta
El programa está
tratando de referenciar
un elemento de un
arreglo indexado que se
SUBSCRIPT_BEYOND_COUNT
encuentra en una -6533
posición más grande que
el número real de
elementos de la
colección
El programa está
referenciando un
elemento de un arreglo
SUBSCRIPT_OUTSIDE_LIMIT
utilizando un número -6532
fuera del rango
permitido (por ejemplo,
el elemento “-1″)
La conversión de una
cadena de caracteres
SYS_INVALID_ROWID
hacia un tipo rowid falló -1410
porque la cadena no
representa un número
Se excedió el tiempo
TIMEOUT_ON_RESOURCE
máximo de espera por un -51
recurso en Oracle
Una sentencia SELECT

TOO_MANY_ROWS
INTO devuelve más de -1422
una fila
Ocurrió un error
aritmético, de conversión
o truncamiento. Por
VALUE_ERROR ejemplo, sucede cuando
-6502
se intenta calzar un valor
muy grande dentro de
una variable más
pequeña
ZERO_DIVIDE
efectuar una división por -1476
cero
Excepciones definidas por el usuario

PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser
declaradas y lanzadas explícitamente utilizando la sentencia RAISE.
Las excepciones deben ser declaradas en el segmento DECLARE de un bloque,

subprograma o paquete. Se declara una excepción como cualquier otra variable,
asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto
sobre variables como sobre las excepciones.
DECLARE
– Declaraciones
MyExcepcion EXCEPTION;
BEGIN
– Ejecucion
EXCEPTION
– Excepcion
END;
Reglas de Alcance
Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o

programa donde ha sido declarada. Las excepciones predefinidas son siempre
válidas.
Como las variables, una excepción declarada en un bloque es local a ese bloque
y global a todos los sub-bloques que comprende.
La sentencia RAISE
La sentencia RAISE permite lanzar una excepción en forma explícita.

Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del
alcance de la excepción.
DECLARE
– Declaramos una excepcion identificada por
VALOR_NEGATIVO
VALOR_NEGATIVO EXCEPTION;
valor NUMBER;
BEGIN
– Ejecucion
valor := -1;
IF
valor < 0
THEN
RAISE VALOR_NEGATIVO;
END IF;
EXCEPTION
– Excepcion
WHEN VALOR_NEGATIVO THEN
dbms_output.put_line(‘El valor no puede ser

negativo’);
END;
Con la sentencia RAISE podemos lanzar una excepción definida por el usuario
o predefinida, siendo el comportamiento habitual lanzar excepciones definidas
por el usuario.
Recordar la existencia de la excepción OTHERS, que simboliza cualquier

condición de excepción que no ha sido declarada. Se utiliza comúnmente para
controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común
observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o
alguna de las funciones SQLCODE – SQLERRM, que se detallan en el
próximo punto.
Uso de SQLCODE y SQLERRM
Al manejar una excepción es posible usar las funciones predefinidas SQLCode

y SQLERRM para aclarar al usuario la situación de error acontecida.
SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de

exito al ejecutarse una sentencia SQL.
Por otra parte, SQLERRM devuelve el correspondiente mensaje de error.
Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones,

para aclarar el significado de la excepción OTHERS.
Estas funciones no pueden ser utilizadas directamente en una sentencia SQL,

pero sí se puede asignar su valor a alguna variable de programa y luego usar esta
última en alguna sentencia.
DECLARE
err_num NUMBER;
err_msg VARCHAR2(255);
result NUMBER;
BEGIN
SELECT 1/0 INTO result

FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM;
DBMS_OUTPUT.put_line(‘Error:’||TO_CHAR(err_num));
DBMS_OUTPUT.put_line(err_msg);
END;
También es posible entregarle a la función SQLERRM un número negativo que

represente un error de Oracle y ésta devolverá el mensaje asociado.
DECLARE
msg VARCHAR2(255);
BEGIN
msg := SQLERRM(-1403);
DBMS_OUTPUT.put_line(MSG);
END;
Excepciones personalizadas en PL/SQL
RAISE_APPLICATION_ERROR
En ocasiones queremos enviar un mensaje de error personalizado al producirse

una excepción PL/SQL.
Para ello es necesario utilizar la instruccion
RAISE_APPLICATION_ERROR;
La sintaxis general es la siguiente:
RAISE_APPLICATION_ERROR(<error_num>,<mensaje>);
Siendo:
 error_num es un entero negativo comprendido entre -20001 y -20999

 mensaje la descripcion del error
DECLARE
v_div NUMBER;
BEGIN
SELECT 1/0 INTO v_div FROM DUAL;
EXCEPTION
WHEN
OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001,‘No se puede dividir por
cero’);
END;
Cursores en PL/SQL
Introducción a cursores PL/SQL
PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es

un conjunto de registros devuelto por una instrucción SQL. Técnicamente los
cursores son fragmentos de memoria que reservados para procesar los resultados
de una consulta SELECT.
Podemos distinguir dos tipos de cursores:
 Cursores implicitos. Este tipo de cursores se utiliza para operaciones

SELECT INTO. Se usan cuando la consulta devuelve un único registro.
 Cursores explicitos. Son los cursores que son declarados y controlados
por el programador. Se utilizan cuando la consulta devuelve un conjunto
de registros. Ocasionalmente también se utilizan en consultas que
devuelven un único registro por razones de eficiencia. Son más rápidos.
Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de

acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no
necesitan declaración.
El siguiente ejemplo declara un cursor explicito:
declare
cursor c_paises is
SELECT CO_PAIS, DESCRIPCION
FROM PAISES;
begin
/* Sentencias del bloque …*/
end;
Para procesar instrucciones SELECT que devuelvan más de una fila, son
necesarios cursores explicitos combinados con un estructura de bloque.
Un cursor admite el uso de parámetros. Los parámetros deben declararse junto
con el cursor.
El siguiente ejemplo muestra la declaracion de un cursor con un parámetro,

identificado por p_continente.
declare
cursor c_paises (p_continente IN

VARCHAR2) is
SELECT CO_PAIS, DESCRIPCION
FROM PAISES
WHERE CONTINENTE = p_continente;

begin
/* Sentencias del bloque …*/
end;
El siguiente diagrama representa como se procesa una instrucción SQL a través

de un cursor.
Fases para procesar una instrucción SQL
Cursores Implicitos en PL/SQL
Declaración de cursores implicitos.
Los cursores implicitos se utilizan para realizar consultas SELECT que

devuelven un único registro.
Deben tenerse en cuenta los siguientes puntos cuando se utilizan cursores

implicitos:
 Con cada cursor implicito debe existir la palabra clave INTO.
Las variables que reciben los datos devueltos por el cursor tienen que contener el mismo
tipo de dato que las columnas de la tabla.
Los cursores implicitos solo pueden devolver una única fila. En caso de que se
devuelva más de una fila (o ninguna fila) se producirá una excepcion. No se
preocupe si aún no sabe que es una excepcion, le valdrá conocer que es el medio
por el que PL/SQL gestiona los errores.
El siguiente ejemplo muestra un cursor implicito:
declare
vdescripcion VARCHAR2(50);
begin
SELECT DESCRIPCION
INTO vdescripcion
from PAISES
WHERE CO_PAIS = ‘ESP’;
dbms_output.put_line(‘La lectura del cursor es: ‘ || vdescripcion);
end;
La salida del programa generaría la siguiente línea:
La lectura del cursor es: ESPAÑA
Excepciones asociadas a los cursores implicitos.
Los cursores implicitos sólo pueden devolver una fila, por lo que pueden
producirse determinadas excepciones. Las más comunes que se pueden
encontrar son no_data_found y too_many_rows. La siguiente tabla
explica brevemente estas excepciones.
Excepcion Explicacion
NO_DATA_FOUND Se produce cuando una sentencia SELECT

intenta recuperar datos pero ninguna fila
satisface sus condiciones. Es decir, cuando “no
hay datos”
Dado que cada cursor implicito sólo es capaz de

TOO_MANY_ROWS recuperar una fila , esta excepcion detecta la
existencia de más de una fila.
Cursores Explicitos en PL/SQL
Declaración de cursores explicitos
Los cursores explicitos se emplean para realizar consultas SELECT que

pueden devolver cero filas, o más de una fila.
Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:
Declarar el cursor.
 Abrir el cursor con la instrucción OPEN.

 Leer los datos del cursor con la instrucción FETCH.
 Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
Para declarar un cursor debemos emplear la siguiente sintaxis:
CURSOR
nombre_cursor
IS
instrucción_SELEC
T
También debemos declarar los posibles parametros que requiera el cursor:
CURSOR
nombre_cursor(param1 tipo1, …, paramN
tipoN)
IS
instrucción_SELECT
Para abrir el cursor
OPEN nombre_cursor;
o bien (en el caso de un cursor con parámetros)
OPEN nombre_cursor(valor1, valor2, …, valorN);
Para recuperar los datos en variables PL/SQL.
FETCH
nombre_cursor
INTO lista_variables;
– o bien …
FETCH
nombre_cursor
INTO registro_PL/SQL;
Para cerrar el cursor:
CLOSE nombre_cursor;
El siguiente ejemplo ilustra el trabajo con un cursor explicito. Hay que

tener en cuenta que al leer los datos del cursor debemos hacerlo sobre
variables del mismo tipo de datos de la tabla (o tablas) que trata el cursor.
DECLARE
CURSOR cpaises
IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
co_pais VARCHAR2(3);
descripcion VARCHAR2(50);
continente VARCHAR2(25);
BEGIN
OPEN cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
CLOSE cpaises;
END;
Podemos simplificar el ejemplo utilizando el atributo de tipo

%ROWTYPE sobre el cursor.
DECLARE
CURSOR cpaises
IS
SELECT
CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
registro cpaises%ROWTYPE;
BEGIN
OPEN cpaises;
FETCH cpaises INTO registro;
CLOSE cpaises;
END;
El mismo ejemplo, pero utilizando parámetros:
DECLARE
CURSOR cpaises (p_continente VARCHAR2)
IS
SELECT
FROM PAISES
WHERE CONTINENTE = p_continente;
registro cpaises%ROWTYPE;
BEGIN
OPEN cpaises(‘EUROPA’);
FETCH cpaises INTO registro;
CLOSE cpaises;
END;
Cuando trabajamos con cursores debemos considerar:
 Cuando un cursor está cerrado, no se puede leer.

 Cuando leemos un cursor debemos comprobar el resultado de la lectura
utilizando los atributos de los cursores.
 Cuando se cierra el cursor, es ilegal tratar de usarlo.
 Es ilegal tratar de cerrar un cursor que ya está cerrado o no ha sido
abierto
Atributos de cursores
Toman los valores TRUE, FALSE o NULL dependiendo de la situación:
Antes de Durante la Al finalizar la Después de

Atributo Al abrir
abrir recuperación recuperación cerrar
%NOTFOUND ORA- NULL FALSE TRUE ORA-1001
1001
ORA-
%FOUND NULL TRUE FALSE ORA-1001
1001
%ISOPEN FALSE TRUE TRUE TRUE FALSE
ORA-
%ROWCOUNT 0 * ** ORA-1001
1001
* Número de registros que ha recuperado hasta el momento

** Número de total de registros
Manejo del cursor
Por medio de ciclo LOOP podemos iterar a través del cursor. Debe tenerse
cuidado de agregar una condición para salir del bucle:
Vamos a ver varias formas de iterar a través de un cursor. La primera es

utilizando un bucle LOOP con una sentencia EXIT condicionada:
OPEN nombre_cursor;
LOOP
FETCH nombre_cursor INTO lista_variables;
EXIT
WHEN nombre_cursor%NOTFOUND;
/* Procesamiento de los registros recuperados */
END LOOP;
Aplicada a nuestro ejemplo anterior:
DECLARE
CURSOR cpaises
IS
SELECT
FROM
PAISES;
BEGIN
OPEN cpaises;
LOOP
EXIT
WHEN cpaises%NOTFOUND;
dbms_output.put_line(descripcion);
END
LOOP;
CLOSE cpaises;
END;
Otra forma es por medio de un bucle WHILE LOOP. La instrucción FECTH

aparece dos veces.
OPEN nombre_cursor;
WHILE nombre_cursor%FOUND
LOOP
END LOOP;
DECLARE
CURSOR cpaises
IS
SELECT
FROM PAISES;
BEGIN
OPEN cpaises;
WHILE cpaises%found
LOOP
dbms_output.put_line(descripcion);
END
LOOP;
CLOSE cpaises;
END;
Por último podemos usar un bucle FOR LOOP. Es la forma más corta ya que el
cursor es implicitamente se ejecutan las instrucciones OPEN, FECTH y CLOSE.
FOR variable IN nombre_cursor LOOP
END LOOP;
BEGIN
FOR REG IN (SELECT * FROM

PAISES)
LOOP
dbms_output.put_line(reg.descripcion);
END
LOOP;
END;
Cursores de actualización en PL/SQL
Declaración y utiización de cursores de actualización.
Los cursores de actualización se declarán igual que los cursores explicitos,

añadieno FOR UPDATE al final de la sentencia select.
CURSOR
nombre_cursor
IS
instrucción_SELEC
T
FOR UPDATE
Para actualizar los datos del cursor hay que ejecutar una sentencia UPDATE
especificando la clausula WHERE CURRENT OF <cursor_name>.
UPDATE <nombre_tabla> SET

<campo_1> = <valor_1>
[,<campo_2> = <valor_2>]
WHERE CURRENT OF
<cursor_name>
El siguiente ejemplo muestra el uso de un cursor de actualización:
DECLARE
CURSOR cpaises IS
select CO_PAIS, DESCRIPCION, CONTINENTE
from paises
FOR UPDATE;
BEGIN
OPEN cpaises;
WHILE cpaises%found
LOOP
UPDATE PAISES
SET CONTINENTE = CONTINENTE || ‘.’
WHERE CURRENT OF cpaises;

END
LOOP;
CLOSE cpaises;
COMMIT;
END;
Cuando trabajamos con cursores de actualización debemos tener en cuenta las

siguientes consideraciones:
Los cursores de actualización generan bloqueos en la base de datos.
Bloques Anonimos (sin nombre)
Siempre comienza con DECLARE o directamente con BEGIN.
Ejemplo 1:
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Hola’);
END;
DBMS_OUTPUT es un depurador de Oracle que sirve para visualizar cualquier

cosa, pero antes lo debemos tener activado:
SET SERVEROUTPUT ON;
Ejemplo 2:
DECLARE
v_precio number;
BEGIN
select pvp into v_precio
from tarticulos
where codigo=100;
dbms_output.put_line (v_precio);
END;
Ejemplo 3:
El siguiente bloque anónimo nos muestra la fecha actual con el formato “Martes,
18 de Marzo de 1998, a las 13:04:55″.
DECLARE
fecha date;
BEGIN
select sysdate into fecha from dual;
dbms_output.put_line (to_char(sysdate,
‘day”, “dd” de “month” de “yyyy”, a las “hh24:mi:ss’));
END;
Subprogramas
Se pueden almacenar en la base de datos.

Existen dos tipos de subprogramas:
Procedimientos
Los procedimientos tienen la utilidad de fomentar la reutilización de programas

que se usan comúnmente. Una vez compilado, queda almacenado en la base de
datos y puede ser utilizado por múltiples aplicaciones.
La sintaxis es la siguiente
CREATE [OR REPLACE] PROCEDURE nombre_procedimiento
[nombre_parametro modo tipodatos_parametro ]
IS | AS
bloque de código
Donde “modo” puede contener los valores IN, OUT, IN OUT. Por defecto tiene
el valor IN si no se pone nada. IN indica que el parámetro es de entrada y no se
podrá modificar. OUT indica que el parámetro es de salida con lo que el
procedimiento devolverá un valor en él. IN OUT indica que el parámetro es de
entrada/salida. Con lo que al llamar al procedimiento se le dará un valor que
luego podrá ser modificado por el procedimiento y devolver este nuevo valor.
“tipodatos_parametro indica el tipo de datos que tendrá el parámetro según lo

indicado en Tipos de datos Oracle/PLSQL
Para borrar un procedimiento almacenado de la base de datos
DROP PROCEDURE nombre_procedimiento
Para utilizar un procedimiento almacenado de la base de datos
Simplemente se lo llama desde un bloque anónimo (desde la línea de

comandos), previamente habiendo inicializado el/los parametro/s (en caso que
existan).
DECLARE
nombre_parametro tipodatos_parametro;
BEGIN
nombre_parametro tipodatos_parametro :=
valor_de_inicializacion;
nombre_procedimiento (nombre_parametro =>

nombre_parametro);
END;
/
Funciones
Una función es un bloque de código PL/SQL que tiene las mismas

características que un procedimiento almacenado. La diferencia estriba que una
función devuelve un valor al retornar. Al devolver un valor puede ser llamada
como parte de una expresión.
La sintaxis sería
CREATE [OR REPLACE] FUNCTION nombre_función
[nombre_parámetro modo tipodatos_parametro ]
RETURN tipodatos_retorno IS | AS
bloque de código
Donde “modo” puede contener los valores IN, OUT, IN OUT. Por defecto tiene
el valor IN si no se pone nada. IN indica que el parámetro es de entrada y no se
podrá modificar. OUT indica que el parámetro es de salida con lo que el
procedimiento devolverá un valor en él. IN OUT indica que el parámetro es de
entrada/salida. Con lo que al llamar al procedimiento se le dará un valor que
luego podrá ser modificado por el procedimiento y devolver este nuevo valor.
Sin embargo, en este caso solo tendría sentido( por el concepto de función en sí
mismo) declarar parámetros del tipo IN y devolver el valor como retorno de la
función.
“tipodatos_parametro” y “tipodatos_retorno” indican el tipo de datos que tendrá

el parámetro y el valor de retorno de la función respectivamente según lo
indicado en Tipos de datos Oracle/PLSQL
Para borrar una función de la base de datos
DROP FUNCTION nombre_función
Los procedimientos y funciones se pueden agrupar en unas estructuras llamadas

Paquetes
Paquetes
Los paquetes se utilizan para agrupar procedimiento y funciones,
Ejemplo de declaración de un Paquete de base de datos:

CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition ...]
[record_type_definition ...]
[subtype_definition ...]
[collection_declaration ...]
[constant_declaration ...]
[exception_declaration ...]
[object_declaration ...]
[record_declaration ...]
[variable_declaration ...]
[cursor_spec ...]
[function_spec ...]
[procedure_spec ...]
[call_spec ...]
[PRAGMA RESTRICT_REFERENCES(assertions) ...]
END [package_name];
[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition ...]
[record_type_definition ...]
[subtype_definition ...]
[collection_declaration ...]
[constant_declaration ...]
[exception_declaration ...]
[object_declaration ...]
[record_declaration ...]
[variable_declaration ...]
[cursor_body ...]
[function_spec ...]
[procedure_spec ...]
[call_spec ...]
[BEGIN
sequence_of_statements]
END [package_name];]
Para Borrar un paquete:
DROP PACKAGE nombre_paquEte;
DROP PACKAGE BODY nombre_paquEte; –Elimina el cuerpo del

paquete
El uso de OR REPLACE permite sobreescribir un paquete existente. Si se omite,

y el paquete existe, se producirá, un error.
Disparadores (Triggers)
Declaración de triggers
Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como

consecuencia de una determinada instrucción SQL (una operación DML:
INSERT, UPDATE o DELETE) sobre dicha tabla.
La sintaxis para crear un trigger es la siguiente:
CREATE [OR REPLACE] TRIGGER <nombre_trigger>

{BEFORE|AFTER}
{DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]
[OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]…]}
ON <nombre_tabla>
[FOR EACH ROW [WHEN (<condicion>)]]
DECLARE
— variables locales
BEGIN
— Sentencias
[EXCEPTION]
— Sentencias control de excepcion
END <nombre_trigger>;
El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite,

y el trigger existe, se producirá, un error.
Los triggers pueden definirse para las operaciones INSERT, UPDATE o

DELETE, y pueden ejecutarse antes o después de la operación. El modificador
BEFORE AFTER indica que el trigger se ejecutará antes o despues de ejecutarse
la sentencia SQL definida por DELETE INSERT UPDATE. Si incluimos el
modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los
campos incluidos en la lista.
El alcance de los disparadores puede ser la fila o de orden. El modificador FOR

EACH ROW indica que el trigger se disparará cada vez que se realizan
operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN,
se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan
la restricción.
La siguiente tabla resume los contenidos anteriores.
Valor Descripción
INSERT,
Define qué tipo de orden DML provoca la activación del
DELETE,
disparador.
UPDATE
BEFORE , Define si el disparador se activa antes o después de que se

AFTER ejecute la orden.
Los disparadores con nivel de fila se activan una vez por

cada fila afectada por la orden que provocó el disparo. Los
FOR EACH disparadores con nivel de orden se activan sólo una vez,
ROW antes o después de la orden. Los disparadores con nivel de
fila se identifican por la cláusula FOR EACH ROW en la
definición del disparador.
La cláusula WHEN sólo es válida para los disparadores con nivel de fila.
Dentro del ambito de un trigger disponemos de las variables OLD y

NEW . Estas variables se utilizan del mismo modo que cualquier otra variable
PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo
%ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW)
de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger.
Utilizando esta variable podemos acceder a los datos que se están insertando,
actualizando o borrando.
El siguiente ejemplo muestra un trigger que inserta un registro en la tabla

PRECIOS_PRODUCTOS cada vez que insertamos un nuevo registro en la tabla
PRODUTOS:
CREATE OR REPLACE TRIGGER TR_PRODUCTOS_01

AFTER INSERT ON PRODUCTOS
FOR EACH ROW
DECLARE
– local variables
BEGIN
INSERT INTO PRECIOS_PRODUCTOS
(CO_PRODUCTO,PRECIO,FX_ACTUALIZACION)
VALUES
(:NEW.CO_PRODUCTO,100,SYSDATE);
END ;
El trigger se ejecutará cuando sobre la tabla PRODUCTOS se ejecute

una sentencia INSERT.
INSERT INTO PRODUCTOS

(CO_PRODUCTO, DESCRIPCION)
VALUES
(’000100′,’PRODUCTO 000100′);
Orden de ejecución de los triggers
Una misma tabla puede tener varios triggers. En tal caso es necesario conocer el
orden en el que se van a ejecutar.
Los disparadores se activan al ejecutarse la sentencia SQL.
 Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo)

con nivel de orden.
 Para cada fila a la que afecte la orden:
 Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila.
 Se ejecuta la propia orden.
 Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior)
con nivel de fila.
 Se ejecuta, si existe, el disparador de tipo AFTER con nivel de orden.
Restricciones de los triggers

El cuerpo de un trigger es un bloque PL/SQL. Cualquier orden que sea legal en
un bloque PL/SQL, es legal en el cuerpo de un disparador, con las siguientes
restricciones:
Un disparador no puede emitir ninguna orden de control de transacciones:

COMMIT, ROLLBACK o SAVEPOINT. El disparador se activa como parte
de la ejecución de la orden que provocó el disparo, y forma parte de la misma
transacción que dicha orden. Cuando la orden que provoca el disparo es
confirmada o cancelada, se confirma o cancela también el trabajo realizado por
el disparador.
Por razones idénticas, ningún procedimiento o función llamado por el disparador

puede emitir órdenes de control de transacciones.
El cuerpo del disparador no puede contener ninguna declaración de variables

LONG o LONG RAW
Utilización de :OLD y :NEW
Dentro del ambito de un trigger disponemos de las variables OLD y

NEW . Estas variables se utilizan del mismo modo que cualquier otra variable
PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo
%ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW)
de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger.
Utilizando esta variable podemos acceder a los datos que se están insertando,
actualizando o borrando.
La siguiente tabla muestra los valores de OLD y NEW.
ACCION
OLD NEW
SQL
No definido; todos los Valores que serán insertados

INSERT campos toman valor cuando se complete la
NULL. orden.
Valores originales de la Nuevos valores que serán

UPDATE fila, antes de la escritos cuando se complete
actualización. la orden.
No definidos; todos los

Valores, antes del
DELETE campos toman el valor
borrado de la fila.
NULL.
Los registros OLD y NEW son sólo válidos dentro de los disparadores con nivel
de fila.
Podemos usar OLD y NEW como cualquier otra variable PL/SQL.
Utilización de predicados de los triggers: INSERTING, UPDATING y

DELETING
Dentro de un disparador en el que se disparan distintos tipos de órdenes DML

(INSERT, UPDATE y DELETE), hay tres funciones booleanas que pueden
emplearse para determinar de qué operación se trata. Estos predicados son
INSERTING, UPDATING y DELETING.
Su comportamiento es el siguiente:
Predicado Comportamiento
TRUE si la orden de disparo es INSERT; FALSE en otro

INSERTING
caso.
TRUE si la orden de disparo es UPDATE; FALSE en otro

UPDATING
caso.
TRUE si la orden de disparo es DELETE; FALSE en otro

DELETING
caso.
Para eliminar un trigger:
DROP TRIGGER nombre_trigger
Estructuras de control en PL-SQL
Sentencia IF
En PL/SQL solo disponemos de la estructura condicional IF. Su sintaxis se

muestra a continuación:
IF (expresion) THEN
– Instrucciones
ELSIF
(expresion) THEN
– Instrucciones
ELSE
– Instrucciones
END
IF;
Un aspecto a tener en cuenta es que la instrucción condicional anidada es
ELSIF
y no “ELSEIF”.
Sentencia GOTO
PL/SQL dispone de la sentencia GOTO. La sentencia GOTO desvia el flujo

de ejecució a una determinada etiqueta.
En PL/SQL las etiquetas se indican del siguiente modo: << etiqueta >>
El siguiente ejemplo ilustra el uso de GOTO.
DECLARE
flag NUMBER;
BEGIN
flag :=1 ;
IF (flag = 1) THEN
GOTO paso2;
END
IF;
<<paso1>>
dbms_output.put_line(‘Ejecucion de paso 1′);
<<paso2>>
dbms_output.put_line(‘Ejecucion de paso 2′);
END;
Sentencia Case
En PL/SQL solo disponemos de la estructura condicional Case. Su sintaxis se

muestra a continuación:
CASE n
WHEN 1 THEN
accicción1;
WHEN 2 THEN
2;
WHEN 3 THEN
3;
ELSE
otra acción;
END CASE;
El siguiente ejemplo ilustra el uso de CASE.
DECLARE
A
number;
BEGIN
A
:= 3;
CASE A
WHEN 1 THEN
dbms_output.put_line(‘A vale 1′);
WHEN 2 THEN
dbms_output.put_line(‘A vale 2′);;
WHEN 3 THEN
dbms_output.put_line(‘A vale 3′);
ELSE
dbms_output.put_line(‘A no vale 1,3 o 3′);
END CASE;
END;
Bucles
En PL/SQL tenemos a nuestra disposición los siguientes iteradores o bucles:
 LOOP
 WHILE
 FOR
El bucle LOOP, se repite tantas veces como sea necesario hasta que se fuerza su salida
con la instrucción EXIT. Su sintaxis es la siguiente
LOOP
– Instrucciones
IF (expresion) THEN
– Instrucciones
EXIT;
END IF;
END LOOP;
El bucle WHILE, se repite mientras que se cumpla expresion.
WHILE (expresion) LOOP
– Instrucciones
END LOOP;
El bucle FOR, se repite tanta veces como le indiquemos en los identificadores

inicio y final.
FOR contador IN [REVERSE] inicio..final LOOP
– Instrucciones
END LOOP;
En el caso de especificar REVERSE el bucle se recorre en sentido inverso.
Registros PL/SQL
Cuando vimos los tipos de datos, omitimos intencionadamente ciertos tipos de

datos. Estos son:
 Registros
 Tablas de PL
 VARRAY
Declaración de un registro.
Un registnslpwdro es una estructura de datos en PL/SQL, almacenados en

campos, cada uno de los cuales tiene su propio nombre y tipo y que se tratan
como una sola unidad lógica.
Los campos de un registro pueden ser inicializados y pueden ser definidos como
NOT NULL. Aquellos campos que no sean inicializados explícitamente, se
inicializarán a NULL.
TYPE <nombre> IS RECORD
(
campo <tipo_datos> [NULL | NOT NULL]
[,<tipo_datos>...]
);
El siguiente ejemplo crea un tipo PAIS, que tiene como campos el código, el
nombre y el continente.
TYPE PAIS IS RECORD
(
CO_PAIS NUMBER
,
DESCRIPCION VARCHAR2(50),
CONTINENTE VARCHAR2(20)
);
Los registros son un tipo de datos, por lo que podremos declarar variables de
dicho tipo de datos.
DECLARE
TYPE PAIS IS RECORD
CO_PAIS NUMBER
,
);
/* Declara una variable identificada por miPAIS de tipo

PAIS
Esto significa que la variable miPAIS tendrá los campos
ID, DESCRIPCION y CONTINENTE.
*/
miPAIS PAIS;
BEGIN
/* Asignamos valores a los campos de la variable.
*/
miPAIS.CO_PAIS := 27;
miPAIS.DESCRIPCION := ‘ITALIA’;
miPAIS.CONTINENTE := ‘EUROPA’;
END;
Los registros pueden estar anidados. Es decir, un campo de un registro puede ser
de un tipo de dato de otro registro.
DECLARE
TYPE PAIS IS RECORD
(CO_PAIS NUMBER ,
);
TYPE MONEDA IS RECORD

( DESCRIPCION VARCHAR2(50),
PAIS_MONEDA PAIS );
miPAIS PAIS;
miMONEDA MONEDA;
BEGIN
/* Sentencias
*/
END;
Pueden asignarse todos los campos de un registro utilizando una sentencia

SELECT. En este caso hay que tener cuidado en especificar las columnas en el
orden conveniente según la declaración de los campos del registro. Para este tipo
de asignación es muy frecuente el uso del atributo %ROWTYPE que veremos
más adelante.

INTO miPAIS
FROM PAISES
WHERE CO_PAIS = 27;
Puede asignarse un registro a otro cuando sean del mismo tipo:
DECLARE
TYPE PAIS IS RECORD …
miPAIS PAIS;
otroPAIS PAIS;
BEGIN
miPAIS.CO_PAIS := 27;
miPAIS.DESCRIPCION := ‘ITALIA’;
miPAIS.CONTINENTE := ‘EUROPA’;
otroPAIS := miPAIS;
END;
Declaración de registros con el atributo %ROWTYPE
Se puede declarar un registro basándose en una colección de columnas de una

tabla, vista o cursor de la base de datos mediante el atributo %ROWTYPE.
Por ejemplo, si tengo una tabla PAISES declarada como:
CREATE TABLE PAISES(

CO_PAIS NUMBER,
CONTINENTE VARCHAR2(20) );
Puedo declarar una variable de tipo registro como PAISES%ROWTYPE;
DECLARE
miPAIS PAISES%ROWTYPE;
BEGIN
/* Sentencias … */
END;
Lo cual significa que el registro miPAIS tendrá la siguiente estructura: CO_PAIS

NUMBER, DESCRIPCION VARCHAR2(50), CONTINENTE
VARCHAR2(20).
De esta forma se crea el registro de forma dinamic y se podrán asignar valores a

los campos de un registro a través de un select sobre la tabla, vista o cursor a
partir de la cual se creo el registro.
Tablas PL/SQL
Declaración de tablas de PL/SQL
Las tablas de PL/SQL son tipos de datos que nos permiten almacenar varios
valores del mismo tipo de datos.
Una tabla PL/SQL :
 Es similar a un array
 Tiene dos componenetes: Un índice de tipo BINARY_INTEGER que
permite acceder a los elementos en la tabla PL/SQL y una columna de
escalares o registros que contiene los valores de la tabla PL/SQL
 Puede incrementar su tamaño dinámicamente.
La sintaxis general para declarar una tabla de PL es la siguiente:
TYPE
<nombre_tipo_tabla>
IS TABLE OF
<tipo_datos> [NOT NULL]
INDEX BY BINARY_INTEGER ;
Una vez que hemos definido el tipo, podemos declarar variables y asignarle valores.
DECLARE
/* Definimos el tipo PAISES como tabla PL/SQL */

TYPE PAISES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
/* Declaramos una variable del tipo PAISES */
tPAISES PAISES;
BEGIN
tPAISES(1) := 1;
tPAISES(2) := 2;
tPAISES(3) := 3;
END;
No es posible inicializar las tablas en la inicialización.
El rango de binary integer es –2147483647.. 2147483647, por lo tanto el índice

puede ser negativo, lo cual indica que el índice del primer valor no tiene que ser
necesariamente el cero.
Tablas PL/SQL de registros
Es posible declarar elementos de una tabla PL/SQL como de tipo registro.
DECLARE
TYPE PAIS IS RECORD
CO_PAIS NUMBER
NOT NULL
,
);
TYPE PAISES IS TABLE OF PAIS INDEX BY BINARY_INTEGER ;
tPAISES PAISES;
BEGIN
tPAISES(1).CO_PAIS := 27;
tPAISES(1).DESCRIPCION := ‘ITALIA’;
tPAISES(1).CONTINENTE := ‘EUROPA’;
END;
Funciones para el manejo de tablas PL/SQL
Cuando trabajamos con tablas de PL podemos utilizar las siguientes funciones:

FIRST. Devuelve el menor índice de la tabla. NULL si está vacía.
LAST. Devuelve el mayor índice de la tabla. NULL si está vacía.
El siguiente ejemplo muestra el uso de FIRST y LAST :
DECLARE
TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY

BINARY_INTEGER;
misCiudades ARR_CIUDADES;
BEGIN
misCiudades(1) := ‘MADRID’;
misCiudades(2) := ‘BILBAO’;
misCiudades(3) := ‘MALAGA’;
FOR i IN misCiudades.FIRST..misCiudades.LAST
LOOP
dbms_output.put_line(misCiudades(i));
END LOOP;
END;
EXISTS(i). Utilizada para saber si en un cierto índice hay almacenado un valor.

Devolverá TRUE si en el índice i hay un valor.
DECLARE

BINARY_INTEGER;
BEGIN
FOR i IN misCiudades.FIRST..misCiudades.LAST
LOOP
IF misCiudades.EXISTS(i) THEN
dbms_output.put_line(misCiudades(i));
ELSE
dbms_output.put_line(‘El elemento no existe:’||TO_CHAR(i));
END IF;
END LOOP;
END;
COUNT. Devuelve el número de elementos de la tabla PL/SQL.

DECLARE

BINARY_INTEGER;
BEGIN
/* Devuelve 2, ya que solo hay dos elementos con
valor */
dbms_output.put_line(
‘El número de elementos es:’||misCiudades.COUNT);
END;
PRIOR (n). Devuelve el número del índice anterior a n en la tabla.
DECLARE
TYPE ARR_CIUDADES IS TABLE OF
VARCHAR2(50) INDEX BY BINARY_INTEGER;
BEGIN
/* Devuelve 1, ya que el elemento 2 no existe */
‘El elemento previo a 3 es:’ || misCiudades.PRIOR(3));
END;
NEXT (n). Devuelve el número del índice posterior a n en la tabla.
DECLARE
TYPE ARR_CIUDADES IS TABLE OF
VARCHAR2(50) INDEX BY BINARY_INTEGER;
BEGIN
/* Devuelve 3, ya que el elemento 2 no existe */
‘El elemento siguiente es:’ || misCiudades.NEXT(1));
END;
TRIM. Borra un elemento del final de la tabla PL/SQL.
TRIM(n) borra n elementos del final de la tabla PL/SQL.
DELETE. Borra todos los elementos de la tabla PL/SQL.
DELETE(n) borra el correspondiente al índice n.

DELETE(m,n) borra los elementos entre m y n.
Varrays
Definición de VARRAYS.
Un varray se manipula de forma muy similar a las tablas de PL, pero se

implementa de forma diferente. Los elementos en el varray se almacenan
comenzando en el índice 1 hasta la longitud máxima declarada en el tipo varray.
TYPE <nombre_tipo> IS
VARRAY (<tamaño_maximo>) OF <tipo_elementos>;
Una consideración a tener en cuenta es que en la declaración de un varray el tipo

de datos no puede ser de los siguientes tipos de datos:
BOOLEAN
NCHAR
NCLOB
NVARCHAR(n)
REF CURSOR
TABLE
VARRAY
Sin embargo se puede especificar el tipo utilizando los atributos %TYPE y

%ROWTYPE.
Los VARRAY deben estar inicializados antes de poder utilizarse. Para inicializar
un VARRAY se utiliza un constructor (podemos inicializar el VARRAY en la
sección DECLARE o bien dentro del cuerpo del bloque):
DECLARE
/* Declaramos el tipo VARRAY de cinco elementos VARCHAR2*/

TYPE t_cadena IS VARRAY(5) OF VARCHAR2(50);
/* Asignamos los valores con un constructor */

v_lista t_cadena:= t_cadena(‘Aitor’, ‘Alicia’, ‘Pedro’,”,”);
BEGIN
v_lista(4) := ‘Tita’;
v_lista(5) := ‘Ainhoa’;
END;
El tamaño de un VARRAY se establece mediante el número de parámetros

utilizados en el constructor, si declaramos un VARRAY de cinco elementos
pero al inicializarlo pasamos sólo tres parámetros al constructor, el tamaño del
VARRAY será tres. Si se hacen asignaciones a elementos que queden fuera del
rango se producirá un error.
El tamaño de un VARRAY podrá aumentarse utilizando la función EXTEND,

pero nunca con mayor dimensión que la definida en la declaración del tipo. Por
ejemplo, la variable v_lista que sólo tiene 3 valores definidos por lo que se
podría ampliar hasta cinco elementos pero no más allá.
Un VARRAY comparte con las tablas de PL todas las funciones válidas para
ellas, pero añade las siguientes:
 LIMIT . Devuelve el número maximo de elementos que admite el

VARRAY.
 EXTEND .Añade un elemento al VARRAY.
 EXTEND(n) .Añade (n) elementos al VARRAY.
Varrays en la base de datos
Los VARRAYS pueden almacenarse en las columnas de la base de datos. Sin

embargo, un varray sólo puede manipularse en su integridad, no pudiendo
modificarse sus elementos individuales de un varray.
Para poder crear tablas con campos de tipo VARRAY debemos crear el
VARRAY como un objeto de la base de datos.
La sintaxis general es:
CREATE [OR REPLACE]

TYPE
<nombre_tipo>
IS
VARRAY (<tamaño_maximo>) OF
<tipo_elementos>;
Una vez que hayamos creado el tipo sobre la base de datos, podremos utilizarlo
como un tipo de datos más en la creacion de tablas, declaración de variables ….
Vease el siguiente ejemplo:
CREATE
OR REPLACE TYPE PACK_PRODUCTOS AS VARRAY(10) OF
VARCHAR2(60);
CREATE
TABLE OFERTAS
(
CO_OFERTA NUMBER,
PRODUCTOS PACK_PRODUCTOS,
PRECION NUMBER
);
Para modificar un varray almacenado, primero hay que seleccionarlo en una

variable PL/SQL. Luego se modifica la variable y se vuelve a almacenar en la
tabla.
La utilización de VARRAYS en la base de datos está completamente

desaconsejada.
Bulk collect
PL/SQL nos permite leer varios registros en una tabla de PL con un único
acceso a través de la instrucción BULK COLLECT.
Esto nos permitirá reducir el número de accesos a disco, por lo que

optimizaremos el rendimiento de nuestras aplicaciones. Como contrapartida el
consumo de memoria será mayor.
DECLARE
TYPE t_descripcion IS TABLE OF PAISES.DESCRIPCION%TYPE;
TYPE t_continente IS TABLE OF PAISES.CONTINENTE%TYPE;
v_descripcion t_descripcion;
v_continente t_continente;
BEGIN
SELECT DESCRIPCION,
CONTINENTE
BULK COLLECT INTO v_descripcion, v_continente
FROM PAISES;
FOR i IN v_descripcion.FIRST .. v_descripcion.LAST

LOOP
dbms_output.put_line(v_descripcion(i) || ‘, ‘ ||
v_continente(i));
END LOOP;
END;
/
Podemos utilizar BULK COLLECT con registros de PL.
DECLARE
TYPE PAIS IS RECORD (CO_PAIS NUMBER ,
CONTINENTE VARCHAR2(20));
TYPE t_paises IS TABLE OF PAIS;
v_paises t_paises;
BEGIN
BULK COLLECT INTO v_paises
FROM PAISES;
FOR i IN v_paises.FIRST .. v_paises.LAST

LOOP
dbms_output.put_line(v_paises(i).DESCRIPCION ||
‘, ‘ || v_paises(i).CONTINENTE);
END LOOP;
END;
/
Tambien podemos utilizar el atributo ROWTYPE.
DECLARE
TYPE t_paises IS TABLE OF PAISES%ROWTYPE;
v_paises t_paises;
BEGIN
BULK COLLECT INTO v_paises
FROM PAISES;
FOR i IN v_paises.FIRST .. v_paises.LAST

LOOP
dbms_output.put_line(v_paises(i).DESCRIPCION ||
‘, ‘ || v_paises(i).CONTINENTE);
END LOOP;
END;
/
Transacciones autónomas
En ocasiones es necesario que los datos escritos por parte de una transacción
sean persistentes a pesar de que la transaccion se deshaga con ROLLBACK.
PL/SQL permite marcar un bloque con PRAGMA

AUTONOMOUS_TRANSACTION. Con esta directiva marcamos el
subprograma para que se comporte como transacción diferente a la del proceso
principal, llevando el control de COMMIT o ROLLBACK independiente.
Observese el siguiente ejemplo. Primero creamos un procedimiento y lo

marcamos con PRAGMA AUTONOMOUS_TRANSACTION.
CREATE OR REPLACE PROCEDURE Grabar_Log(descripcion

VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG_APLICACION
(CO_ERROR, DESCRIPICION, FX_ERROR)
VALUES
(SQ_ERROR.NEXTVAL, descripcion, SYSDATE);
COMMIT; – Este commit solo afecta a la transaccion

autonoma
END ;
A continuación utilizamos el procedimiento desde un bloque de PL/SQL:
DECLARE
producto PRECIOS%TYPE;
BEGIN
producto := ’100599′;
INSERT INTO PRECIOS
(CO_PRODUCTO, PRECIO, FX_ALTA)
VALUES
(producto, 150, SYSDATE);

COMMIT;
EXCEPTION
WHEN
OTHERS
THEN
Grabar_Log(SQLERRM);
ROLLBACK;
/* Los datos grabados por “Grabar_Log” se escriben en la base
de datos a pesar del ROLLBACK, ya que el procedimiento está
marcado como transacción autonoma.
*/
END;
Es muy común que, por ejemplo, en caso de que se produzca algún tipo de error
queramos insertar un registro en una tabla de log con el error que se ha
produccido y hacer ROLLBACK de la transacción. Pero si hacemos
ROLLBACK de la transacción tambien lo hacemos de la insertción del log.
SQL Dinamico
Sentencias DML con SQL dinamico
PL/SQL ofrece la posibilidad de ejecutar sentencias SQL a partir de cadenas de

caracteres. Para ello debemos emplear la instrucción EXECUTE
IMMEDIATE.
Podemos obtener información acerca de número de filas afectadas por la

instrucción ejecutada por EXEXUTE IMMEDIATE utilizando SQL
%ROWCOUNT.
El siguiente ejemplo muestra la ejecución de un comando SQL dinamico.
DECLARE
ret NUMBER;
FUNCTION fn_execute RETURN NUMBER IS
sql_str VARCHAR2(1000);
BEGIN
sql_str := ‘UPDATE DATOS SET NOMBRE = ”NUEVO NOMBRE”
WHERE CODIGO = 1′;
EXECUTE IMMEDIATE sql_str;
RETURN SQL%ROWCOUNT;
END fn_execute ;
BEGIN
ret := fn_execute();
dbms_output.put_line(TO_CHAR(ret));
END;
Podemos además parametrizar nuestras consultas a través de variables host. Una

variable host es una variable que pertenece al programa que está ejecutando la
sentencia SQL dinámica y que podemos asignar en el interior de la sentencia SQL
con la palabra clave USING . Las variables host van precedidas de dos puntos “:”.
El siguiente ejemplo muestra el uso de variables host para parametrizar una

sentencia SQL dinamica.
DECLARE
ret NUMBER;
FUNCTION fn_execute (nombre VARCHAR2, codigo NUMBER)
RETURN NUMBER
IS
sql_str VARCHAR2(1000);
BEGIN
sql_str := ‘UPDATE DATOS SET NOMBRE = :new_nombre
WHERE CODIGO = :codigo’;
EXECUTE IMMEDIATE sql_str USING nombre, codigo;
RETURN SQL%ROWCOUNT;
END fn_execute ;
BEGIN
ret := fn_execute(‘Devjoker’,1);
dbms_output.put_line(TO_CHAR(ret));
END;
Cursores con SQL dinámico
Con SQL dinámico también podemos utilizar cursores.
Para utilizar un cursor implicito solo debemos construir nuestra sentencia

SELECT en una variable de tipo caracter y ejecutarla con EXECUTE
IMMEDIATE utilizando la palabra clave INTO.
DECLARE
str_sql VARCHAR2(255);
l_cnt VARCHAR2(20);
BEGIN
str_sql := ‘SELECT count(*) FROM PAISES’;
EXECUTE IMMEDIATE str_sql INTO l_cnt;
dbms_output.put_line(l_cnt);
END;
Trabajar con cursores explicitos es también muy fácil. Únicamente destacar el uso
de REF CURSOR para declarar una variable para referirnos al cursor generado con
SQL dinamico.
DECLARE
TYPE CUR_TYP IS
REF CURSOR;
c_cursor CUR_TYP;
fila PAISES%ROWTYPE;
v_query VARCHAR2(255);
BEGIN
v_query := ‘SELECT * FROM PAISES’;
OPEN c_cursor FOR v_query;

LOOP
FETCH c_cursor INTO fila;
EXIT WHEN c_cursor%NOTFOUND;
dbms_output.put_line(fila.DESCRIPCION);
END LOOP;
CLOSE c_cursor;
END;
Las varibles host tambien se pueden utilizar en los cursores.
DECLARE
TYPE cur_typ IS REF CURSOR;
c_cursor CUR_TYP;
fila PAISES%ROWTYPE;
v_query VARCHAR2(255);
codigo_pais VARCHAR2(3) := ‘ESP’;
BEGIN
v_query := ‘SELECT * FROM PAISES WHERE CO_PAIS = :cpais’;

OPEN c_cursor FOR v_query USING codigo_pais;
LOOP
FETCH c_cursor INTO fila;
EXIT WHEN c_cursor%NOTFOUND;
dbms_output.put_line(fila.DESCRIPCION);
END LOOP;
CLOSE c_cursor;
END;
PL/SQL y Java
Otra de las virtudes de PL/SQL es que permite trabajar conjuntamente con Java.
PL/SQL es un excelente lenguaje para la gestion de información pero en

ocasiones, podemos necesitar de un lenguaje de programación más potente. Por
ejemplo podríamos necesitar consumir un servicio Web, conectar a otro servidor,
trabajar con Sockets …. Para estos casos podemos trabajar conjuntamente con
PL/SQL y Java.
Para poder trabajar con Java y PL/SQL debemos realizar los siguientes pasos:
Crear el programa Java y cargarlo en la base de datos.
Crear un program de recubrimiento (Wrapper) de PL/SQL.
Creacion de Objetos Java en la base de datos ORACLE.
ORACLE incorpora su propia versión de la máquina virtual Java y del JRE.

Esta versión de Java se instala conjuntamente con ORACLE.
Para crear objetos Java en la base de datos podemos utilizar la uitlidad LoadJava
de ORACLE desde linea de comandos o bien crear objetos JAVA SOURCE en
la propia base de datos.
La sintaxis para la creación de JAVA SOURCE en ORACLE es la siguiente.
CREATE [OR REPLACE] AND COMPILE
JAVA SOURCE
NAMED <JavaSourceName>
AS
public class <className>
{
<java code>
…
};
El siguiente ejemplo crea y compila una clase Java OracleJavaClass en el interior

de JAVA SOURCE FuentesJava. Un aspecto muy a tener en cuenta es que los
métodos de la clase java que queramos invocar desde PL/SQL deben ser
estaticos.
CREATE
OR REPLACE AND COMPILE
JAVA SOURCE
NAMED FuentesJava
AS
public
class
OracleJavaClass
{
public
static
String Saluda(String nombre)
{
return (“Hola desde Java” + nombre);
}
}
;
Un mismo JAVA SOURCE puede contener varias clases de Java.
CREATE
OR REPLACE AND COMPILE
JAVA SOURCE
NAMED FuentesJava
AS
public
class
OracleJavaClass
{
public
static
String Saluda(String nombre)
{
return (“Hola desde Java” + nombre);
}
}
public class
OracleJavaMejorada
{
public static
String SaludoMejorado(String nombre)
{
return (“Saludo mejorado desde Java para:” + nombre);
}
}
;
La otra opción sería guardar nuestro codigo java en el archivo OracleJavaClass.java,

compilarlo y cargarlo en ORACLE con LoadJava.
A continuación se muestran ejemplos del uso de la utilidad LoadJava
loadJava -help
loadJava -u usario/password -v -f -r OracleJavaClass.class
loadJava -u usario/password -v -f -r OracleJavaClass.java
Ejecución de programas Java con PL/SQL
Una vez que tenemos listo el programa de Java debemos integrarlo con PL/SQL.
Esto se realiza a través de subprogramas de recubrimiento llamados Wrappers.
No podemos crear un Wrapper en un bloque anonimo.
CREATE [OR
REPLACE]
FUNCTION|PROCEDURE
<name> [(<params>,...)]
[RETURN
<tipo>]
IS|AS
LANGUAGE
JAVA NAME
‘<clase>.<metodo> [return
<tipo>]‘ ;
El siguiente ejemplo muestra el Wrapper para nuestra función Saludo.
CREATE OR
REPLACE
FUNCTION Saluda_wrap (nombre VARCHAR2)

RETURN
VARCHAR2
AS
LANGUAGE JAVA NAME
‘OracleJavaClass.Saluda(java.lang.String) return java.lang.String’;
Una vez creado el wrapper, podremos ejecutarlo como cualquier otra funcion o
procedure de PL/SQL. Debemos crear un wrapper por cada función java que
queramos ejecutar desde PL/SQL.
Cuando ejecutemos el wrapper, es decir, la función “Saluda_wrap”,

internamente se ejecutará la clase java y se invocará el método estático
“OracleJavaClass.Saluda”.
Un aspecto a tener en cuenta es que es necesario proporcionar el nombre del tipo

java completo, es decir, debemos especificar java.lang.String en lugar de
únicamente String.
SELECT SALUDA_WRAP(‘DEVJOKER’)
FROM DUAL;
La ejecución de este ejemplo en SQL*Plus genera la siguiente salida:
SQL> SELECT SALUDA_WRAP(‘DEVJOKER’) FROM DUAL;
SALUDA_WRAP(‘DEVJOKER’)
———————————————-
Hola desde JavaDEVJOKER
Una recomendación de diseño sería agrupar todos los Wrapper en un mismo

paquete.
En el caso de que nuestro programa Java necesitase de packages Java

adicionales, deberiamos cargarlos en la base de datos con la utilidad LoadJava.
Buenas prácticas trabajando con PL-SQL
Porque necesitamos Buenas Practicas en el desarrollo del Back End de las

aplicaciones?
Simplemente, por las mismas razones que se necesitan en el desarrollo de

cualquier tipo de software: Mantención, Legibilidad, Rehúso y Modificabilidad
del código. Pero, los programadores de PL/SQL, debemos estar especialmente
atentos en este sentido, por las siguientes dos razones:
Con el potencial que nos ofrece PL/SQL es importante estar atentos al abanico
de posibilidades del que disponemos. No sucede lo mismo en otros lenguajes
PL, ya que estos se encuentran mucho mas limitados en sus alternativas de
expresión.
Es sumamente fácil, tal vez demasiado a juicio de los gurúes, escribir SQL en
PL/SQL, por cuanto no necesitamos de una interfaz ODBC/JDBC etc. Esto,
sumado a la creencia de que el modelo de datos esta libre de la evolución propia
del software, genera código difícil de mantener y evolucionar.
EL TESTING DE UNIDAD
Algunas consideraciones al respecto:
LOS DEVELOPERS GASTAN EL 80% DEL TIEMPO DE DESARROLLO EN

IDENTIFICAR Y CORREGIR DEFECTOS!
Si bien existen muchas clases de test, solamente existe un responsable para los
Test de Unidad: el Programador!
Nuestro código no se va a testear solo, por lo que debemos aceptar la

responsabilidad y ser disciplinados!!
Seis pasos hacia un testing de unidad exitoso:
 Describir toda la funcionalidad requerida del programa

 Definir el Header del programas: Nombre, Lista de Parámetros, Valor de
retorno.
 Elaborar los casos de test para el programa
 Construir el programa que testee nuestro programa
 Programar la unidad de programa
 Testear, Debaguear y Corregir. Testear, Debaguear y Corregir…
Repetir del 3-6 generando un Bug Report.
No debemos preocuparnos por comprar o desarrollar código para hacer nuestros

test de unidad, podemos descargar gratuitamente del sitio oficial de Oracle un
sencillo frame PL/SQL a tales efectos.
Es interesante mencionar que los casos de uso deben escribirse ANTES que la
unidad de programa. Si escribimos las pruebas después de la unidad, vamos a
inclinar la balanza hacia la búsqueda de aciertos y no de los fallos, existe de
hecho, una tendencia psicológica a seleccionar casos de test que el desarrollador
sepa tratados en su unidad.
Ahora, que ocurre cuando nuestra aplicación en producción necesita una

modificación leve? Podemos hacerla y comprobar con nuestro test de unidad si
la unidad es correcta, pero existe un beneficio mayor: Como sabemos que la
aplicación en conjunto sigue funcionando correctamente? Los test de Unidad
almacenados nos permitirán realizar lo que se denomina Test de Regresión. Se
vuelven a disparar los test de unidad y de esta manera nos aseguramos que el
cambio en la funcionalidad de las partes no afectó el funcionamiento del todo.
Aumentando el rendimiento con FORALL y BULL COLLECT
FORALL: Resulta sumamente útil en operaciones DML, por cuanto la sentencia

FORALL nos permite tomar todos los datos de una colección y llevarlos a la
base de datos de una sola vez.
Cuando se ejecuta un UPDATE, se parsea el bloque en el motor de pl/sql y

después en el motor SQL una vez por cada fila updeteada, estos N cambios de
contexto representan mucho overhead. En cambio, en el FOR ALL UPDATE se
produce solo un cambio de contexto en cada parser.
Ejemplo:
Esta sentencia por estar dentro de un bloque PL/SQL requiere que la interprete el
motor PL/SQL y después el de SLQ POR CADA fila.
FOR rec IN emp_cur LOOP
UPDATE employee
SET …
WHERE …
LOOP;
Se parsea solo una vez, pues se hace un cambio de contexto entre el motor PL y
el moto SQL, solamente.
ORALL indice IN coleccion.FIRST .. coleccion.LAST
UPDATE employee
SET …
WHERE …
Observaciones sobre el uso de FORALL:
El motor lee el contenido de la colección de manera secuencial, por lo que si

encuentra una posición sin un valor definido disparará la excepción ORA-22160.
Si no queremos este problema, y disponemos de Oracle 10G  podemos
agregar la sentencia INDICES OF en la sentencia FORALL. Por ejemplo:
FORALL i IN INDICES OF miColección

INSERT INTO …
Solo se permiten DML simples para FORALL.
SQL%BULK_ROWCOUNT retorna el número de filas procesadas para la

consulta.
Antes de Oracle 10G, las colecciones debían llenarse secuencialmente.
BULK COLLECT INTO:
Como es sabido, cuando queremos recuperar el valor de una fila utilizamos la

sentencia
INTO, pero esto no es posible cuando el resultado de la consulta retorna mas de

una fila. Existe a tales efectos, dos soluciones. Una, es el uso de cursores. La
otra, es la sentencia BULK COLLECT INTO que podrá recuperar N filas y
colocarlas en una estructura adecuada, siendo esta una colección de lo que sea
que necesitemos, Rowtypes, Objects, etc.
También podemos hacer abrir una variable de cursor sobre una estructura,
usando la sentencia BULK COLLECT INTO.
Un caso en que podemos usar BULK COLLECT INTO
Si necesitamos lleva datos masivamente a la base, entonces necesitaremos hacer

un FORALL pero esta sentencia solo funciona con colecciones, no con cursores,
por lo que para usarla necesitamos haber hecho preferiblemente un BULK
COLLECT INTO sobre alguna colección o array.
Resumiendo, siempre que necesitemos realizar una DML por cada fila de un
cursor, o una DML dentro de un LOOP, es mejor utilizar un BULK COLLECT
INTO mas allá de si lo hacemos sobre una variable de cursor o directamente con
una sentencia SQL.
Cuando usar esta sentencia? Cuando se necesite ejecutar SQL dentro de un

LOOP, preferentemente si el número de filas es mayor a 5.
declaramos una tabla de algún tipo de dato, preferentemente un ROWTYPE:

MiTabla
SELECT * BULK COLLECT INTO MiTabla
Iteramos sobre la tabla.
PAGINACIÓN DE SENTENCIAS DML
Declarar un cursor y abrirlo

En ves de hacer FETCH rec, hacemos FETCH rec BULK COLLECT INTO
MiTabla [LIMIT cantFilasQueQuieraTraer].
Procesamos la tabla, y seguimos iterando pero nos traemos de a N filas, en vez

de a una.
Los BULK COLLECT pueden ser usados con cursores Implícitos o Explícitos.
Las colecciones siempre se rellenan secuencialmente, comenzando de 1.
SOBRE LOS ABUSOS DE SQL
Pl/sql nos permite interactuar con el motor SQL sin otra capa intermedia, como
ODBC/JDBC etc. De esta manera, facilita tal vez demasiado el uso de SQL.
Pero, la capa PL/SQL debería permitir una fácil manipulación del modelo de
datos, y no constituir un obstáculo para la correcta evolución del mismo. Si
todos escriben SQL donde y cuando quieren, entonces el código se torna
inmanejable.
Recordemos hardcodear nuestro código, es malo. Por otro lado, es una verdad
innegable que TODA SENTENCIA SQL ES HARD-CODING! Por lo que
podemos afirmar por transitividad y sin pérdida de generalidad, que SQL es
MALO! Que mas sabemos sobre SQL?
La mayoría de los problemas de performance suelen estar en las sentencias SQL.
Provocan errores en tiempo de ejecución
Las sentencias SQL estan sujetas a las reglas del negocio, y las reglas del
negocio cambian por lo que nuestras sentencias SQL cambian.
El modelo de datos, es una de los modelos mas volátiles de cualquier aplicación;

las sentencias SQL cablean esas estructuras! Sería conveniente que regáramos
por toda la aplicación las mismas sentencias SQL cableadas que probablemente
mañana tengamos que cambiar? Claro que no lo sería, si hacemos tal cosa,
deberemos debaguear, optimizar y mantener las mismas líneas de código donde
quiera que aparezcan.
Si abstraemos el modelo de datos en estructuras que lo encapsulen será mucho

mas fácil realizar las modificaciones del modelo de datos. Además, se puede
hacer un manejo consistente de las excepciones, pues de otra manera, pueden
hacerse N manejos de una excepción en distintos lugares.
Para abstraer consultas que retornen una sola fila:
Es importante la utilización de generadores de código para las sentencias

simples, como la recuperación de una fila por su PK o por algún índice. Se
sugiere la abstracción de una tabla mediante métodos de un paquete que retornen
el valor de una columna en particular. También puede ser útil escribir un paquete
que se inicialice con la PK de una instancia de la entidad, por ejemplo si
tenemos la tabla Empleados, podemos desarrollar un paquete que nos permita
acceder cada uno de las columnas mediante una función retornarColumna(PK),
por ejemplo. Facilitará esta abstracción, el uso de un TYPE declarado público en
el paquete que abstrae la entidad del modelo que estamos implementando.
Para abstraer consultas que retornen mas de una fila:
Tenemos dos opciones:
 Encapsular retornando una colección: Podemos hacer un BULK

COLLECT en una colección de tipos ROWTYPE y retornar la coleccion.
 Variable Cursor: Esto último es sumamente útil para retornar datos en
entornos no PL/SQL. Una variable cursor es un ptero que apunta a un
result set. Podemos pasar variables cursor de una unidad de programa a
otra, inclusive si no son programas PL/SQL.
Pensar en SQL como un SERVICIO provisto por el gestor relacional, y no como

líneas de código, puede contribuir a que no rescribamos una y otra vez
sentencias cableadas. Repetir SQL es repetir lógica asociada al negocio. Mejor
solicitar el servicio cuando se necesite y no redefinirlo cada vez. Esto se logra
con una apropiada encapsulación de los objetos de base de datos.
PROGRAMAR PARA EL CAMBIO
Cambian las estructuras de datos  Los programas se marcan como inválidos

 Hay que re compilar el código inválido
Por esto es sumamente importante no cablear la definición de una variable.
Usar %TYPE y %ROWTYPE.
Hacer los FETCH de un cursor en un registro de tipo cursor y no en variables!
Asegurarnos de que ninguna variable entrará en conflicto con un objeto existente

de la base de datos. Pero como podemos adelantarnos a un futuro
renombramiento de algún objeto de la base? Las convenciones de nomenclatura
no nos ofrecen garantías 
SIEMPRE CUALIFICAR LAS REFERENCIAS A VARIABLES DENTRO
DE UNA SENTENCIA SQL!!
Ejemplo:
Anteponemos el nombre del Procedure a la variable para no tener conflictos.
También podemos arriesgarnos a que las reglas de nomenclatura nos ayuden,

esto por supuesto, si no existe la posibilidad de que una columna se renombre
con nuestra nomenclación…
MANEJAR LOS ERRORES EFECTIVA Y CONCISTENTEMENTE

Las excepciones pueden ser disparadas y manejadas, pero no pasadas como
argumentos a otros programas.
Para comunicarle un mensaje de error a la aplicación que usa nuestro paquete,

tenemos que usar RAISE_APLICATTION_ERROR (NUMERO, MENSAJE).
Es importante definir de manera centralizada los números de error y los
mensajes correspondientes para hacer un manejo consistente.
UN PAR DE CONCEPTOS AVANZADOS SOBRE PACKAGES
La participación de los packages en el diseño de alto nivel:
La fortaleza de los packages radica en la posibilidad que brindan implementar

abstracción para nuestras aplicaciones. Nos permiten ocultar la complejidad y
los detalles de una funcionalidad determinada. Por esta razón, es el proceso de
diseño debe haber una instancia para generar una visión arquitectural de la
definición e interrelación de los packages. Una manera efectiva de lograr esto, es
asociar un package a cada necesidad funcional compleja de nuestra aplicación.
No es desatinado intentar una relación entre un diagrama de clases , uno de

Entidad / Relación y el diagrama de packages. De hecho, un paquete intenta
implementar las principales cualidades de una clase al definir una interfase
pública y una implementación privada.
Sobre las interdependencias:
Un diagrama nos permitirá establecer dependencias y relaciones entre los

mismos. Nunca deberíamos permitir que el package A tenga una dependencia
con el package B si este último tiene una dependencia con el package A. Si no es
posible evitar esta interdependencia tenemos que recordar que compilando
primero la especificación de ambos paquetes podemos compilar la
interdependencia existente. Es importante recordar esto a la hora de escribir un
script de instalación. De esto surge como una buena practica mantener en
archivos separados la especificación de un package de su body. Si ponemos todo
junto quizá habrá problemas en los scripts de instalación.
Por supuesto, no todas las interdependencias son viables. Si la especificación del

package A referencia a un elemento del package B, y la especificación del
package B referencia un elemento del package A  Sencillamente será
imposible compilar estos paquetes, por lo que dicha situación tiene que ser
claramente evitada.
Buenas Prácticas:
Un principio fundamental es considerar al resto de developers como Usuarios.

Este detalle psicológico tendrá un impacto sustancial en la calidad de nuestras
interfaces. Todos los programadores somos concientes de la opinión
generalizada que tenemos de los usuarios de nuestras aplicaciones. Para lograr el
confort en el uso de nuestras aplicaciones, debemos tener en mente algunas
consideraciones.
Hacer nuestras aplicaciones CASE INSENSITIVE
Siempre que recibamos parámetros de tipo texto, necesitamos asegurarnos de

que la manera en que los recibamos no afecte la conducta de nuestra aplicación,
de lo contrario veremos muy seguido errores como por ejemplo el ORA-06503:
Function returned without value. Simplemente con un UPPER(Parámetro)
podemos evitar la incomodidad de recordar como tienen que ser parametrizada
nuestra aplicación.
Evitar la parametrización con literales de nuestra unidad.
El usuario estará mucho mas feliz si no tiene que recordar los literales que guían
la conducta de nuestra aplicación. Como logramos esto? Tenemos dos opciones.
Una es proveer programas separados para realizar distintas acciones. Esto es

viable solamente si la cantidad de acciones a realizar es fija y estemos seguros
de que continuará así…
Proveer constantes basadas en packages, preferiblemente el mismo donde

tenemos la unidad. Esta es sin duda la mejor opción. Es cierto que de todas
formas el usuario tendrá que recordar el nombre del parámetro por mas que esté
en un package, pero si algo sale mal se enterará en tiempo de Compilación lo
cual es mucho mas ventajoso que enterarse en tiempo de ejecución, teniendo que
depurar el código buscando ese misterioso error no detectado causado por
escribir con mayúsculas alguna letra del literal que parametriza la unidad.
Flexibilizar nuestros packages mediante Toggles.
La aproximación mas general para flexibilizar una unidad de programa es

hacerla altamente parametrizada. El problema es que este enfoque aumenta
considerablemente el coupling de la unidad y complejiza la interface.
Numerosos estudios comprueban que el cerebro humano no esta bien equipado
para tratar con mas de 7 ítems a la vez… Por lo que tenemos que cuirdarnos de
sobrecargar en exceso las firmas de nuestras unidades de programa. Además,
que pasaría si quisiéramos afectar TODA la conducta de un package y no solo la
de una unidad?
Una mejor aproximación es el uso de Toggles o suichs. Un toggle es un set de

tres programas: Dos procedimientos para prender o apagar y una función para
retornar el valor del toggle.
Sacar provecho del OverLoad de nombre
Las ventajas de sobrecargar nombres de unidades son claras. Pensemos en la

funcion To_Char(). Tenemos que llamar a funciones distintas si el argumento es
de tipo integer o de tipo real? No, gracias a su OverLoad.Cuando sobrecargamos
el nombre de una unidad, estamos ofreciendo muchas versiones de una misma
unidad, simplificando la interface de la unidad, lo que resulta crítico. En esto de
ver a los developers como usuarios de nuestros desarrollos, notamos la
importancia de programar unidades intuitivas y versátiles.
Sucede a veces, que nos encontramos con 3 o 4 unidades de programas que
básicamente realizan la misma tarea a nivel semántico pero que devuelven
distintos tipos de datos: Como transformar esas N unidades de diferentes
nombres en N unidades de igual nombre sobrecargadas? Un primer intento
pudiera ser generar solo una función y pasarle un parámetro enumerado. Una
mejor aproximación sería generar las N unidades con igual nombre,
proveyéndoles a cada una un parámetro VALOR_DEFAULT para el retorno, de
manera que los diferentes tipos de datos permitirán evadir la ambigüedad de la
firma de las unidades sobrecargadas.
El verdadero desafío es desarrollar una sensibilidad que nos permita hacer del
overloading nuestra primera solución intuitiva frente a los problemas donde la
facilidad de uso sea un objetivo.
Modularizando para desarrollar Packages mantenibles
Tenemos que resistir la tentación de cortar/pegar código creyendo que así

estamos construyendo aplicaciones rápidamente, porque lo que realmente
estamos haciendo es cavar una fosa muy profunda donde enterraremos las
características evolutivas de nuestra aplicación. Tenemos entonces, una regla:
Nunca repitamos una línea de código. En cambio, hemos de construir una
unidad privada y llamarla 2 o N veces.
Vale aclarar, que Overloading y modularidad deben considerarse dos caras de la

misma moneda si queremos implementar el package correctamente.
El modelo del Diamante:
Debemos estar atentos para que las N versiones de nuestra unidad sobrecargada
no este repitiendo líneas de código. Debemos factorizar esas repeticiones y de
esa manera lograr la regla mencionada. Normalmente, se llama a este diseño
Diamante, si vemos que en la capa superior tenemos una vista de usuario (Que
no se corresponde exactamente a una unidad de programa, sino a las N unidades
sobrecargadas). En un nivel medio tenemos N versiones de la unidad, y en la
capa inferior tenemos una unidad que factoriza lo común de las anteriores (que
normalmente contendrá un IF desarrollado, a esto se le llama Efecto de Lampara
de Lava, porque si abrimos los if da la sensación de modelar una de estas
lámparas). Es altamente recomendable construir nuestro package con este
criterio.
Ocultando datos en el Package
Llamamos Packages Data a cualquier estructura de datos implementada en la

especificación (los públicos) o el cuerpo (privados) de un package.
Manejar datos públicos es ciertamente conveniente para algunos casos. Pero

puede presentar problemas:
Perdida de Control: Cualquier programador puede cambiar los valores de la

estructura por ser ésta pública. Es posible que existan reglas de negocios que
afecten el valor de esta variable, pero no podremos hacer nada para proteger esa
regla de negocios pues nuestra variable es pública, y cualquiera puede
modificarla.
Perdida de Flexibilidad: Al ser conocida su estructura, todos hacen suposiciones

sobre la misma. La posible necesidad de hacer un cambio en la estructura
generará muchos problemas en los usuarios.
Se recomienda nunca definir variables en la especificación de un package si

es que tenemos otra alternativa. Lo mejor es hacer públicos los datos pero no las
estructuras que lo contengan. Como se logra esto? Declarando las variables en el
cuerpo del package y definiendo métodos Get y Set para manipular sus valores.
Una muy interesante consecuencia de esta buena práctica, es la posibilidad de

conocer Que y Cuando se esta modificando el valor de un dato. Como es esto
posible? Como solo existe un punto por el que se puede afectar el valor de una
variable privada, es decir su método Set, podemos en el mismo realizar una
traza. Antes de modificar su valor, mostramos en pantalla o en un archivo el
nuevo valor que tomará. Esta aproximación puede ser muy poderosa si
mantenemos en un package especialmente dedicado a las trazas, manteniendo el
nombre del programa que esta realizando el cambio y suministrando un método
al que podamos llamar desde los Sets para realizar la traza. A este package
podemos añadirle un Toggle o un Suich para activar a desactivar la traza a
nuestro gusto. De esta manera, no pasaremos horas debagueando nuestra
aplicación para saber donde y que provocó el cambio del valor de una variable.
Otra ventaja, es cuando cambie la estructura de la variable, no habrá que

recompilar todos los packages que la utilicen, cosa que sí ocurre cuando
declaramos la variable en la especificación del package.
Al mover los datos al cuerpo del paquete, estaremos simplificando la interface

de los métodos allí contenidos, pues estos datos serán ahora globales al package,
de modo que probablemente nos ahorremos un parámetro en todos aquellas
unidades de código que lo necesiten. Es cierto que esto hace nuestros packages
dependiente de los datos, pero como contrapartida solidifica la idea de que los
packages son mas un entorno que una colección de unidades.
Sobre cuando y donde empaquetar funcionalidad:
Cuando hay que empaquetar funcionalidad? Casi siempre.
Cuando no hay que empaquetar funcionalidad? Las siguientes condiciones dadas

simultáneamente pueden modelar una sugerencia:
La unidad de programa es muy usada desde muchos lugares
La unidad de programa, nunca cambia  no necesita ser recompilada
El resto de las unidades de programa dentro del package suele cambiar y en

consecuencia el package entero necesita ser recompilado.
En este caso, sería importante separar esa unidad de programa del package,
porque si no lo hacemos, cada vez que se modifique el package en cuestión se
marcará como inválidos todos los programas que lo usen, aunque estos no hallan
cambiado.
NOTA: Sobre lo anterior, es importante saber que si recompilamos el BODY de

un package, no habrá necesidad de recompilar todos los programas que
referencien el package, pero si recompilamos la especificación del mismo,
entonces sí habrá que recompilar todas los objetos de la base que lo referencien.
Entonces, como regla: Es conveniente separar aquella funcionalidad que

rara vez cambia, de aquella que suele hacerlo. Si no lo hacemos, habrá que
compilar el package, acción que invalidará todos los programas que lo usen, y
estos también necesitaran ser recompilados.
Sobre la inicialización de packages:
Supongamos una sección de inicialización de un package que tiene también una

sección de manejo de excepciones:
CREATE OR REPLACE PACKAGE valerr
IS
FUNCTION private_variable RETURN VARCHAR2;
END valerr;
CREATE OR REPLACE PACKAGE BODY valerr
IS
g_private VARCHAR2(1) := ‘ABCDE’; –PROBOCARÁ EL DISPARO DE UNA

EXCEPCIÓN!!!
FUNCTION private_variable RETURN VARCHAR2
IS
BEGIN
RETURN g_private;
END private_variable;
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Before I show you v…’);
EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (‘Trapped the error!’);
END valerr;
Que ocurrirá cuando ejecutemos el programa?
ERROR at line 1:
ORA-06502: PL/SQL: numeric or
value error: character string
buffer too small
Pero no había una sección de cacheo de errores? Por otro lado, para hacerlo mas
confuso, si corremos este programa una vez mas, este no dará error! Porque
sucede esto?
La sección EXCEPTION de la inicialización de un package, solo captura errores

que se produzcan en la sección ejecutable del bloque de inicialización.
Cuando la inicialización de un package falla, Oracle marca el paquete como

inicializado. Y como todos los paquetes se inicializan solo una vez por sesión,
este no vuelve a inicializarse durante la vida de la sesión que lo inicializó.
Resumiendo el enfoque orientado a la Performance
 Mirar las áreas donde pueda ser posible la aplicación de FOR ALL &
BULK COLLECT.
 Buscar todas las sentencias DML. Si estas están dentro de algún tipo de
LOOP, externalizarlas en un Procedure de ámbito Local en la sección de
declaración, buscando aumentar el uso del procesamiento BULK.
10 Mitos que los programadores de PL/SQL deberíamos conocer!
Nunca pedir ayuda.
Grave error, sin importar nuestro nivel de Senority siempre debemos hacerlo. La
siguiente sugerencia puede producir un equilibrio práctico: Si no podemos
encontrar el problema después de 30 minutos… Pidamos ayuda!
La cantidad de cafeína consumida incide en forma directamente proporcional a

nuestras facultades de concentración y productividad.
Siempre es mejor beber agua para hidratar nuestro cuerpo que abusar de la
cafeína…
Nunca compartir nuestro conocimiento! Esto nos da poder y seguridad.
De hecho, desde Oracle.Com podemos disponer de muchos recursos tales como

Papers, Código, Sugerencias, etc de manera libre y gratuita, algunas de las
presentes sugerencias están tomadas de allí.
Nunca dudar de los gurus.
Siempre debemos dejar que nuestra realidad termine de aceitar los consejos que
recibimos, ellos también se equivocan.
Nunca ocultar los detalles de nuestro código
Pero ocultar no es sinónimo de algo negativo en el contexto de la programación?

No si refiere a detalles de la implementación. Siempre debemos ocultar los
detalles, solo así podremos programar para el cambio.
Nunca permitamos que alguien mas lea y observe nuestro código!
La revisión de código constituye un arma sumamente útil contra los bugs y

problemas de diseño a bajo nivel.
Nunca te preocupes por el mañana, codifiquemos para el hoy!
Es vital comprender la naturaleza dinámica y cambiante de las reglas del

negocio representadas desde el modelo de datos hacia el front end.
Nunca corrijas bugs que otros programadores no hallan encontrado…
Falso. El Bug de hoy, es el bug de mañana. Por supuesto que un repositorio de

código soportado por un controlador de versiones nos dará mas valentía para tal
empresa.
Nunca asumamos que un Bug de Oracle, fue corregido.
Oracle sugiere confiar en la disponibilidad de la plataforma y la evolución

positiva de las misma.
Nunca digas Nunca!
En el contexto del desarrollo de software, podemos asegurar que ese enunciado

es falso. Sirva de ejemplo, las siguientes verdades universales en el mundo del
desarrollo:
Las cosas nunca permanecerán iguales… o a lo que es equivalente
Las cosas siempre cambiaran…
Unas palabras sobre Refactorizar

Al terminar de desarrollar una unidad, no esta de mas dedicar unos minutos al
análisis detenido de nuestro código buscando secciones que requieran una
refactorización. La belleza esta en los detalles…
Podemos definir Refactorización, como el proceso de transformación del código

escrito mediante el cual sin modificar la conducta externa y esperada, se mejora
la estructura y la escritura del código. Es decir, nos abstraemos del Que, y nos
concentramos en el Como.
Dos objetivos son sumamente relevantes en el proceso de refactorización:
Simplificar DONDE las cosas son realizadas. (Reorganizar las estructuras del
código, moviendo este de un lugar a otro a efectos de aumentar su legibilidad,
modificabilidad y rehuso)
Simplicar COMO las cosas son realizadas (Haciendo el código mas elegante,
mas compacto y fácil de seguir).
Pautas para refactorizar nuestros desarrollos:
Si en una unidad se llama con mucha frecuencia a la funcionalidad de un

package, debemos preguntarnos si no sería mas apropiado que la unidad este
contenida en dicho paquete.
Prestar atención a los puntos donde se utilice lógica booleana compleja. Es muy
probable que mediante las reglas matemáticas de la lógica Booleana
normalmente conocida por los profesionales, una gran expresión sea equivalente
a otra mucho mas simple. Por ejemplo supongamos una función que retorna lo
siguiente
RETURN ((V_booleana1 And V_booleana2) OR (NOT V_booleana1 And NOT

V_booleana2))
El sentido común asi como la lógica booleana nos dicen que esa expresión es
equivalente a la siguiente:
RETURN (V_booleana1 = V_booleana2)
Observar aquellos lugares donde el código se extiende demasiado. Esta claro que
existen problemas que requieren la escritura de miles de líneas, no es el volumen
de las líneas lo que esta en discusión, sino la estructura que se le de a las
mismas. Es muy probable que podamos ocultar detalles del mismo en Procedure
o Function anidadas y de esta manera simplificar las cosas. Como referencia,
debemos pensar que desde el BEGIN al END nunca deberíamos tener mas
de 50 líneas, es mas, un promedio de 20 a 30 líneas es ampliamente
recomendado. Si esto se hace con un criterio Top Down, seguramente será
mucho mas sencillo de seguir que un monolítico bloque ejecutable, o muchas
unidades encadenadas sin criterio arquitectural.
Cuidar el acoplamiento de las unidades. Si tenemos unidades de programa
exageradamente parametrizadas, además de restarle claridad al código, es muy
probable que tengamos un problema de coupling en nuestro diseño.
Cuidar de mantener simples las estructuras condicionales:
Las expresiones son a menudo mas legibles y entendibles cuando se escriben en

forma positiva, es decir el abuso del operador NOT puede restar claridad a
nuestro código.
Mantener el nivel de anidamientos bajo. Preferiblemente una estructura If

anidada no debería tener mas de dos o tres niveles. Si es necesario ocultar mas
niveles de anidamiento, deberíamos usar Procedimientos Locales Anidados.
Cuando una vez no es suficiente:. Los loops o interacciones, se cuentan entre

los elementos mas sensibles a la performance y los errores. Por supuesto, Oracle
provee varias estructuras para implementar iteraciones, y el uso de cada una
dependerá de las circunstancias y no es materia de discusión de las presentes
líneas. Ahora bien, existe una guía que bien puede ser tomada como una máxima
a la hora de diseñar nuestras estructuras condicionales: Una forma de entrar,
Una forma de salir. Esto significa que solo existe una manera de entrar a una
iteración o de comenzar ese Loop, y que existe un solo punto donde la iteración
termina. Una estructura Loop que tenga tres RETURN, no estaría respetando
este principio.
Optimización de SQL-Pl-SQL Trazas – tkprof
Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL
Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL
o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle.
En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas
estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL,
utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay
necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la
poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el
formato de dicho comando y lo complejo que resulta analizar el contenido de la
tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el
uso del comando SQL*Plus AUTOTRACE.
En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una

excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE
es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando
EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:
SET AUTOTRACE OFF – Deshabilita el análisis (traceado) de las sentencias SQL.
SET AUTOTRACE ON – Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY – Habilita el análisis (traceado) de las sentencias SQL
pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos
analizando el rendimiento de la sentencia y no nos interesa conocer los registros que
pueda devolver.
SET AUTOTRACE ON/TRACEONLY EXPLAIN – Muestra el plan de ejecución de la

sentencia pero no muestra las estadísticas.
SET AUTOTRACE ON STATISTICS – Muestra las estadísticas pero no muestra el plan

de ejecución de la sentencia.
Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una

sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.
Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear
la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo
puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada
tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido
configurados para poder ejecutar el comando AUTOTRACE.
Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del
sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los
DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script
puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script
como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.
Una vez que se ha configurado convenientemente un usuario para que pueda acceder al
plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al
ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los
correspondientes valores estadísticos.
Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez
activado el comando AUTOTRACE, son los siguientes:
- DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria

caché.
- Consistent gets: Número de operaciones de entrada/salida realizadas sobre los
segmentos de rollback debido a cambios en la memoria caché.
- Physical reads: Número de bloques leídos desde el disco.
- Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
- Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.
A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos

conseguir que el número de db block gets, consistent gets y physical reads sea bajo
comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la
ordenación de los datos debe realizarse, siempre que sea posible, en memoria.
En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un
ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve
interpretación de dicho plan: Ejemplo de plan de ejecución.
Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar
que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros
de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL
SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser
conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un
índice.
El optimizador PL/SQL basado en normas (Rule-Based Optimizer)
Veremos algunas de las características del optimizador PL/SQL basado en

normas (Rule-Based Optimizer). Lo primero que quiero mencionar es que
Oracle recomienda utilizar el optimizador PLSQL basado en costes (cost-based
optimizer), no obstante, en algunos casos, el hecho de tener que activar las
estadísticas de la base de datos para poder utilizar este último optimizador,
puede hacer que resulte interesante utilizar el optimizador basado en normas y
dejar las estadísticas desactivadas para no afectar al rendimiento de la base de
datos.
El optimizador PLSQL basado en normas utiliza siempre que puede los índices, incluso
cuando las tablas son pequeñas o cuando el número de registros que devuelve la
sentencia SELECT es un porcentaje elevado con respecto al número total de registros de
la tabla, casos para los que es mejor realizar un escaneado total (full scan) ya que la
respuesta es más rápida (mejora el rendimiento). Esto es debido a que el optimizador
basado en normas no hace uso de valores estadísticos, tales como el número total de
registros de una tabla.
El optimizador PL/SQL basado en normas hace uso del siguiente orden de prioridades
para determinar cual va a ser la forma de acceder a las tablas y determinar finalmente
cual va a ser el plan de ejecución:
Prio Forma de acceso

1 Single row by ROWID
2 Single row by cluster join
3 Single row by hash cluster key with unique or
primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite index
9 Single-column index
10 Bounded range search on indexed column
11 Unbounded range search on indexed column
12 Sort-merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed column
15 Full table scan
Los distintos métodos de acceso los he dejado en inglés, ya que es bastante

complicado traducir esta terminología. En el presente artículo no voy a explicar
cuales son las diferencias existentes entre las distintas formas de acceso. No
obstante, en sucesivos artículos pondré algunos ejemplos que permitirán
diferenciar estos conceptos.
Siguiendo con el tema que concierne a este post, el optimizador basado en normas
analiza la sintaxis de la sentencia SQL para establecer los distintos métodos de acceso a
las tablas. Básicamente lo que hace es determinar todas las formas de acceso posibles y
escoger aquella que tiene una prioridad menor.
Este esquema siempre asume que un escaneado total (full scan) es el peor método de
accesso (prioridad 15). Sin embargo, ya he mencionado al principio del artículo que esto
no siempre es verdad.
Estos métodos de acceso, así como otros adicionales, están también disponibles para el
optimizador PL/SQL basado en costes. Sin embargo, este optimizador ignora las
prioridades, y determina el coste esperado de ejecución de la sentencia SQL para cada
uno de las formas de acceso posibles basándose en las estadísticas, escogiendo después
aquella forma de acceso con el menor coste estimado. Muchas funcionalidades del
Oracle, como los hash joins, star queries e histogramas, sólo están disponibles para el
optimizador PLSQL basado en costes.
Hints en PL/SQL para el modo de optimización
Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el
plan de ejecución de una sentencia DML (sentencias de manipulación de datos
como select, insert, update, delete, etc) según nosotros le aconsejemos. En este
primer artículo sobre los hints voy a empezar hablando de aquellos que se
utilizan para seleccionar el modo de trabajar del optimizador Oracle. Estos hints,
hablando desde un punto de vista práctico, no son muy utilizados, aunque no por
ello pueden dejar de ser útiles en determinadas circunstancias.
Los hints se incorporan a una sentencia DML en forma de comentario y deben ir justo
detrás del comando principal. Por ejemplo, si se tratara de una sentencia SELECT el
formato sería el siguiente:
SELECT /*+ COMANDO-HINT */ …
Estos son los hints que se pueden utilizar para cambiar el modo de optimización del
optimizador Oracle:
/*+ RULE */ – Fuerza a que se utilice el optimizador basado en normas (rule-based

optimizer). Con este optimizador los planes de ejecución cambían según sea la sintaxis
de la sentencia DML, no utiliza las estadísticas asociadas con las tablas de la base de
datos Oracle y no calcula los costes del plan de ejecución. El optimizador basado en
normas no ha cambiado desde la versión 6 de Oracle.
/*+ CHOOSE */ – Fuerza a que se utilice el optimizador basado en costes (cost-based

optimizer). Este optimizador construye los planes de ejecución basándose en las
estadísticas almacenadas en el diccionario de datos. Tiene en consideración el número
de lectura lógicas (el factor más importante), la utilización de la CPU junto con los
accesos a disco y a memoria, y el uso de la red (cuando los datos residen en diferentes
servidores). Una de las ventajas de utilizar el optimizador basado en costes es que
Oracle lo está mejorándolo continuamente.
/*+ ALL_ROWS */ – Fuerza a que se utilice el optimizador basado en costes y

optimiza el plan de ejecución de la sentencia DML para que devuelva todas las filas en
el menor tiempo posible. Es la opción por defecto del optimizador basado en costes y es
la solución apropiada para procesos en masa e informes, en los que son necesarias todas
las filas para empezar a trabajar con ellas.
/*+ FIRST_ROWS (n) */ – También fuerza a que se utilice el optimizador basado en

costes y optimiza el plan de ejecución de la sentencia DML para que devuelva las “n”
primeras filas en el menor tiempo posible. Esto es idóneo para procesos iterativos y
bucles, en los que podemos ir trabajando con las primeras filas mientras se recuperan el
resto de resultados. Obviamente este hint no será considerado por el optimizador si se
utilizan funciones de grupo como MAX, SUM, AVG, etc.
Hints en PL/SQL para determinar el método de acceso
Ya hemos hablado de los hints para el modo optimización. En este apartado

continuaremos hablando de los hints pero, en concreto, de aquellos que permiten
indicar al optimizador Oracle el modo en que se debe acceder a los datos de las
tablas. Este tipo de hints resultan extremadamente eficaces a la hora de
optimizar una sentencia SQL.
En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la
muestre de nuevo:
{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */
{ DELETE | INSERT | SELECT | UPDATE } –+ HINT (parámetros)
Los hints básicos que sirven para determinar el metodo de acceso a los datos de una
tabla Oracle son los siguientes:
/*+ FULL (nombre_tabla) */ – Fuerza a que se realice la búsqueda accediendo a todos

los registros de la tabla indicada. Cuando las tablas tienen un número reducido de
registros puede resultar bueno para el rendimiento de una sentecia DML el forzar un
escaneado completo de la tabla en lugar de que el optimizador decida acceder a dicha
tabla mediante un índice, ya que, en estos casos, el acceso por índice suele ser más
lento.
/*+ ROWID (nombre_tabla) */ – Fuerza a que se acceda a la tabla utilizando el

ROWID (identificativo único de los registros de una tabla). Este tipo de hint, por si solo,
no es muy útil.
/*+ INDEX (nombre_tabla [nombre_índice] …) */ – Fuerza a que se acceda a la tabla
utilizando, en sentido ascendente, el índice indicado. Muchos problemas de rendimiento
vienen causados por el hecho de que el optimizador Oracle decide acceder a una tabla
utilizando un índice incorrecto. Mediante este hint podemos indicarle al optimizador
que utilice el índice que nosotros consideremos adecuado.
/*+ INDEX_DESC (nombre_tabla [nombre_índice] …) */ – Idéntico al anterior hint

pero en este caso el acceso a través del índice se hace en sentido descendente.
/*+ AND_EQUAL (nombre_tabla [nombre_índice] …) */ – Este hint se utiliza para

forzar el uso de más de un índice (se utilizarían los índices indicados como parámetros)
y, después, fusionar los índices quedándose con los registros encontrados en todas las
búsquedas por índice realizadas.
/*+ INDEX_FFS (nombre_tabla [nombre_índice] …) */ – Fuerza el acceso a los

datos de la tabla mediante una búsqueda (Scan) rápida (Fast) y total (Full) sobre el
índice indicado. Es parecido a utilizar el hint FULL pero sobre un índice en lugar de una
tabla, lo cual, difícilmente, puede ser bueno para el rendimiento de una sentencia DML.
/*+ NO_INDEX (nombre_tabla [nombre_índice] …) */ – Indica al optimizador que

no se utilicen los índices indicados. Puede ser útil cuando no tengamos claro cual es el
mejor índice que debe ser utilizado para acceder a una tabla pero, por contra, sepamos
que podemos tener problemas de rendimiento si se accede a la tabla por un determinado
índice y queramos evitar que esto ocurra.
Puesta a punto de sentencias SQL
Si la siguiente sentencia SELECT:
SELECT *
FROM empleados
WHERE nombre = ‘Francisco’
AND estado_civil = ‘S’ — Soltero
nos está dando tiempos de ejecución largos, esto querrá decir que:
1. Obviamente la tabla empleados es de un tamaño considerable.
2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice

adecuado, lógicamente un índice sobre la columna “nombre”, el optimizador SQL
decide utilizar el índice sobre otra columna.
Si tenemos problemas de “performance” con la sentencia SELECT de arriba y

suponiendo que tenemos un índice sobre la columna “nombre”, esto nos debe llevar a
pensar que existe otro índice sobre la columna “estado_civil” que es el que el
optimizador está utilizando para ejecutar la sentencia.
Los índices sobre columnas de este tipo, con un rango de valores pequeño (soltero,
casado, viudo,…) y con una distribución de valores más o menos homogénea para
algunos de estos valores (podemos pensar que el número de solteros va a ser similar al
de casados y bastante mayor que el de viudos), van a causar problemas en los tiempos
de ejecución. Este tipo de índices sólo son interesantes cuando uno de los posibles
valores que puede tomar la columna es mucho menos numeroso que el resto y la
sentencia SQL la vamos a limitar en base a dicho valor. Por ejemplo, si nuestra
sentencia fuera:
SELECT *
FROM empleados
AND estado_civil = ‘V’ — Viudo
Podemos tener la certeza de que los tiempos de ejecución no serían tan malos. En
cambio, cuando hacemos la comparación “estado_civil = ‘S’”, estamos practicamente
haciendo un “full scan” sobre un índice y esto es incluso peor que hacer un “full scan”
sobre una tabla.
¿Qué tenemos que hacer para arreglar la primera sentencia? Debemos forzar al
optimizador a usar el índice sobre la columna “nombre”, para ello tenemos dos
opciones:
SELECT *
FROM empleados
AND estado_civil!!” = ‘S’ — Soltero
Al añadir !!” el optimizador no podrá utilizar el índice sobre la columna estado_civil.
También, suponiendo que el identificador del índice sobre la columna “nombre” es

INXEMPLEADOSNOMBRE, podemos añadir el siguiente “hint” en nuestra sentencia
SELECT:
SELECT * /*+ INDEX(empleados INXEMPLEADOSNOMBRE) */

FROM empleados
AND estado_civil = ‘S’ — Soltero
Esto hace que el optimizador utilice el índice sobre la columna “nombre” aunque exista
otro índice sobre la columna “estado_civil”.
Cómo usar la utilidad de trazado del SQL de Oracle
La utilidad de trazado del SQL de las bases de datos Oracle nos permite analizar
el rendimiento de un determinado programa PL/SQL. Esta funcionalidad nos va
a permitir obtener información acerca del rendimiento de todas las sentencias
SQL que se ejecuten durante la ejecución del programa PLSQL.
Para utilizar la herramienta de trazado del PL/SQL de Oracle deberemos seguir cinco
pasos:
1) Inicializar los parámetros relativos a esta funcionalidad SQL.

2) Activar la traza SQL.
3) Ejecutar la aplicación que queremos analizar y desactivar la traza cuando termine.
4) Formatear el fichero producido por la traza SQL con el comando TKPROF.
5) Interpretar la salida del comando TKPROF y, si es necesario, optimizar nuestro
programa PLSQL.
En este artículo voy a hablar sobre los tres primeros apartados.
Inicialización de los parámetros de trazado Oracle
La utilidad de trazado puede, opcionalmente, proporcionar información acerca de los

tiempos de ejecución. Para que esta información quede almacenada es necesario activar
el parámetro TIMED_STATISTICS.
Dicho parámetro se puede activar a nivel de base de datos mediante su inclusión en el

fichero de parámetros de la base de datos Oracle (nota: una vez incluido el parámetro es
necesario reinicializar la base de datos para que el cambio tenga efecto):
TIMED_STATISTICS = TRUE
Este parámetro también se puede asignar dinámicamente a nivel de sesión ejecutando el

siguiente comando:
SQL> ALTER SESSION SET timed_statistics=true;
La activación de este parámetro puede afectar ligeramente al rendimiento de la base de

datos por lo que normalmente este parámetro está desactivado
Existen otros dos parámetros que nos permiten controlar el tamaño y el nombre del
directorio donde se generará el fichero de trazado:
MAX_DUMP_FILE_SIZE = n
USER_DUMP_DEST = nombre_directorio
El valor por defecto del parámetro MAX_DUMP_FILE_SIZE es 500, es decir, que

nuestro fichero de trazado podrá ocupar 500 bloques del disco duro. Este parámetro
puede cambiar también a nivel de sesión con el comando ALTER SESSION.
El valor por defecto del parámetro USER_DUMP_DEST depende del sistema operativo
y no puede ser cambiado a nivel de sesión. Por lo tanto, al ser un parámetro global del
sistema, su valor sólo pueden cambiarlo los administradores de la base de datos
utilizando el comando ALTER SYSTEM.
Para obtener información acerca de los valores que toman los distintos parámetros
podemos ejecutar la siguiente sentencia:
SELECT name, value

FROM v$parameter
WHERE name LIKE ‘%dump%’
Obviamente se necesita tener acceso a la vista (view) V$PARAMETER para poder

visualizar esta información.
Activación de la traza SQL
Podemos activar la traza a nivel de sistema mediante la inclusión en el fichero de

parámetros de la siguiente línea:
SQL_TRACE = TRUE
Esta posibilidad es poco recomendable y no se debe implementar en ningún sistema en

producción. La activación de la traza a nivel de sistema puede afectar seriamente el
rendimiento de la base de datos Oracle.
Por ello es mucho más recomendable activar la traza a nivel de sesión con el comando:
SQL> ALTER SESSION SET sql_trace = true;
También se puede utilizar el paquete estándar DBMS_SESSION. Esta posibilidad es

particularmente útil si queremos activar y desactivar la traza dentro del un
procedimiento o función PL/SQL.
SQL> EXECUTE dbms_session.set_sql_trace (true);
Por otro lado, los DBA (Database Administrators) pueden activar la traza sobre una
sesión de usuario concreta utilizando el siguiente comando:
SQL> EXECUTE dbms_system.set_sql_trace_in_session (session_id,

serial_id, true);
Los valores session_id y serial_id adecuados los tiene que identificar el DBA mediante
el análisis de los registros incluidos en la vista V$SESSION, los campos de esta vista
que se corresponden con estos valores son SID y SERIAL#.
Desactivación de la traza SQL
Cuando la ejecución del programa que estamos optimizando termina, debemos proceder
a desactivar la traza utilizando cualquiera de los métodos mencionados anteriormente,
sustituyendo la palabra TRUE por FALSE.
Si la traza se activó a nivel de sesión, entonces cuando la sesión termina, la traza se

desactiva automáticamente.
Identificación del fichero de trazado
Para identificar los ficheros de trazado debemos ir al directorio especificado por el

parámetro USER_DUMP_DEST y, normalmente, el fichero de trazado será aquel que se
ha generado más recientemente.
La identificación puede complicarse cuando hay varios usuarios generando ficheros de

trazado al mismo tiempo. En este caso podemos utilizar un script estándar denominado
readtrace.sql. Este script crea un procedimiento que abre nuestro fichero de trazado
utilizando el paquete UTL_FILE. El nombre por defecto del fichero de trazado que se
genera es username.trc pero puede cambiarse fácilmente.
SQL> @readtrace.sql
SQL> ALTER SESSION SET sql_trace = true;
SQL> SELECT * FROM nombre_tabla;
SQL> execute gettrace (‘nombre_fichero_trazado’);
El siguiente paso sería utilizar el programa TKPROF para poder interpretar el contenido
binario del fichero de trazado, pero esto será objeto de otro artículo.
Sql loader
Sql Loader es una utilidad que proporciona Oracle para cargar datos a una base
de datos desde un fichero externo, normalmente un fichero de texto aunque
tambien pueden ser ficheros binarios.
Al SQLLoader ( sqlldr) se le pasan como parametros (los más importanes) el

fichero que contiene los datos que se van a cargar y la ruta del fichero de control
que contiene las acciones a realizar. El formato de los datos, donde se cargaran y
cualquier otro tipo de control.
? Durante la ejecución genera tres tipos de ficheros:
1.- Fichero Log.

2.- Fichero Bad.
3.- Fichero Discard.
Las características de estos ficheros son:
1.- Log ? Muestra estadísticas de la carga.

2.- Bad ? Almacena los registros rechazados por datos incorrectos.
3.- Discard ? Almacena los registros que no han cumplido los criterios de
selección de la cláusula WHEN.
La cláusula WHEN carga aquellas filas que cumplen los criterios

especificados en ella.
? SQL-Loader utiliza dos mecanismos para cargar datos, estos mecanismos son:
1.- Path Convencional.

2.- Path Directo.
1.- Path Convencional
Este mecanismo utiliza un buffer llamado BIND ARRAY. El mecanismo se basa

en lo siguiente: Se leen varios registros que son almacenados en el Bind array,
cuando este se llena o no quedan más datos para leer se pasan a Oracle para que
inserte los datos en las tablas.
2.- Path Directo.

Este mecanismo consiste en colocar los registros en bloques de la Base de datos
ya formatados y escritos directamente en la Base de datos, pasando por alto la
mayor parte de procesamiento RDBMS.
Este mecanismo es más rápido que el convencional, pero tiene muchas más
restricciones.
? Para realizar una carga correcta de la información se necesita un fichero que

interprete los campos de los registros con el fin de almacenarlos de forma
adecuada. Pues este fichero es el fichero de control.
? Por otra parte tenemos los datos. Estos pueden ir en el mismo fichero de
control. Los datos se pueden cargar en diferentes formatos:
1.- Formato Binario ? No puede tratar campos o columnas de longitud variable.

2.- Formato Carácter ? Puede tratar tanto campos o columnas de longitud fija
como variable.
Para los formatos variables, el Sistema Operativo utiliza una marca de final de
registro (como el newline).
? Existen dos tipos de limitadores que se utilizan para delimitar los campos:
1.- Terminated. ? Campos seguidos por un carácter especificado.

Ejemplo la coma: 1,2,3……
2.- Enclosed. ? Campos precedidos y seguidos por un carácter especificado.

Ejemplo las comillas: “ENCLOSED”.
? Se distingue entre dos tipos de registros:
1.- Registros físicos ? Se consideran registros físicos cada linia del fichero de
donde deseamos cargar los datos a Oracle.
2.- Registros lógicos ? Se consideran registros lógicos cada fila de la tabla de

Base de datos.
Se conoce como continuation fields aquella combinación de registros físicos

que forman un sólo registro lógico.
ESTRUCTURA DEL ARCHIVO DE CONTROL
El archivo de control suele tener como mínimo la siguiente estructura:
LOAD DATA
INFILE ………………
INTO TABLE ………………

[BEGIN DATA]
……………………….
……………………….
……………………….
Explicación del uso de las sentencias anteriores:
LOAD DATA ? Esta declaración es obligatoria en el fichero de control. Siempre

debe aparecer al principio de este.
INFILE ? En este parámetro se especifica de donde se van a extraer los datos a

almacenar en las tablas. Es decir, si se pone un “*”, le estamos diciendo al SQL-
Loader que los datos se encuentran en el mismo fichero de control, justo después
de la sentencia BEGIN DATA. Por otro lado, se puede indicar, al SQL-Loader,
que coja los datos de un fichero de la siguiente manera: INFILE
‘nombre_fichero’.
- Se pueden poner tantos INFILE´s como ficheros de datos deseemos cargar.
- Sí no se especifica la extensión del fichero en INFILE, por defecto se utilizará

“.dat”.
Ejemplos:
? Carga de datos a partir de unos datos incluidos en el fichero de control:
INFILE *
…
…
BEGIN DATA
…………..
…………..
? Carga de datos a partir de un fichero de datos especificado:
INFILE ‘C:\fichero_de_datos.dat’
…..
…..
[BEGIN DATA] ? No se debe poner
INTO TABLE ? En esta sentencia, se especifican las tablas que seran cargadas
con la información. Dependiendo del tipo de carga que se vaya a realizar, se
deben especificar los campos (columnas) de la tabla y su correspondiente
formato.
[BEGIN DATA] ? Se pone esta sentencia cuando los datos a cargar en las
tablas se encuentran en el fichero de control. Esta sentencia se debe poner
siempre despues de la última especificación de control, sino es así, podría
suceder que se interpretasen datos como sentencias de control.
Seguidamente, se muestra un fichero de control más detallado y con los
parámetros más usuales:
LOAD DATA ? Sentencia obligatoria
INFILE ‘<fichero_datos.dat>’
[INFILE] ‘<fichero_datos2.dat>’
…………
…………
[BADFILE] ‘<fichero_bad.bad>’
[DISCARDFILE] ‘<fichero_discard.dsc>’
[INSERT] ? Por defecto, la tabla debe estar vacia sino dá error.

[APPEND] ? Añade nuevas filas a la tabla.
[REPLACE] ? Elimina las filas existentes y añade las nuevas.
INTO TABLE <nombre_tabla>
[BEGIN DATA] ? Obligatorio sí y sólo sí, se ha especificado ‘*’ en INFILE.
[WHEN] ? Obligatorio sí y sólo sí, se ha declarado DISCARDFILE.
¿Cómo ejecutar SQL-Loader?
Dependiendo de la versión de Oracle en la que estemos trabajando, existe un

determinado SQL-Loader. Normalmente, el fichero ejecutable para Windows NT
suele llamarse sqlldrXX.exe donde XX es el número de la versión del producto.
Este fichero se suele almacenar en el directorio: C:\orant\bin .
Ejemplos:
Para la versión de Oracle 7.3.4.0.0 seria sqlldr73.exe

Para la versión de Oracle 8.0.5.0.0 seria sqlldr80.exe
Etc…
Para ejecutar el SQL-Loader, debemos llamar al programa anterior desde la linia

de comandos. La llamada seria algo como:
C:\> sqlldrXX argumento=valor [, argumento=valor,] …
Para saber que argumentos podemos utilizar con SQL-Loader, podemos utilizar
la ayuda de la siguiente manera:
C:\> sqlldrXX help=y
Imaginemos que los argumentos que estamos utilizando, son los que utilizamos
en la mayoría de ejecuciones de SQL-Loader. Pues, existe una manera de hacer
que estos argumentos sean almacenados en un fichero externo y que por medio
de la cláusula PARFILE sea llamado como si estuviese incluido en el mismo
fichero de control. Este método nos ahorra el tener que escribir largas líneas de
argumentos en la línea de comandos. Además de facilitar su entendimiento.
Lista de Argumentos
USERID ? Se debe poner el usuario y clave de Oracle, sino se pone el programa

lo solicita.
CONTROL ? Se debe poner el nombre del fichero de control. La extensión es

.CTL.
LOG ? Se debe poner el nombre del fichero de Log que guarda información del
proceso de carga. La extensión por defecto es .LOG.
BAD ? Se debe poner el nombre del fichero donde se almacenarán las causas de
los errores que provocaron las inserciones durante la carga. La extensión por
defecto es .BAD.
DATA ? Se debe poner el nombre del fichero de datos a cargar. Por defecto se
coge el nombre del fichero de control + la extensión .DAT.
[DISCARD] ? Nombre del fichero opcional de descartados. La extensión es

.DSC.
[DISCARDMAX] ? Parámetro que índica el número de descartados que se

permiten antes de acabar la carga. Para parar en el primer registro se debe poner
“0″. Por defecto su valor es “todos”.
[SKIP] ? Especifica el número de registros o filas que desde el principio del

fichero no deben ser cargados en la ejecución del SQL-Loader. Por defecto es
“0″.
[LOAD] ? Especifica el número máximo de registros que serán cargados,

después de saltar los especificados en la cláusula SKIP. Por defecto cargará
todos.
[ERRORS] ? Especifica el número máximo de errores que permiten que se

produzcan antes de acabar la carga. Para no permitir ningún error poner =0, para
permitirlos todos poner un valor extremadamente elevado. Por defecto, este
argumento permite 50 errores.
[ROWS] ? En Path convencional, este argumento especifica el número de filas

del BIND ARRAY (por defecto 64). Por otra parte, en Path directo este
argumento índica la cantidad entera de registros que deseamos cargar en la Base
de datos (Por defecto es salvar todos de una sóla vez al final de la carga).
[BINDSIZE] ? Especifica el tamaño máximo en Bytes del BIND ARRAY, este

argumento machaca el valor del argumento ROWS.
[SILENT] ? Suprime los mensajes que aparecen durante la ejecución. Tales
como cabeceras, pies de página, errores, descartes etc…
[DIRECT] ? Índica el modo Path a utilizar. Si su valor es TRUE usará el Path

Directo, si el valor es FALSE usará el Path Convencional (Por defecto)

Manual Completo de SQL

Cargado por

Copyright:

Formatos disponibles

Manual Completo de SQL

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Manual Completo de SQL

Cargado por

Copyright:

Formatos disponibles

PL-SQL

Un identificador es un nombre que se le pone a un objeto que interviene en un

:= (dos puntos + igual)

Operadores aritméticos + (suma)

Operadores relacionales o de comparación

<>, != (distinto de)

< (menor que)

> (mayor que)

>= (mayor o igual a)

<= (menor o igual a)

Operador de concatenación || Comentarios /* comentario de dos o más líneas */

– comentario de una línea

:= y DEFAULT son lo mismo. Si ponemos NOT NULL es obligatorio

num_dep NUMBER(2) NOT NULL :=20

num_emple VARCHAR2(15) DEFAULT ‘Pedro’

También se puede definir una variable a partir de un campo mediante los

%TYPE es la que se utiliza normalmente, %ROWTYPE es para claves de

nombre_constante CONSTANT tipo_de_dato := valor

Por ejemplo, el IVA es un valor fijo, y para declararlo lo haríamos de la siguiente

Imp_iva constant number(2,2) := 12,5

Funciones integradas de PL/SQL

PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A

Devuelve la fecha del sistema:

SELECT SYSDATE FROM DUAL;

El siguiente ejemplo devuelve 0 si el precio es nulo, y el precio cuando está

SELECT CO_PRODUCTO, NVL(PRECIO, 0) FROM PRECIOS;

Decode proporciona la funcionalidad de una sentencia de control de flujo if-

DECODE(<expr>, <cond1>, <val1>[, ..., <condN>, <valN>],

Esta función evalúa una expresión “<expr>”, si se cumple la primera condición

Es muy común escribir la función DECODE identada como si se tratase de un

‘ESP’, ‘ESPAÑA’, /* Si co_pais = ‘ESP’ ==> ‘ESPAÑA’ */

‘MEX’, ‘MEXICO’, /* Si co_pais = ‘MEX’ ==> ‘MEXICO’ */

‘PAIS ‘||co_pais)/* ELSE ==> concatena */

Convierte una expresión al tipo fecha. El parámetro opcional formato indica el

Este otro ejemplo muestra la conversión con formato de día y hora.

SELECT TO_DATE(’31/12/2006 23:59:59′,

Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el

Convierte una expresion alfanumérica en numerica. Opcionalmente podemos

SELECT TO_NUMBER (’10′)

Trunca una fecha o número.

SELECT TRUNC(SYSDATE)FROM DUAL;

Si el parámetro es un número devuelve la parte entera.

SELECT TRUNC(9.99)FROM DUAL;

Devuelve la longitud de un tipo CHAR.

Busca una cadena de caracteres dentro de otra. Devuelve la posicion de la

INSTR(<char>, <search_string>, <startpos>, <occurrence> )

Reemplaza un texto por otro en un expresion de busqueda.

REPLACE(<expresion>, <busqueda>, <reemplazo>)

El siguiente ejemplo reemplaza la palabra ‘HOLA’ por ‘VAYA’ en la cadena

SELECT REPLACE (‘HOLA MUNDO’,‘HOLA’, ‘VAYA’)– devuelve

SUBSTR(<expresion>, <posicion_ini>, <longitud> )

SELECT SUBSTR(‘HOLA MUNDO’, 6, 5) – Devuelve MUNDO

Convierte una expresion alfanumerica a mayúsculas.

SELECT UPPER(‘hola mundo’) – Devuelve HOLA MUNDO

Convierte una expresion alfanumerica a minúsculas.

SELECT LOWER(‘HOLA MUNDO’) – Devuelve hola mundo

Convierte un ROWID a tipo caracter.

Añade N veces una determinada cadena de caracteres a la derecha una

RPAD(<expresion>, <longitud>, <pad_string> )