Rückgabe einer Ergebnismenge aus einer gespeicherten Prozedur

In diesem Thema wird beschrieben, wie gespeicherte Prozeduren Daten zurückgeben.

Sie können eine Ergebnismenge zurückgeben unter Verwendung eines Cursors oder einer temporären Tabelle.

Rückgabe eines Cursors

Um einen Cursor zurückzugeben, erstellen Sie eine Prozedur mit einem INOUT Argument, das mit einem refcursor Datentyp definiert ist. Wenn Sie die Prozedur aufrufen, benennen Sie den Cursor. Dann können Sie die Ergebnisse anhand des Namens vom Cursor abrufen.

Im folgenden Beispiel wird eine Prozedur erstellt, die get_result_set mit einem INOUT Argument benannt ist, das den refcursor Datentyp rs_out verwendet. Die Prozedur öffnet den Cursor mithilfe einer SELECT Anweisung.

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;

Der folgende CALL Befehl öffnet den Cursor mit dem Namenmycursor. Verwenden Sie Cursor nur während Transaktionen.

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

Wenn der Cursor geöffnet ist, können Sie aus dem Cursor abrufen, wie das folgende Beispiel zeigt.

FETCH ALL FROM mycursor;

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

Schließlich wird die Transaktion entweder bestätigt oder zurückgesetzt.

COMMIT;   

Ein von einer gespeicherten Prozedur zurückgegebener Cursor unterliegt denselben Einschränkungen und Leistungsaspekten wie unter beschrieben DECLARECURSOR. Weitere Informationen finden Sie unter Einschränkungen für Cursors.

Das folgende Beispiel zeigt den Aufruf der get_result_set gespeicherten Prozedur unter Verwendung eines refcursor Datentyps vonJDBC. Der wortgetreue 'mycursor' (der Cursorname) wird an den prepareStatement übergeben. Anschließend werden die Ergebnisse von dem aufgerufen 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);
    }

Verwenden einer temporären Tabelle

Um Ergebnisse zu erhalten, können Sie ein Kürzel einer temporären Tabelle zurückgeben, die Ergebniszeilen enthält. Der Client kann der gespeicherten Prozedur als Parameter einen Namen zuweisen. Innerhalb der gespeicherten Prozedur SQL kann Dynamic verwendet werden, um mit der temporären Tabelle zu arbeiten. Es folgt ein Beispiel.

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)