Cursores Implícitos vs Cursores Explicitos en PL/SQL

Cursores Implícitos

Cuando se ejecuta una sentencia DML (son aquellas utilizadas para insertar, borrar, modificar y consultar los datos en una base de datos) un cursor implícito es lanzado.  La principal característica es que no se declara con la palabra reservada CURSOR y que se suele utilizar para trabajar con un registro especifico.

Supongamos que tenemos la siguiente tabla:

Los siguientes son ejemplos de cursores implícitos

--Ejemplo 1:
INSERT INTO empleados (cedula, nombre, apellidos,
                       direccion, username, estado)
     VALUES (701110222,'Perico','De los palotes',
             'En algún lugar Costa Rica','perico','A');

--Ejemplo 2:
UPDATE empleados
   SET estado := 'I'
 WHERE cedula = 701110222;

--Ejemplo 3:
DELETE empleados
 WHERE cedula = 701110222;

--Ejemplo 4:
SELECT cedula, nombre, apellidos, direccion
  INTO l_cedula, l_nombre, l_apellidos, l_direccion
  FROM empleados
 WHERE cedula = 701110222;

Excepciones asociadas a los cursores implícitos.

Excepción	Descripción
NO_DATA_FOUND	Se produce cuando en una sentencia SELECT la consulta no devuelve nada, porque ninguna fila satisface las condiciones. En español "no hay datos".
TOO_MANY_ROWS	Se produce cuando en una sentencia SELECT la consulta devuelve más de un resultado (o sea más de un registro). En español “muchas filas”

Cursores Explícitos

Este tipo de cursores son declarados de manera explicita utilizando la palabra reservada CURSOR.  Se utilizan cuando es necesario trabajar con "n" cantidad de registros.

A continuación un ejemplo:

DECLARE
  --Se declara el cursor explicito
  CURSOR c_empleados(p_estado VARCHAR2) IS
    SELECT * FROM empleados
     WHERE estado = p_estado;

  --Se declara un record del tipo tabla a recuperar
  l_Record empleados%ROWTYPE;
    
BEGIN
 
  -- Se abre el cursor
  OPEN c_empleados('A');
  LOOP
    -- Se recupera una tupla del cursor y se carga en el record
    FETCH c_empleados INTO l_Record;
       
    -- La siguiente sentencia es TRUE si 0 filas fueron
    -- retornadas en la presente iteración
    EXIT WHEN l_Cursor%NOTFOUND;
 
    IF length(l_Record.direccion) = 0 THEN
      UPDATE empleados
         SET estado = 'I'
       WHERE cedula = l_Record.cedula;
    END IF;
    
  END LOOP;
  CLOSE c_empleados;
 
END;

El mismo ejemplo pero abreviado:

DECLARE
  --Se declara el cursor explicito
  CURSOR c_empleados(p_estado VARCHAR2) IS
    SELECT * FROM empleados
     WHERE estado = p_estado;

BEGIN
  --Con la siguiente forma de iteración el cursor
  --se abre y cierra automáticamente.
  FOR registro IN c_empleados('A') LOOP
    IF length(registro.direccion) = 0 THEN
      UPDATE empleados
         SET estado = 'I'
        WHERE cedula = registro.cedula;
    END IF;       
  END LOOP;
END;

Atributos de los cursores explícitos

Atributo	Cerrado	Abierto	Durante las iteraciones	Al finalizar
%NOFOUND	Ora-1001	Null	False	True
%FOUND	Ora-1001	Null	True	False
%ISOPEN	False	True	True	True
%ROWCOUNT	Ora-1001	0	*	**

* Número de registros recuperados hasta el momento
** Número de total de registro

Cursores de Referencia en PL/SQL Oracle

En mi trabajo actual estoy dedicado 100% al desarrollo de sistemas, por este motivo he decidido que ahora voy a estar posteando Tips & Tricks sobre programación y base de datos, este no era el objetivo inicial de este blog, pero igualmente creo estos temas les serán de gran utilidad.

Un poco de teoría

Un REF CURSOR es básicamente un tipo de datos y una variable creada sobre esa base de tipo de datos generalmente se llama una variable de cursor. Una variable cursor puede estar asociada con diferentes consultas en tiempo de ejecución. La principal ventaja de utilizar variables de cursor es su capacidad para pasar de conjuntos de resultados entre los subprogramas (como procedimientos almacenados, funciones, paquetes, etc.)
 
A continuación les propongo un ejercicio meramente académico, para exponer brevemente una de las tantas formas del manejo de los cursores de referencia en PL/SQL.

Base de Datos

Para que el código que les dejo tenga algo de sentido, definamos previamente que tenemos las siguientes tablas de base de datos.

Ahora el código

A continuación encontrarán el código fuente del ejemplo propuesto.  En el mismo he incluido comentarios para explicar las secciones que consideré necesario.

DECLARE

  -- El tipo para el cursor de referencia
  TYPE r_Cursor IS REF CURSOR;
  
  -- El cursor tipo cursor de refencia
  l_Cursor r_Cursor;
  
  --Con la siguiente declaración se crea un record del tipo datos,
  --o sea un record con todas las columnas presentes en la tabla datos.
  l_Record datos%ROWTYPE;
  
  -- Las siguientes variables son para capturar errores
  l_ecode NUMBER;
  l_emesg VARCHAR2(200);
  
  -- La siguiente función es meramente académica, la idea es ilustrar
  -- que la sentencia SQL se puede construir dinámicamente.   
  FUNCTION fn_Generar_Sql(p_Estado IN VARCHAR2) RETURN VARCHAR2
  IS
    l_Query VARCHAR2(1000);
  BEGIN
    
    l_Query := 'SELECT u.rol, e.nombre, e.apellidos ' ||
               '  FROM users u, empleados e ' ||
               ' WHERE u.username = e.username ' ||
               '   AND e.estado = ' p_Estado;
    
    return (l_Query);
  END fn_Generar_Sql;

BEGIN

  -- Se abre el cursor con la sentencia devuelta por la función
  OPEN l_Cursor FOR fn_Generar_Sql('A');
  LOOP
      -- Se recupera una tupla del cursor y se carga el en record
      FETCH l_Cursor INTO l_Record;
      
      -- La siguiente sentencia es TRUE si 0 filas fueron 
      -- retornadas en la presente iteración
      EXIT WHEN l_Cursor%NOTFOUND;

      BEGIN
        -- Se hace un insert en la tabla datos
        INSERT INTO datos (rol, nombre, apellidos)
          VALUES (l_Record.rol, l_Record.nombre, l_Record.apellidos);
      EXCEPTION
        -- Si ocurrió cualquier error se captura está excepción
        WHEN OTHERS THEN
          l_ecode := SQLCODE;
          l_emesg := SQLERRM;
          -- En este ejemplo si ocurre un error, se escribira el
          -- código y descripción en el buffer
          dbms_output.put_line(TO_CHAR(l_ecode) || '-' || l_emesg);
      END;
      
  END LOOP;
  CLOSE l_Cursor;

END;