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;