Cursores PL/SQL notación FETCH .. BULK COLLECT INTO .. LIMIT

Esta notación nos permite recuperar las filas asociadas a la sentencia SELECT que representa el cursor con la ejecución de varias sentencias FETCH en grupos de filas de un número determinado por la cláusula LIMIT. Los grupos de filas recuperados se asignan a un array o vector.

Resumiremos linea a línea el código fuente del procedimiento del ejemplo llamado FETCH_BULK_COLLECT_INTO_LIMIT

Línea 2: Inicio del procedimiento.
Líneas 4 a 7: Declaramos el cursor de nombre CursorVistas y lo asociamos a la sentencia SELECT que recupera las filas.
Línea 9: Declaramos un tipo de dato de array o vector que almacena filas de la tabla ALL_VIEWS y lo llamamos TipoArrayRegistros. Es decir, cada elemento del array permite almacenar todos los valores de una fila de la tabla ALL_VIEWS. Cada elemento del array contiene tantos campos como columnas tiene la tabla, con los mismos nombres y tipos de dato.
Línea 10: Declaramos la variable ArrayRegistros del tipo de datos TipoArrayRegistros.
Línea 12: Declaramos la variable indice de tipo de dato entero. La utilizaremos para hacer referencia a los valores que contiene el array o vector.
Línea 14: Abrimos el cursor. Este paso selecciona las filas de la tabla que evalúan verdadera la condición que la sentencia SELECT declara en su cláusula WHERE. Si cambian los valores de las filas antes de ser recuperadas, estos cambios no se verán reflejados. Tomamos la foto de los datos a recuperar en este punto en el tiempo. No se consideran modificaciones posteriores.
Línea 15: Iniciamos el ciclo o bucle repetitivo en el que recuperamos los grupos de filas (la línea 26 fuerza la salida del bucle o ciclo repetitivo si no hay mas filas para recuperar del cursor).
Línea 16: Recuperamos las filas que cumplen la condición de la sentencia SELECT con la instrucción FETCH. El BULK COLLECT INTO permite asignar el grupo de filas recuperadas a un array o vector. Cada elemento del array contiene una fila recuperada. Los elementos están formados por una estructura de campos con nombres y tipos de datos equivalentes a las columnas seleccionadas. La cantidad máxima del grupo de filas recuperadas por la ejecución de cada FETCH está determinada por la cláusula LIMIT. En este ejemplo recuperamos las filas en grupos de 5 filas.
Línea 17 a 25: Recorremos el array o vector ArrayRegistros que contiene las filas recuperadas por la sentencia FETCH y mostramos el contenido en pantalla.
Línea 17: Asignamos a la variable indice la posición del primer elemento almacenado en el array o vector. Si no contiene elementos se asigna el valor NULL.
Línea 18 a 25: Mientras la variable indice sea distinta de NULL iteramos dentro del ciclo definido por el LOOP .. END LOOP. Por cada ciclo mostramos en pantalla con la instrucción DBMS_OUTPUT.PUT_LINE el contenido de los campos OWNER y VIEW_NAME en la posición indice del array.
Luego asignamos a la variable indice la siguiente posición del array que contiene una fila. Si no hay mas filas en el array se asigna NULL. Al evaluarse la condición de WHILE (indice IS NOT NULL) se corta la iteración del ciclo que recorre el vector o array.
Línea 28: Cerramos el cursor. Liberamos los recursos en memoria asociados a la sentencia SELECT.
Línea 28: Fin del procedimiento FETCH_BULK_COLLECT_INTO_LIMIT.

SQL> CONNECT FERNANDEZ/FERNANDEZ
Conectado.

SQL> create or replace
  2  PROCEDURE FETCH_BULK_COLLECT_INTO_LIMIT
  3  IS
  4     CURSOR CursorVistas IS SELECT *
  5                              FROM ALL_VIEWS
  6                             WHERE OWNER = 'SYS'
  7                               AND VIEW_NAME LIKE 'ALL_TAB%';
  8
  9     TYPE TipoArrayRegistros IS TABLE OF CursorVistas%ROWTYPE;
 10     ArrayRegistros TipoArrayRegistros;
 11
 12     indice  PLS_INTEGER;
 13  BEGIN
 14      OPEN CursorVistas;
 15      LOOP
 16          FETCH CursorVistas BULK COLLECT INTO ArrayRegistros LIMIT 5;
 17          indice := ArrayRegistros.FIRST;
 18          WHILE (indice IS NOT NULL)
 19          LOOP
 20              DBMS_OUTPUT.PUT_LINE('ESQUEMA: '                  ||
 21                                   ArrayRegistros(indice).OWNER ||
 22                                   ' VISTA  : '                 ||
 23                                   ArrayRegistros(indice).VIEW_NAME);
 24              indice := ArrayRegistros.NEXT(indice);
 25          END LOOP;
 26          EXIT WHEN CursorVistas%NOTFOUND;
 27      END LOOP;
 28      CLOSE CursorVistas;
 29
 30  END FETCH_BULK_COLLECT_INTO_LIMIT;
 31  /
 
Procedimiento creado.
 
SQL> DESCRIBE FETCH_BULK_COLLECT_INTO_LIMIT;
PROCEDURE FETCH_BULK_COLLECT_INTO_LIMIT
 
SQL> SET SERVEROUTPUT ON
 
SQL> EXEC FETCH_BULK_COLLECT_INTO_LIMIT;
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>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 11 Tuning PL/SQL Applications for Performance
Título: Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
Subtítulo: Retrieving Query Results into Collections with the BULK COLLECT clause
Párrafo: Limiting the Rows for a Bulk FETCH Operation with the LIMIT clause