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.