Retour d’un ensemble de résultats

Vous pouvez retourner un ensemble de résultats à l’aide d’un curseur ou d’une table temporaire.

Retour d’un curseur

Pour retourner un curseur, créez une procédure avec un argument INOUT défini avec un type de données refcursor. Lorsque vous appelez la procédure, donnez un nom au curseur. Vous pouvez ensuite extraire les résultats du curseur par leur nom.

L’exemple suivant crée une procédure nommée get_result_set avec un argument INOUT nommé rs_out à l’aide du type de données refcursor. La procédure ouvre le curseur à l’aide d’une instruction 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;

La commande CALL suivante ouvre le curseur avec le nom mycursor. N’utlisez les curseurs qu’au sein des transactions.

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

Une fois que le curseur est ouvert, vous pouvez procéder à l’extraction depuis ce curseur, comme l’illustre l’exemple suivant.

FETCH ALL FROM mycursor;

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

À la fin, la transaction est validée ou annulée.

COMMIT;   

Un curseur retourné par une procédure stockée est soumise aux mêmes contraintes et considérations de performance, telles que décrites dans DECLARE CURSOR; Pour plus d'informations, consultez Contraintes de curseur.

L’exemple suivant montre l’appel de la procédure stockée get_result_set à l’aide d’un type de données refcursor depuis JDBC. Le littéral 'mycursor' (nom du curseur) est transmis à prepareStatement. Puis, les résultats sont extraits 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);
    }

Utilisation d’une table temporaire

Pour retourner les résultats, vous pouvez retourner un descripteur vers une table temporaire contenant les lignes de résultats. Le client peut fournir un nom comma paramètre à la procédure stockée. À l’intérieur de la procédure stockée, Dynamic SQL peut être utilisé pour œuvrer sur la table temporaire. Vous en trouverez un exemple ci-dessous.

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)