2 de febrero de 2015

Cursores en Oracle

CURSORES EXPLÍCITOS

Se utilizan para trabajar con consultas que pueden devolver más de una fila. Hay
cuatro operaciones básicas para trabajar con un cursor explícito:

1- Declaración del cursor:
Dentro de la parte DECLARE ...
 CURSOR IS SELECT ;

Ejemplo:
 CURSOR c1 IS SELECT apellidos, nombre FROM CLIENTES;
En este momento sólo se asocia el nombre de un cursor a una sentencia, pero
en ese momento la sentencia SQL todavía no se ha ejecutado.

2- Apertura del cursor:
Esto se hace dentro del BEGIN. Se ejecuta de la siguiente forma:
 OPEN ;
Es en este momento en el que se ejecuta la cosulta asociada a dicho cursor.

3- Recogida de información:
Para hacer la consulta primero tenemos que abrir el cursor del que queremos
obtener los datos. La recuperación de los datos se hace de la forma siguiente:
 FETCH INTO { [, ]}
Las variables hay que ponerlas en el mismo orden en el que se han recuperado
las columnas en la consulta. Cada FETH recupera una fila de la consulta y, el
cursor avanza automáticamente a la fila siguiente. Puede ser necesario meter
esta sentencia en una estructura repetitiva para controlar la recogida de todas
las filas devueltas por la consulta.

4- Cerrar un cursor:
Cuando no se va a utilizar más el cursor, o una vez que recuperadas todas las
filas, el cursor se debe cerrar.
CLOSE nombre_cursor;

Atributos del cursor:
Para conocer detalles sobre la situación de un cursor, hay cuatro atributos para
consultar y la sintaxis de uso es %; los atributo son:

%FOUND: Este atributo devolverá TRUE cuando el último FETCH ha encontrado
una fila como resultado y será FALSE cuando no haya devuelto ninguna fila como
resultado.

Si el cursor no esta abierto devuelve error. Devolverá NULL cuando no se halla
ejecutado ningún FETCH después de haber abierto el cursor.

Ejemplo:
FETCH nombre_cursor INTO variables;
WHILE nombre_cursor%FOUND LOOP;
 FETCH nombre_cursor INTO variables;
 Sentencias;
 END LOOP;

%NOTFOUND: Hace lo contrario, devuelve el valor TRUE cuando al ejecutar una
FECTH no se ha encontrado ninguna fila en el resultado y, FALSE si el último
FETCH ha devuelto una fila.

Uso: LOOP
 FETCH nombre_cursor INTO variables;
Sentencias;
 EXIT WHEN nombre_cursor%NOTFOUND;
 END LOOP;

%ROWCOUNT: Devuelve el número de filas recuperadas por el cursor (número de
FETCH realizados satisfactoriamente. Sirve para recuperar un numero de filas en
concreto. Este número se irá incrementando cada vez que se ejecuta un comando
FETCH.

Uso: LOOP
 FETCH nombre_cursor INTO variables;
Sentencias;
 EXIT WHEN nombre_cursor%ROWCOUNT > 100;
 END LOOP;

%ISOPEN: Devuelve TRUE si el cursor está abierto y FALSE si está cerrado.

Uso: IF NOT nombre_cursor%ISOPEN THEN
 OPEN ;
 END IF;

Variables de acoplamiento:
 En muchas ocasiones la cláusula SELECT del curso deberá seleccionar las
filas de acuerdo con una condición. Cuando se trabaja con SQL interactivo se
introducen los términos exactos de la condición. Cuando se escribe un programa
PL/SQL los valores exactos de la condición sólo se conocen en tiempo de
ejecución.

Ejemplo:
CREATE OR REPLACE PROCEDURE ver_emple_po_dept ( dep VARCHAR2)
AS
 v_dept NUMBER(2);
 CURSOR C1 IS
 SELECT apellido FROM emple WHERE dept_no = v_dept;
 V_apellido VARCHAR”(10);
BEGIN
 v_dept := dep;
 OPEN C1;
 FETCH C1 INTO v_apellido;
 WHILE C1%NOTFOUND LOOP
 DBMS_OUTPUT.PUT_LINE(v_apellido);
 FETCH C1 INTO v_apellido;
 END LOOP;
 CLOSE C1;
END;

Así la variable de acoplamiento de este ejemplo es v_dept. El programa la
sustituirá por su valor en el momento en que se abre el cursor, y se seleccionarán
las filas según dicho valor. Aunque ese valor cambie durante la recuperación de los
datos con FETCH, el conjunto de filas que contiene el cursor no variará.

Una vez creado el procedimiento, se puede ejecutar:
SQL> EXECUTE ver_emple_por_dept (30);

Cláusula WHERE CURRENT OF:
Con esta cláusula nos podemos referir a la fila actual que hay en el fetch para
poder modificar los valores de dicha fila.

DECLARE
 V_cod_cliente VARCHAR2(2);
 CURSOR C1 IS SELECT cod_cliente, nombre FROM CLIENTES
 WHERE cod_cliente = ‘A1111’
 V_nombre VARCHAR(20);
BEGIN
 OPEN c1;
LOOP
 FETCH c1 INTO v_cod_cliente, v_nombre;
 UPDATE clientes SET v_nombre=’Pedro’ WHERE CURRENT OF c1;
END LOOP;
END;

CURSORES IMPLICITOS:

Cada vez que nosotros hacemos un SELECT sin usar ningún cursor, en
realidad el SQL esta creando un cursor, llamado SQL. De esta forma, cuando
nosotros sabemos que el resultado es un único valor, lo podemos introducir en una
variable de la siguiente forma:
SELECT INTO FROM [WHERE…] [etc…];
Las variables que siguen al INTO reciben el valor de la consulta. Deben
coincidir en en número y tipo con las columnas especificadas en la cláusula
SELECT.

Ejemplo:
 DECLARE
 V_ape VARCHAR(10);
 V_oficioVARCHA2(10);
 BEGIN
 SELECT apellido, oficio, INTO V_ape, V_oficio
FROM emple WHERE EMP_NO = 7900;
DBMS_OUTPUT.PUT_LINE( V_ape || ‘*’ || V_oficio);
 END;

Podemos usar las propiedades de los cursores, con la excepción de que no
hay nombre explícito y, tendremos que usar las propiedades de la siguiente forma:
 SQL%NOTFOUND, SQL%FOUND, SQL%ROWCOUNT y SQL%ISOPEN.

Este cursor creado con SELECT … INTO, debe devolver una única fila, pues
de lo contrario se producirá un error y se levantará automáticamente una
excepción:
- NO_DATA_FOUND, si la consulta no devuelve ninguna fila.
- TOO_MANY_ROWS, si la consulta devuelve más de una fila.
Se detendrá la ejecución normal del programa y bifurcará a la sección
EXCEPTION. Por tanto, la comprobación de la situación del cursor en esas
circunstancias resultará inútil.

Un cursor de este tipo nunca está abierto, porque cuando se ejecuta una
sentencia de este tipo hace el solo las sentencias OPEN, FETCH y CLOSE en la
misma sentencia, y por lo tanto la propiedad SQL%ISOPEN siempre devolverá
FALSO puesto que Oracle cierra automáticamente el cursor después de cada

orden.