Utilisation des RESULTSETs¶

Utilisation d’un curseur pour accéder à des données d’un RESULTSET¶

Pour utiliser un curseur afin d’accéder aux données d’un RESULTSET, déclarez le curseur sur l’objet. Par exemple :

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;

Notez que lorsque vous déclarez un curseur sur un RESULTSET, le curseur a accès aux données déjà présentes dans le RESULTSET. L’exécution de la commande OPEN sur le curseur n’exécute pas à nouveau la requête pour le RESULTSET.

Vous pouvez alors ouvrir le curseur et utiliser le curseur pour extraire les données.

Retourner un RESULTSET comme une table¶

Si vous voulez retourner les résultats vers lesquels pointe le RESULTSET, passez le RESULTSET à TABLE(). Par exemple :

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...

Copy

C’est similaire à la façon dont TABLE() est utilisé avec les fonctions de table (comme RESULT_SCAN)

Comme le montre l’exemple, si vous écrivez une procédure stockée qui renvoie une table, vous devez déclarer que la procédure stockée renvoie une table.

Notez que même si vous avez utilisé un curseur pour extraire des lignes de RESULTSET, la table renvoyée par TABLE(resultset_name) contient toujours toutes les lignes (et pas seulement celles qui commencent à partir du pointeur de ligne interne du curseur).

Configuration des données pour les exemples¶

Les exemples ci-dessous utilisent la table et les données présentées ci-dessous :

CREATE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
    (1, 'row1'),
    (2, 'row2');

Copy

Exemple : renvoyer une table à partir d’une procédure stockée¶

Le code suivant montre comment déclarer un RESULTSET et renvoyer les résultats vers lesquels pointe le RESULTSET. La clause RETURNS de la commande CREATE PROCEDURE déclare que la procédure stockée renvoie une table, qui contient une colonne de type INTEGER.

L’instruction RETURN à l’intérieur du bloc utilise la syntaxe TABLE() pour renvoyer les résultats sous forme de table.

Créer la procédure stockée :

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET default (select a from t001 order by a);
  BEGIN
    RETURN TABLE(res);
  END;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
    DECLARE
        res RESULTSET default (select a from t001 order by a);
    BEGIN
        RETURN TABLE(res);
    END;
$$;

Copy

Appeler la procédure stockée :

CALL test_sp();
+---+
| A |
|---|
| 1 |
| 2 |
+---+

Copy

Notez que vous pouvez utiliser la fonction RESULT_SCAN pour traiter les résultats de l’appel de la procédure stockée :

SELECT *
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY 1;
+---+
| A |
|---|
| 1 |
| 2 |
+---+

Copy

Exemple : construire l’instruction SQL dynamiquement¶

Vous pouvez construire le SQL de manière dynamique. L’exemple suivant exécute la même requête que la procédure stockée ci-dessus, mais utilise une instruction SQL construite dynamiquement :

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;
$$
;

Copy

Pour exécuter l’exemple, appelez la procédure stockée et indiquez le nom de la table :

call test_sp_dynamic('t001');

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Copy

Exemple : déclarer une variable RESULTSET sans clause DEFAULT¶

Le code suivant montre comment déclarer un RESULTSET sans clause DEFAULT (c’est-à-dire sans associer une requête à la RESULTSET), puis associer la RESULTSET à une requête ultérieurement.

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (select a from t001 order by a);
    RETURN TABLE(res);
  END;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
    DECLARE
        res RESULTSET;
    BEGIN
        res := (select a from t001 order by a);
        RETURN TABLE(res);
    END;
$$;

Copy

Exemple : utiliser un CURSOR avec un RESULTSET¶

Le code suivant montre comment utiliser un curseur pour itérer sur les lignes d’un RESULTSET :

Créer la procédure stockée :

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (select a from t001 order by a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
      accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator::VARCHAR;
  END;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
    DECLARE
        accumulator INTEGER DEFAULT 0;
        res1 RESULTSET DEFAULT (select a from t001 order by a);
        cur1 CURSOR FOR res1;
    BEGIN
        FOR row_variable IN cur1 DO
                accumulator := accumulator + row_variable.a;
        END FOR;
        RETURN accumulator;
    END;
$$;

Copy

Appeler la procédure stockée :

CALL test_sp_03();
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

Copy