Oracle Cursor PL/SQL: implícito, explícito, bucle For con ejemplo

¿Qué es CURSOR en PL/SQL?

Un cursor es un puntero al área de contexto. Oracle crea un área de contexto para procesar un SQL declaración, y esta área contiene toda la información sobre la declaración.

PL / SQL Permite al programador controlar el área de contexto mediante el cursor. Un cursor almacena las filas devueltas por la instrucción SQL, y el conjunto de filas que contiene se denomina conjunto activo. Estos cursores también pueden nombrarse para poder referenciarlos desde otras partes del código.

El cursor es de dos tipos:

• Cursores implícitos
• Cursor explícito

Cursores implícitos

Siempre que alguno Operación DML Cuando se produce una operación en la base de datos, se crea un cursor implícito que almacena las filas afectadas. Estos cursores no pueden tener nombre y, por lo tanto, no se pueden controlar ni referenciar desde otras partes del código. Solo podemos referirnos al cursor más reciente mediante sus atributos.

Cursor explícito

Los programadores pueden crear un área de contexto con nombre para ejecutar sus operaciones DML y obtener más control sobre ella. El cursor explícito debe definirse en la sección de declaración del Bloque PL / SQLy se crea para la instrucción SELECT que debe usarse en el código.

A continuación se detallan los pasos necesarios para trabajar con cursores explícitos:

• Declarando el cursor: Declarar el cursor simplemente significa crear un área de contexto con nombre para la instrucción SELECT que se define en la declaración. El nombre de esta área de contexto es el mismo que el del cursor.
• Abriendo el cursor: Al abrir el cursor, PL/SQL le indica que asigne memoria para dicho cursor. Esto prepara el cursor para recuperar los registros.
• Obteniendo datos del cursor: En este proceso, se ejecuta la instrucción SELECT y las filas recuperadas se almacenan en la memoria asignada. Estas ahora se denominan conjuntos activos. La recuperación de datos del cursor es una actividad a nivel de registro, lo que significa que podemos acceder a los datos registro por registro. Cada instrucción fetch recupera un conjunto activo y contiene la información de ese registro en particular. Esta instrucción es la misma que una instrucción SELECT que recupera el registro y lo asigna a la variable en la cláusula INTO, pero no generará ningún error. excepciones.
• Cerrando el cursor: Una vez recuperados todos los registros, debemos cerrar el cursor para liberar la memoria asignada a esta área de contexto.

Sintaxis

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
<cursor_variable declaration>;
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

En la sintaxis anterior, la declaración contiene la definición del cursor y la variable de cursor a la que se asignarán los datos obtenidos. El cursor se crea para la instrucción SELECT especificada en su declaración. En la ejecución, el cursor declarado se abre, se obtienen los datos y se cierra.

Atributos del cursor

Tanto el cursor implícito como el explícito poseen ciertos atributos a los que se puede acceder. Estos atributos proporcionan información adicional sobre las operaciones del cursor. A continuación, se describen los diferentes atributos del cursor y su uso.

Ejemplo de cursor explícito: En este ejemplo, veremos cómo declarar, abrir, obtener y cerrar un cursor explícito. Proyectaremos todos los nombres de los empleados de la tabla `emp` mediante un cursor. También utilizaremos un atributo del cursor para configurar el bucle y obtener todos los registros del mismo.

La captura de pantalla a continuación muestra este ejemplo explícito de cursor y su salida en Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;
BEGIN
OPEN guru99_det;
LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line('Employee Fetched:'||lv_emp_name);
END LOOP;
Dbms_output.put_line('Total rows fetched is'||guru99_det%ROWCOUNT);
CLOSE guru99_det;
END;
/

Resultado

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Total rows fetched is 3

Code Explicación

• Code línea 2: Declarando el cursor guru99_det para la instrucción 'SELECT emp_name FROM emp'.
• Code línea 3: Declarar la variable lv_emp_name con el %tipo anclado a emp.emp_name.
• Code línea 5: Abriendo el cursor guru99_det.
• Code línea 6: Configurar la instrucción de bucle básica para obtener todos los registros de la tabla emp.
• Code línea 7: Obtiene los datos de guru99_det y asigna el valor a lv_emp_name.
• Code línea 8: Se utiliza el atributo %NOTFOUND del cursor para comprobar si se han recuperado todos los registros. Si se han recuperado, devuelve TRUE y el control sale del bucle; de ​​lo contrario, el control continúa recuperando los datos del cursor y los imprime.
• Code línea 10: Condición de SALIDA para la declaración de bucle.
• Code línea 12: Imprima el nombre del empleado recuperado.
• Code línea 14: Utilice el atributo %ROWCOUNT del cursor para encontrar el número total de registros recuperados por el cursor.
• Code línea 15: Tras salir del bucle, el cursor se cierra y se libera la memoria asignada.

FOR instrucción del cursor de bucle

Un cursor En bucle Se puede utilizar para trabajar con cursores. En el bucle FOR, podemos especificar el nombre del cursor en lugar de un límite de rango, de modo que el bucle recorra desde el primer registro hasta el último. La variable del cursor, su apertura, su obtención y su cierre se gestionan implícitamente mediante el bucle FOR.

Sintaxis

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
FOR I IN <cursor_name>
LOOP
.
.
END LOOP;
END;

En la sintaxis anterior, la declaración contiene la definición del cursor. Este cursor se crea para la instrucción SELECT especificada en su declaración. En la ejecución, el cursor declarado se configura dentro del bucle FOR, y la variable de bucle 'I' actúa como variable de cursor en este caso.

Oracle Ejemplo de bucle con cursor: En este ejemplo, proyectaremos todos los nombres de los empleados de la tabla emp utilizando un bucle FOR con cursor.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line('Employee Fetched:'||lv_emp_name.emp_name);
END LOOP;
END;
/

Resultado

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY

Code Explicación

• Code línea 2: Declarando el cursor guru99_det para la instrucción 'SELECT emp_name FROM emp'.
• Code línea 4: Construyendo el bucle FOR para el cursor con la variable de bucle lv_emp_name.
• Code línea 6: Imprimir el nombre del empleado en cada iteración del bucle.
• Code línea 7: Salir del bucle (FIN DEL BUCLE).

Nota: En un bucle FOR con cursor, no se pueden utilizar los atributos del cursor, ya que la apertura, la obtención y el cierre del cursor se realizan implícitamente mediante el bucle FOR.

Preguntas Frecuentes