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
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.