/* Sesión10 – CursoresEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:i201010865@cibertec.edu.pe*/

Objetivos de la sesión.

Definir qué es un cursor en PL/SQL. Entender los diferentes tipos de cursores. Conocer las estrategias para controlar adecuadamente los cursores.

1) Definición y declaración de cursores2) Control de cursores3) Atributos de cursores4) Bucle FOR de Cursor5) Cursores con Parámetros6) Cláusula FOR UPDATE7) Cláusula WHERE CURRENT OF

1) Definición y declaración de cursores.

Útiles para las consultas que devuelven más de una fila. Son declarados y nombrados por el programador, y manipulados por

medio de sentencias específicas en las acciones ejecutables del bloque.

Sintaxis:

a) Sintaxis de declaración del Cursor

CURSOR nom_cursor ISSentencia select

No incluya la cláusula INTO en la declaración del cursor. Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.

Ejemplo de Declaración

DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal>200; BEGIN…END;/

b) Sintaxis de apertura del Cursor

OPEN nombre_cursor;

Si la consulta no devuelve ninguna fila, no se producirá ninguna excepción al abrir el cursor. Para los cursores declarados con la cláusula FOR UPDATE, la sentencia OPEN bloquea estas

filas.

c) Sintaxis de recuperación de datos

FETCH nombre_cursorINTO [variable1, …] [nombre_registro];

Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y en el mismo orden.

Compruebe si el cursor tiene filas.

c) Sintaxis de cierre del Cursor

CLOSE nombre_cursor;

Cierre el cursor una vez completado el procesamiento de las filas. Si es necesario, vuelva a abrir el cursor. No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR).

2) Control de cursores.1º. Crear un área SQL específica DECLARE2º. Identificar el juego activo OPEN3º. Cargar la fila actual en variables FETCH4º. Si todavía existen filas sin leer, volver a 3º.5º. Si no existen más filas a leer CLOSE

3) Atributos de Cursores

Proporcionan información de estado del cursor:

Atributos de Cursores

a) El Atributo %ISOPEN

Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperación para comprobar si el cursor está abierto.

Ejemplo:

IF NOT cursor_nombre%ISOPEN THEN OPEN cursor_nombre;END IF;LOOP FETCH cursor_nombre INTO [variable1, variable2, …] [variable_de_registro]END LOOP;

b) Atributos %NOTFOUND, %ROWCOUNT

Utilice %ROWCOUNT para recuperar un número exacto de filas. Utilice %NOTFOUND para determinar cuándo salir del bucle de lectura del cursor. Antes de la primera recuperación, %NOTFOUND es NULL, así que si FETCH no se

ejecuta nunca satisfactoriamente, no se saldría nunca del bucle de lectura. Ejemplo %NOTFOUND, %ROWCOUNT

LOOP FETCH cursor_nombre INTO vename, vdeptno; IF cursor_nombre%ROWCOUNT > 20 THEN … END IF EXIT WHEN cursor_nombre%NOTFOUND OR cursor_nombre%NOTFOUND IS NULLEND LOOP;

4) Bucles FOR de Cursor

Sintaxis

FOR nombre_registro IN nombre_cursor LOOP Sentencia1; …

END LOOP;

Apertura, recuperación y cierre implícitos. No declare el registro, está declarado implícitamente.

5) Cursores con Parámetros

Sintaxis 1

CURSOR nombre_cursor [(nombre_parámetro tipo_de_dato, …)]

IS sentencia_select;

Permite abrir un cursor varias veces con un juego activo distinto cada vez.

Sintaxis 2

CURSOR nombre_cursor nombre_parámetro [IN] tipo_de_dato [{:= | DEFAULT}] expresión]IS sentencia_select;

Cada parámetro formal de la declaración del cursor debe tener un parámetro real correspondiente en la sentencia OPEN.

Ejemplo

DECLARE CURSOR cursor_empleados (v_deptno NUBER, v_job VARCHAR2) IS SELECT ename, sal, hiredate FROM emp WHERE deptno = v_deptno AND title = v_job;BEGIN;

Transfiera el número de departamento y el cargo a la cláusula WHERE:

6) Cláusula FOR UPDATE

Sintaxis

SELECT … FROM …FOR UPDATE [OF nombre_columna] [NOWAIT]

El bloqueo explícito le permite denegar el acceso mientras dura una transacción. Bloquee las filas antes de la actualización o supresión. La cláusula FOR UPDATE es la última cláusula de una sentencia SELECT, incluso después

del ORDER BY. NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesión, de lo

contrario se espera.

7) Cláusula WHERE CURRENT OF

{UPDATE | DELETE} ….WHERE CURRENT OF nombre_cursor;

Incluya la cláusula FOR UPDATE en la definición del cursor para bloquear las filas. Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila

actual del cursor.

Ejercicio1:

Programa que imprima el ranquin de ventas por empleados (titulo,puesto,nomemp,codigo,monto.).

DECLARECURSOR CUR_RANKING IS

SELECT E.EMPNO,E.ENAME, SUM(O.TOTAL) TOTALFROM EMP E LEFT JOIN ORD OON E.EMPNO = O.EMPNOGROUP BY E.EMPNO,E.ENAMEORDER BY 3 DESC;

REG_RANKING CUR_RANKING%ROWTYPE; V_POSICION NUMBER(4,2):=1;BEGIN

DBMS_OUTPUT.PUT_LINE('RANKING DE EMPLEADOS POR VENTA');

DBMS_OUTPUT.PUT_LINE(RPAD('*',100,'*'));

OPEN CUR_RANKING;

LOOPFETCH CUR_RANKING INTO REG_RANKING;EXIT WHEN CUR_RANKING%NOTFOUND;

/* PROCESAMIENTO DE LOSREGISTROS RECUPERADOS */DBMS_OUTPUT.PUT_LINE(RPAD(V_POSICION,10) ||

RPAD(REG_RANKING.EMPNO,10) || RPAD(REG_RANKING.ENAME,20) || RPAD(REG_RANKING.TOTAL,10));

END LOOP;

CLOSE CUR_RANKING;END;/

Ejercicio2:

PROGRAMA QUE ACTUALIZE LAS COMISIONES A TODOS LOS EMPLEADOSMENOS DE 5000 3% DE LAS VENTASMAS DE 5000 5% DE LAS VENTAS

DECLARECURSOR CUR_RANKING IS

SELECT E.EMPNO,E.ENAME, SUM(O.TOTAL) TOTALFROM EMP E LEFT JOIN ORD OON E.EMPNO = O.EMPNOGROUP BY E.EMPNO,E.ENAMEORDER BY 3 DESC;

REG_RANKING CUR_RANKING%ROWTYPE;V_COMISION NUMBER(8,2);

BEGINOPEN CUR_RANKING;

LOOPFETCH CUR_RANKING INTO REG_RANKING;

EXIT WHEN CUR_RANKING%NOTFOUND;

if REG_RANKING.TOTAL < 5000 thenV_COMISION :=0.03 * REG_RANKING.TOTAL;

elseV_COMISION :=0.05 * REG_RANKING.TOTAL;

end if;

update empset comm =V_COMISIONwhere empno = REG_RANKING.EMPNO;

COMMIT;END LOOP;

CLOSE CUR_RANKING;END;/

Ejercicio3: Programa que imprima el ranquin de ventas por empleados (titulo,puesto,nomemp,codigo,monto.).

declareCURSOR CUR_RANKING IS

SELECT E.EMPNO,E.ENAME, SUM(O.TOTAL) TOTALFROM EMP E LEFT JOIN ORD OON E.EMPNO = O.EMPNOGROUP BY E.EMPNO,E.ENAMEORDER BY 3 DESC;

REG_RANKING CUR_RANKING%ROWTYPE;V_COMISION NUMBER(8,2);

beginOPEN CUR_RANKING;

FETCH CUR_RANKING INTO REG_RANKING;

FOR REG_RANKING IN CUR_RANKING LOOPif REG_RANKING.TOTAL < 5000 then

V_COMISION :=0.03 * REG_RANKING.TOTAL;

elseV_COMISION :=0.05 * REG_RANKING.TOTAL;

end if;

update empset comm =V_COMISIONwhere empno = REG_RANKING.EMPNO;

COMMIT;END LOOP;

CLOSE CUR_RANKING;end;/

Propuesto

Incrementar la comisión, en función del salario, de los empleados de Bostón y Nueva York según su antigüedad y cargo¸según la siguiente tabla:

Fuente de apoyo: http://www.mundoracle.com/cursores.html