martes, 9 de octubre de 2007

Cursores PL/SQL notación CURSOR...FOR...LOOP

Esta notación de cursores PL/SQL agrega a la notación mas simple FOR...LOOP la posibilidad de definir la sentencia SELECT asociada al cursor en forma independiente del recupero de las filas. Podemos reutilizar el cursor definido ganando flexibilidad y facilidad de mantenimiento. Nuevamente la variable Registro es automática creándose y destruyéndose en forma implicita. Sólo es referenciable dentro del contexto del FOR...LOOP...END LOOP. Los miembros o campos de la variable Registro están definidos por las columnas detalladas en la lista de la sentencia SELECT asociada al cursor (OWNER y VIEW_NAME).

SQL> create or replace
2 PROCEDURE CURSOR_FOR_LOOP
3 IS
4 CURSOR CursorVistas
5 IS (SELECT OWNER, VIEW_NAME
6 FROM ALL_VIEWS
7 WHERE OWNER = 'SYS'
8 AND VIEW_NAME LIKE 'ALL_TAB%');
9 BEGIN
10 FOR Registro IN CursorVistas
11 LOOP
12 DBMS_OUTPUT.PUT_LINE('ESQUEMA: ' ||
13 Registro.OWNER ||
14 ' VISTA: ' ||
15 Registro.VIEW_NAME);
16 END LOOP;
17 END CURSOR_FOR_LOOP;
18 /

Procedimiento creado.
Verificamos con el comando DESCRIBE si el procedimiento fue creado, luego lo ejecutamos.
SQL> DESCRIBE CURSOR_FOR_LOOP
PROCEDURE CURSOR_FOR_LOOP

SQL> EXECUTE CURSOR_FOR_LOOP;

Procedimiento PL/SQL terminado correctamente.
Vemos que el procedimiento no muestra los mensajes en pantalla, habilitamos la salida de mensajes y volvemos a ejecutar el procedimiento.
SQL> SET SERVEROUTPUT ON

SQL> EXECUTE CURSOR_FOR_LOOP;
ESQUEMA: SYS VISTA: ALL_TABLES
ESQUEMA: SYS VISTA: ALL_TAB_COLS
ESQUEMA: SYS VISTA: ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA: ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA: ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA: ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA: ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA: ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA: ALL_TAB_SUBPARTITIONS

Procedimiento PL/SQL terminado correctamente.

SQL>
Esta notación de cursores nos permite definir el cursor una vez y ejecutarlo múltiples veces. Se facilita al motor de la base de datos la tarea de reconocer la ejecución frecuente de la misma sentencia SELECT, simplificando la reutilización y ahorrando recursos.

También podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Querying Data with PL/SQL
Subtítulo: Querying Data with PL/SQL: Explicit Cursor FOR Loops

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

3 comentarios:

Yamila dijo...

Al ejecutar el procedimiento no veo las impresiones del DBMS_OUTPUT en mi pantalla :(
Qué puede ser?
Gracias!

databasetutorial dijo...

Gracias Yamila! Modificamos la entrada para que no sucedan estos errores. Antes de ejecutar un DBMS_OUTPUT mostrando mensajes en pantalla debemos habilitar los mensajes en el SQL*Plus con SET SERVEROUTPUT ON.

Anónimo dijo...

Muy claro y preciso.