Como retornar resultados implícitamente de un SELECT usando procedimientos almacenados

Oracle desde la versión 11g permite retornar un conjunto resultado de una sentencia SELECT de modo implícito (sin usar una variable de tipo sys_refcursor como parámetro de salida) invocando a la función RETURN_RESULT del paquete DBMS_SQL.

SYS_REFCURSOR es un tipo de datos que representa un cursor o área de memoria donde se almacena temporalmente un conjunto resultado permitiendo recuperar una a una cada fila.

 

A continuación se presenta un ejemplo que recupera filas de la tabla empleados:

create or replace procedure select_employees as

  rf_cursor_employees SYS_REFCURSOR;
begin
 
  open rf_cursor_employees for

    SELECT employee_id,first_name,last_name,salary,email,phone_number
      FROM HR.employees
    ORDER BY last_name,first_name;
   
    dbms_sql.return_result(rf_cursor_employees);
   
end select_employees;

Compilar  con Oracle SQL Developer o SQL*Plus:

Para su ejecución, se invoca al procedimiento SELECT_EMPLOYEES:

exec select_employees; 

Se puede recuperar más de un conjunto resultado al igual que se lo hace con la forma tradicional empleando variables de tipo  sys_refcursor en lugar de parámetros como se ilustra en el siguiente caso :

create or replace procedure select_multiples_resultados as
 rf_cursor_departments SYS_REFCURSOR;
 rf_cursor_employees SYS_REFCURSOR;
begin
  open rf_cursor_departments for
     SELECT
         department_id,department_name
     FROM HR.departments
     ORDER BY department_name;
    
    dbms_sql.return_result(rf_cursor_departments);

  open rf_cursor_employees for
    SELECT employee_id,first_name,last_name,salary,email,phone_number
    FROM HR.employees
    ORDER BY last_name,first_name;
   
    dbms_sql.return_result(rf_cursor_employees);
   
end select_multiples_resultados;

 

Compilar con SQL Developer o SQL*PLus.

Ejecutar el procedimiento:

exec select_multiples_resultados;

Conclusión:

Por medio de la función RETURN_RESULT se puede obtener uno o varios conjuntos resultado sin declarar parámetros de tipo SYS_REFCURSOR en los  procedimientos, sino, como variables locales del mismo facilitando el trabajo de codificación.

 

Nota:

Les comparto el enlace para saber como llamar estos procedimientos que retornan múltiples recordsets desde JDBC.