ingeniero

TEMA 18: CURSORES, EXCEPCIONES Y CONTROL DE TRANSACCIONES EN PL/SQL

18.1 Cursores

Hasta el momento hemos venido usando cursores implícitos, este tipo de cursores es muy sencillo pero plantea un problema:
La subconsulta debe devolver una fila y sólo una, de lo contrario, se produciría un error. Por ello dado que normalmente una consulta devolverá varias filas, se suelen manejar cursoresexplícitos.

18.2 Cursores explícitos

Se utilizan para trabajar con consultas que pueden devolver mas de una fila. Hay 4 operaciones básicas para trabajar con un cursor explicito:
1) Declaración del cursor: el cursor se declara en la zona de declaraciones según la siguiente sintaxis:

CURSOR IS ;

2) Apertura del cursor: en la zona de instrucción habrá que abrir el cursor.

OPEN ;

3)Recogida de información: para recoger la información almacenada en el cursor, usaremos la siguiente sintaxis:

FETCH INTO {| };

Después del INTO aparecerá una variable que guardara la información de todas las columnas. En este caso la variable puede ser declarada.

%ROWTYPE

o una lista de variable. Cada una recogerá la columna correspondiente a la columna SELECT por lo tanto serán delmismo tipo de las columnas. Cada FETCH lo que hace es recuperar una fila, y el cursor avanza automáticamente a la siguiente fila.

4) Cierre del cursor: cuando el cursor no se va a utilizar hay que cerrarlo.

CLOSE ;


Ejemplo: visualizar el nombre y apellido de todos los clientes

DECLARE

CURSOR CURSOR_CLIENTE IS SELECT CLT_NOM,CLT_APELL FROM CLIENTES; -- DECLARACIÓN CURSORNOMBRE CLIENTES.CLT_NOM%TYPE;
APELLIDO CLIENTES.CLT_APELL%TYPE;

BEGIN

OPEN CURSOR_CLIENTE; -- APERTURA DEL CURSOR

FETCH CURSOR_CLIENTE INTO NOMBRE,APELLIDO; -- RECOGIDA DE DATOS
WHILE CURSOR_CLIENTE%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(NOMBRE||APELLIDO);
FETCH CURSOR_CLIENTE INTO NOMBRE,APELLIDO;

END LOOP;

CLOSE CURSOR_CLIENTE; -- CERRAR CURSOR

END;
/


Adiferencia de lo que ocurre con los cursores implícitos, la cláusula INTO no aparece con la SELECT, sino que aparece en FETCH


18.3. Atributos de cursores

Para conocer los detalles la situación del cursor, hay 4 atributos para consultar:

1) %FOUND, devuelve verdadero si el ultimo FETCH ha recuperado algún valor. En caso contrario devuelve falso. Si el cursor no esta abierto devuelve error. Ysi estaba abierto y no se había ejecutado aun ningún FETCH devuelve NULL. Se puede utilizar como condición de continuación en bucles para recuperar información.
2) %NOTFOUND, se suele utilizar como una condición de salida de bucles

EXIT WHEN %NOT FOUND;

3) %ROWCOUNT, devuelve el numero de filas recuperadas hasta el momento por el cursor.
4) %ISOPEN, devuelve verdadero si esta abierto.18.4 Variables de acoplamiento en el manejo de cursores

En una SELECT para introducir una condición usamos un WHERE, en PL/SQL se puede usar un diseño mas abierto porque los términos exactos de esa condición se conocen en tiempos de ejecución.

Ejemplo: consultar los clientes que viven en una determinada ciudad, mediante un procedimiento cuyo nombre es VIVEN_EN.

CREATE OR REPLACE PROCEDUREVIVEN_EN(A CLIENTES.CLT_POB%TYPE)

IS

CURSOR CURSOR_POB IS SELECT CLT_NOM FROM CLIENTES WHERE CLT_POB=A; -- DECLARACION CURSOR
VAR CLIENTES.CLT_NOM%TYPE;

BEGIN

OPEN CURSOR_POB; -- APERTURA DEL CURSOR
FETCH CURSOR_POB INTO VAR; -- RECOGIDA DE VARIABLE

WHILE CURSOR_POB%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(VAR);
FETCH CURSOR_POB INTO VAR;

END LOOP;

CLOSECURSOR_POB; -- CERRAR CURSOR

END;
/

Le pasamos Madrid:

VIVEN_EN('MADRID');

Que daría:

Miguel
Diego
Joaquin

PL/SQL procedure successfully completed.

Para obtener todos los datos utilizaríamos el %ROWTYPE de la siguiente forma:

CREATE OR REPLACE PROCEDURE VIVEN_EN(A CLIENTES.CLT_POB%TYPE)

IS

CURSOR CURSOR_POB IS SELECT * FROM CLIENTES WHERE CLT_POB=A; -- DECLARACION CURSOR...