Devolución de un conjunto de resultados desde un procedimiento almacenado

En este tema se describe cómo devuelven datos los procedimientos almacenados.

Puede devolver conjuntos de resultados mediante un cursor o una tabla temporal.

Devolución de un cursor

Para devolver un cursor, cree un procedimiento con un argumento INOUT definido con un tipos de datos refcursor. Cuando llame al procedimiento, asigne un nombre al cursor. Después, puede obtener los resultados del cursor por nombre.

El siguiente ejemplo crea un procedimiento llamado get_result_set con un argumento INOUT llamado rs_out mediante el tipo de datos refcursor. El procedimiento abre el cursos mediante una instrucción SELECT.

CREATE OR REPLACE PROCEDURE get_result_set (param IN integer, rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM fact_tbl where id >= param;
END;
$$ LANGUAGE plpgsql;

El siguiente comando CALL abre el cursor con el nombre mycursor. Use cursores solo con transacciones.

BEGIN;
CALL get_result_set(1, 'mycursor');   

Después de abrir el cursor, puede realizar recuperaciones desde el cursor, como se muestra en el siguiente ejemplo.

FETCH ALL FROM mycursor;

    id | secondary_id | name
-------+--------------+---------
     1 |            1 | Joe
     1 |            2 | Ed
     2 |            1 | Mary
     1 |            3 | Mike
(4 rows)   

Al final, la transacción se confirma o revierte.

COMMIT;   

Un cursor devuelto por un procedimiento almacenado está sujeto a las mismas limitaciones y consideraciones de rendimiento que se describen en DECLARE CURSOR. Para obtener más información, consulte Restricciones del cursor.

El siguiente ejemplo muestra la llamada del procedimiento almacenado get_result_set mediante un tipo de datos refcursor de JDBC. El nombre 'mycursor' literal (el nombre del cursor) se pasa a prepareStatement. A continuación, los resultados se recuperan de ResultSet.

static void refcursor_example(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    PreparedStatement proc = conn.prepareStatement("CALL get_result_set(1, 'mycursor')");
    proc.execute();
    ResultSet rs = statement.executeQuery("fetch all from mycursor");
    while (rs.next()) {
      int n = rs.getInt(1);
      System.out.println("n " + n);
    }

Uso de una tabla temporal

Para devolver resultados, puede devolver un controlador a una tabla temporal que contiene filas de resultados. El cliente puede proporcionar un nombre como un parámetro para el procedimiento almacenado. Dentro del procedimiento almacenado, SQL dinámico puede usarse para operar en la tabla temporal. A continuación se muestra un ejemplo.

CREATE PROCEDURE get_result_set(param IN integer, tmp_name INOUT varchar(256)) as $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_name;
  EXECUTE 'create temp table ' || tmp_name || ' as select * from fact_tbl where id <= ' || param;
END;
$$ LANGUAGE plpgsql;

CALL get_result_set(2, 'myresult');
 tmp_name
-----------
 myresult
(1 row)

SELECT * from myresult;
 id | secondary_id | name
----+--------------+------
  1 |            1 | Joe
  2 |            1 | Mary
  1 |            2 | Ed
  1 |            3 | Mike
(4 rows)