• VARIABLES

Las variables se declaran en el apartado DECLARE del bloque. PL/SQL no acepta entrada ni salida de datos por sí mismo (para conseguirlo se necesita software auxiliar). La sintaxis de la declaración de variables es:

DECLARE
identificador [CONSTANT] tipoDeDatos [:= valorInicial];
[siguienteVariable…]
Ejemplos:
DECLARE
pi CONSTANT NUMBER(9,7):=3.1415927;
radio NUMBER(5);
area NUMBER(14,2) := 23.12;

El operador := sirve para asignar valores a una variable. Este operador permite inicializar la variable con un valor determinado. La palabra CONSTANT indica que la variable no puede ser modificada (es una constante). Si no se inicia la variable, ésta contendrá el valor NULL.
Los identificadores de Oracle deben de tener 30 caracteres, empezar por letra y continuar con letras, números o guiones bajos (_) (también vale el signo de dólar ($) y la almohadilla (#)<9. No debería coincidir con nombres de columnas de las tablas ni con palabras reservadas (como SELECT).

En PL/SQL sólo se puede declarar una variable por línea.

• Estructuras de decisión

instrucción IF

Se trata de una sentencia tomada de los lenguajes estructurados. Desde esta sentencia se consigue que ciertas instrucciones se ejecuten o no dependiendo de una condición
sentencia IF simple
Sintaxis:
IF condicion THEN
instrucciones

END IF;

Las instrucciones se ejecutan en el caso de que la condición sea verdadera. La condición es cualquier expresión que devuelva verdadero o falso. Ejemplo:

IF departamento=134 THEN
salario := salario * 13;
departamento := 123;
END IF;

sentencia IF-THEN-ELSE

Sintaxis:
IF condición THEN
instrucciones
ELSE
instrucciones
END IF;
En este caso las instrucciones bajo el ELSE se ejecutan si la condición es falsa.

sentencia IF-THEN-ELSIF

Cuando se utilizan sentencias de control es común desear anidar un IF dentro de otro IF.
Ejemplo:
IF saldo>90 THEN
DBMS_OUTPUT.PUT_LINE(‘Saldo mayor que el esperado’);
ELSE
IF saldo>0 THEN
DBMS_OUTPUT.PUT_LINE(‘Saldo menor que el esperado’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Saldo NEGATIVO’);
END IF;
END IF;
Otra solución es utilizar esta estructura:
IF condición1 THEN
instrucciones1
ELSIF condición2 THEN
instrucciones3
[ELSIF.... ]
[ELSE
instruccionesElse ]
END IF;

En este IF (que es el más completo) es evalúa la primera condición; si es verdadera se ejecutan las primeras instrucciones y se abandona el IF; si no es así

• CURSORES

introducción

Los cursores representan consultas SELECT de SQL que devuelven más de un resultado y que permiten el acceso a cada fila de dicha consulta. Lo cual significa que el cursor siempre tiene un puntero señalando a una de las filas del SELECT que representa el cursor.
Se puede recorrer el cursor haciendo que el puntero se mueva por las filas. Los cursores son las herramientas fundamentales de PL/SQL

procesamiento de cursores

Los cursores se procesan en tres pasos:

(1) Declarar el cursor
(2) Abrir el cursor. Tras abrir el cursor, el puntero del cursor señalará a la primera fila (si la hay)
(3) Procesar el cursor. La instrucción FETCH permite recorrer el cursor registro a registro hasta que el puntero llegue al final (se dice que hasta que el cursor esté vacío)
(4) Cerrar el cursor

declaración de cursores

Sintaxis:

CURSOR nombre IS sentenciaSELECT;

La sentencia SELECT indicada no puede tener apartado INTO. Lógicamente esta sentencia sólo puede ser utilizada en el apartado DECLARE.

Ejemplo:

CURSOR cursorProvincias IS
SELECT p.nombre, SUM(poblacion) AS poblacion
FROM localidades l
JOIN provincias p USING (n_provincia)
GROUP BY p.nombre;

apertura de cursores

OPEN cursor;
Esta sentencia abre el cursor, lo que significa:

(1) Reservar memoria suficiente para el cursor
(2) Ejecutar la sentencia SELECT a la que se refiere el cursor
(3) Colocar el puntero de recorrido de registros en la primera fila

Si la sentencia SELECT del cursor no devuelve registros, Oracle no devolverá una excepción. Hasta intentar leer no sabremos si hay resultados o no.

instrucción FETCH

La sentencia FETCH es la encargada de recorrer el cursor e ir procesando los valores del mismo:

FETCH cursor INTO listaDeVariables;

Esta instrucción almacena el contenido de la fila a la que apunta actualmente el puntero en la lista de variables indicada. La lista de variables tiene tener el mismo tipo y número que las columnas representadas en el cursor (por supuesto el orden de las variables se tiene que corresponder con la lista de columnas). Tras esta instrucción el puntero de registros avanza a la siguiente fila (si la hay).
Ejemplo:

FETCH cursorProvincias INTO v_nombre, v_poblacion;

Una instrucción FETCH lee una sola fila y su contenido lo almacena en variables. Por ello se usa siempre dentro de bucles a fin de poder leer todas las filas de un cursor:

LOOP
FETCH cursorProvincias INTO (v_nombre, v_poblacion);
EXITWHEN... --aquí se pondría la condición de salida
... --instrucciones de proceso de los datos del cursor
END LOOP;

cerrar el cursor

CLOSE cursor;

Al cerrar el cursor se libera la memoria que ocupa y se impide su procesamiento (no se podría seguir leyendo filas). Tras cerrar el cursor se podría abrir de nuevo.

atributos de los cursores

Para poder procesar adecuadamente los cursores se pueden utilizar una serie de atributos que devuelven verdadero o falso según la situación actual del cursor. Estos atributos facilitan la manipulación del cursor. Se utilizan indicando el nombre del cursor, el símbolo % e inmediatamente el nombre del atributo a valorar (por ejemplo cursorProvincias%ISOPEN)

%ISOPEN
Devuelve verdadero si el cursor ya está abierto.
%NOTFOUND
Devuelve verdadero si la última instrucción FETCH no devolvió ningún valor.

Ejemplo:

DECLARE
CURSOR cursorProvincias IS
SELECT p.nombre, SUM(poblacion) AS poblacion
FROM LOCALIDADES l
JOIN PROVINCIAS p USING (n_provincia)
GROUP BY p.nombre;
v_nombre PROVINCIAS.nombre%TYPE;
v_poblacion LOCALIDADES.poblacion%TYPE;
BEGIN
OPEN cursorProvincias;
LOOP
FETCH cursorProvincias INTO v_nombre,
v_poblacion;
EXIT WHEN cursorProvincias%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_nombre || ',' ||
v_poblacion);
END LOOP;
CLOSE cursorProvincias;
END;

En el ejemplo anterior se recorre el cursor hasta que el FETCH no devuelve ninguna fila. Lo que significa que el programa anterior muestra el nombre de cada provincia seguida de una coma y de la población de la misma.

%FOUND
Instrucción contraria a la anterior, devuelve verdadero si el último FETCH devolvió una fila.
%ROWCOUNT
Indica el número de filas que se han recorrido en el cursor (inicialmente vale cero). Es decir, indica cuántos FETCH se han aplicado sobre el cursor.

variables de registro

introducción
Los registros son una estructura estática de datos presente en casi todos los lenguajes clásicos (record en Pascal o struct en C). Se trata de un tipo de datos que se compone de datos más simple. Por ejemplo el registro persona se compondría de los datos simples nombre, apellidos, dirección, fecha de nacimiento, etc.
En PL/SQL su interés radica en que cada fila de una tabla o vista se puede interpretar como un registro, ya que cada fila se compone de datos simples. Gracias a esta interpretación, los registros facilitan la manipulación de los cursores ya que podemos entender que un cursor es un conjunto de registros (cada registro sería una fila del cursor).
declaración
Para utilizar registros, primero hay que definir los datos que componen al registro. Así se define el tipo de registro (por eso se utiliza la palabra TYPE). Después se declarará una variable de registro que sea del tipo declarado (es decir, puede haber varias variables del mismo tipo de registro).

Sintaxis:

TYPE nombreTipoRegistro IS RECORD(
campo1 tipoCampo1 [:= valorInicial],
campo2 tipoCampo2 [:= valorInicial],
...
campoN tipoCampoN [:= valorInicial]
);
nombreVariableDeRegistro nombreTipoRegistro;

Ejemplo:
TYPE regPersona IS RECORD(
nombre VARCHAR2(25),
apellido1 VARCHAR2(25),
apellido2 VARCHAR2(25),
fecha_nac DATE
);
alvaro regPersona;
laura regPersona;

uso de registros

Para rellenar los valores de los registros se indica el nombre de la variable de registro seguida de un punto y el nombre del campo a rellenar:
alvaro.nombre := 'Alvaro';
alvaro.fecha_nac := TO_DATE(‘2/3/2004’);
%ROWTYPE
Al declarar registros, se puede utilizar el modificador %ROWTYPE que sirve para asignar a un registro la estructura de una tabla. Por ejemplo:

DECLARE
regPersona personas%ROWTYPE;

personas debe ser una tabla. regPersona es un registro que constará de los mismos campos y tipos que las columnas de la tabla personas.

cursores y registros

uso de FETCH con registros

Una de las desventajas, con lo visto hasta ahora, de utilizar FETCH reside en que necesitamos asignar todos los valores de cada fila del cursor a una variable. Por lo que si una fila tiene 10 columnas, habrá que declarar 10 variables.
En lugar de ello se puede utilizar una variable de registro y asignar el resultado de FETCH a esa variable. Ejemplo (equivalente al de la página 154):

DECLARE
CURSOR cursorProvincias IS
SELECT p.nombre, SUM(poblacion) AS poblacion
FROM LOCALIDADES l
JOIN PROVINCIAS p USING (n_provincia)
GROUP BY p.nombre;
rProvincias cursorProvincias%ROWTYPE;
BEGIN
OPEN cursorProvincias;
LOOP
FETCH cursorProvincias INTO rProvincias;
EXIT WHEN cursorProvincias%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rProvincias.nombre || ‘,’ ||
rProvincias.poblacion);
END LOOP;
CLOSE cursorProvincias;
END;

bucle FOR de recorrido de cursores
Es la forma más habitual de recorrer todas las filas de un cursor. Es un bucle FOR que se encarga de realizar tres tareas:
(1) Abre un cursor (realiza un OPEN implícito sobre el cursor antes de empezar el bucle)
(2) Recorre todas las filas de un cursor (cada vez que se entra en el interior del FOR se genera un FETCH implícito) y en cada vuelta del bucle almacena el contenido de cada fila en una variable de registro. La variable de registro utilizada en el bucle FOR no se debe declarar en la zona DECLARE; se crea al inicio del bucle y se elimina cuando éste finaliza.
(3) Cierra el cursor (cuando finaliza el FOR)

Sintaxis:

FOR variableRegistro IN cursor LOOP
..instrucciones
END LOOP;

 

Esa sintaxis es equivalente a:
OPEN cursor;
LOOP
FETCH cursor INTO variableRegistro;
EXIT WHEN cursor%NOTFOUND;
...instrucciones
END LOOP;
Ejemplo (equivalente al ejemplo comentado en los apartados anteriores):
DECLARE
CURSOR cursorProvincias IS
SELECT p.nombre, SUM(poblacion) AS poblacion
FROM LOCALIDADES l
JOIN PROVINCIAS p USING (n_provincia)
GROUP BY p.nombre;
BEGIN
FOR rProvincias IN cursorProvincias LOOP
DBMS_OUTPUT.PUT_LINE(rProvincias.nombre || ‘,’ ||
rProvincias.poblacion);
END LOOP;
END;
Naturalmente este código es más sencillo de utilizar y más corto que los anteriores.

cursores avanzados

cursores con parámetros
En muchas ocasiones se podría desear que el resultado de un cursor dependa de una variable. Por ejemplo al presentar una lista de personal, hacer que aparezca el cursor de un determinado departamento y puesto de trabajo.
Para hacer que el cursor varíe según esos parámetros, se han de indicar los mismos en la declaración del cursor. Para ello se pone entre paréntesis su nombre y tipo tras el nombre del cursor en la declaración.
Ejemplo:
DECLARE
CURSOR cur_personas(dep NUMBER, pue VARCHAR2(20)) IS
SELECT nombre, apellidos
FROM empleados
WHERE departamento=dep AND puesto=pue;
BEGIN
OPEN cur_personas(12,’administrativo’);
.....
CLOSE cur_personas;
END

Es al abrir el. cursor cuando se indica el valor de los parámetros, lo que significa que se puede abrir varias veces el cursor y que éste obtenga distintos resultados dependiendo del valor del parámetro.
Se pueden indicar los parámetros también en el bucle FOR:
DECLARE
CURSOR cur_personas(dep NUMBER, pue VARCHAR2(20)) IS
SELECT nombre, apellidos
FROM empleados
WHERE departamento=dep AND puesto=pue;
BEGIN
FOR r IN cur_personas(12,’administrativo’) LOOP
.....
END LOOP;
END

actualizaciones al recorrer registros

En muchas ocasiones se realizan operaciones de actualización de registros sobre el cursor que se está recorriendo. Para evitar problemas se deben bloquear los registros del cursor a fin de detener otros procesos que también desearan modificar los datos.
Esta cláusula se coloca al final de la sentencia SELECT del cursor (iría detrás del ORDER BY). Opcionalmente se puede colocar el texto NOWAIT para que el programa no se quede esperando en caso de que la tabla esté bloqueada por otro usuario. Se usa el texto OF seguido del nombre del campo que se modificará (no es necesaria esa cláusula, pero se mantiene para clarificar el código).
Sintaxis:
CURSOR ...
SELECT...
FOR UPDATE [OF campo] [NOWAIT]
Ejemplo:
DECLARE
CURSOR c_emp IS
SELECT id_emp, nombre, n_departamento, salario
FROM empleados, departamentos
WHERE empleados.id_dep=departamentos.id_dep
AND empleados.id_dep=80
FOR UPDATE OF salario NOWAIT;

A continuación en la instrucción UPDATE que modifica los registros se puede utilizar una nueva cláusula llamada WHERE CURRENT OF seguida del nombre de un cursor, que hace que se modifique sólo el registro actual del cursor.

Ejemplo:

FOR r_emp IN c_emp LOOP
IF r_emp.salario<1500 THEN
UPDATE empleados SET salario = salario *1.30
WHERE CURRENT OF c_emp;