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
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;
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;
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.
Comentarios
Muchas gracias por el post. Estoy probando a ejecutarlo desde java con un CallableStatement o desde una query con jdbc pero no lo consigo, ¿podrías dar un ejemplo?
Muchas gracias!
Alicia.
https://blog.jooq.org/how-to-fetch-oracle-12c-implicit-cursors-with-jdbc-and-jooq/
Cordiales.
https://docs.oracle.com/database/121/JJDBC/getsta.htm#JJDBC29004
============Código PL/SQL============
create procedure foo as
c1 sys_refcursor;
c2 sys_refcursor;
begin
open c1 for select * from hr.employees;
dbms_sql.return_result(c1); --return to client
-- open 1 more cursor
open c2 for select * from hr.departments;
dbms_sql.return_result (c2); --return to client
end;
============Código Java============
String sql = "begin foo; end;";
...
Connection conn = DriverManager.getConnection(jdbcURL, user, password);
try {
Statement stmt = conn.createStatement ();
stmt.executeQuery (sql);
while (stmt.getMoreResults())
{
ResultSet rs = stmt.getResultSet();
System.out.println("ResultSet");
while (rs.next())
{
/* get results */
}
}
}
Cordiales.