Utilisation des RESULTSETs

Cette rubrique explique comment utiliser des RESULTSETs dans Exécution de scripts Snowflake.

Dans ce chapitre :

Introduction

Dans Exécution de scripts Snowflake, un RESULTSET est un type de données SQL qui pointe vers le jeu de résultats d’une requête.

Étant donné qu’un RESULTSET n’est qu’un pointeur vers les résultats, vous devez effectuer l’une des opérations suivantes pour accéder aux résultats par le biais du RESULTSET :

  • Utilisez la syntaxe TABLE() pour récupérer les résultats sous forme de tableau.

  • Itérez sur le RESULTSET avec un curseur.

Vous trouverez ci-dessous des exemples de ces deux types.

Comprendre les différences entre un curseur et un RESULTSET

Un RESULTSET et un curseur permettent tous deux d’accéder au jeu de résultats d’une requête. Toutefois, ces objets présentent les différences suivantes :

  • Le moment où la requête est exécutée.

    • Pour un curseur, la requête est exécutée lorsque vous exécutez la commande OPEN sur le curseur.

    • Pour un RESULTSET, la requête est exécutée lorsque vous l’assignez au RESULTSET (soit dans la section DECLARE, soit dans le bloc BEGIN … END).

  • Prise en charge de la liaison dans la commande OPEN.

    • Lorsque vous déclarez un curseur, vous pouvez spécifier des paramètres de liaison (caractères ?). Plus tard, lorsque vous exécuterez la commande OPEN, vous pourrez lier des variables à ces paramètres dans la clause USING.

    • RESULTSET ne prend pas en charge la commande OPEN.

Notez que si vous avez un curseur et que vous devez renvoyer une table à partir du bloc Exécution de scripts Snowflake, vous pouvez passer le curseur à RESULTSET_FROM_CURSOR(cursor) pour renvoyer un RESULTSET et passer ce RESULTSET à TABLE(...). Voir Retourner une table pour un curseur.

Déclarer un RESULTSET

Vous pouvez déclarer un RESULTSET dans la section DECLARE d’un bloc ou dans la section BEGIN … END du bloc.

  • Dans la section DECLARE, utilisez la syntaxe décrite dans Syntaxe de la déclaration RESULTSET. Par exemple :

    DECLARE
      ...
      res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
    
  • Dans le bloc BEGIN … END, utilisez la syntaxe décrite dans Syntaxe d’affectation RESULTSET. Par exemple :

    BEGIN
      ...
      LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
    

Attribution d’une requête à un RESULTSET déclaré

Pour affecter le résultat d’une requête à un RESULTSET qui a déjà été déclaré, utilisez la syntaxe suivante :

<resultset_name> := ( <query> ) ;

Où :

resultset_name

Le nom à donner au RESULTSET.

Le nom doit être unique dans le scope actuel.

Le nom doit suivre les règles de dénomination pour Identificateurs d’objet.

query

La requête à attribuer au RESULTSET.

Par exemple :

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...

Si vous devez construire dynamiquement une chaîne de SQL pour la requête, définissez query sur (EXECUTE IMMEDIATE string_of_sql). Par exemple :

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Remarque : bien que vous puissiez définir query sur une instruction EXECUTE IMMEDIATE pour un RESULTSET, vous ne pouvez pas le faire pour un curseur.

Utiliser un RESULTSET

La requête pour un RESULTSET est exécutée lorsque l’objet est associé à cette requête. Par exemple :

  • Lorsque vous déclarez un RESULTSET et définissez la clause DEFAULT sur une requête, celle-ci est exécutée à ce moment précis.

  • Lorsque vous utilisez l’opérateur := pour affecter une requête à un RESULTSET, la requête est exécutée à ce moment précis.

Note

Étant donné qu’un RESULTSET pointe vers le jeu de résultats d’une requête (et ne contient pas le jeu de résultats d’une requête), un RESULTSET n’est valide que tant que les résultats de la requête sont mis en cache (généralement 24 heures). Pour plus de détails sur la mise en cache des résultats des requêtes, voir Utilisation de résultats de requête persistants.

Une fois la requête exécutée, vous pouvez accéder aux résultats en utilisant un curseur. Vous pouvez également renvoyer les résultats sous forme de table à partir d’une procédure stockée.

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.

Note

Si les résultats comprennent des valeurs GEOGRAPHY, vous devez convertir ces valeurs en type GEOGRAPHY avant de les transmettre à toute fonction qui attend des valeurs d’entrée GEOGRAPHY. Voir Utiliser un curseur pour récupérer une valeur GEOGRAPHY.

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

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.

Note

Actuellement, la syntaxe TABLE(resultset_name) n’est prise en charge que dans l’instruction RETURN.

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

Limites du type de données RESULTSET

Bien que RESULTSET soit un type de données, Snowflake ne prend pas encore en charge :

  • Déclarer une colonne de type RESULTSET.

  • Déclarer un paramètre de type RESULTSET.

  • Déclarer le type de retour d’une procédure stockée comme un RESULTSET.

Snowflake prend en charge RESULTSET uniquement dans Exécution de scripts Snowflake.

En outre, vous ne pouvez pas utiliser un RESULTSET directement comme une table. Par exemple, ce qui suit n’est pas valide :

select * from my_result_set;

Exemples d’utilisation d’un RESULTSET

Les sections suivantes fournissent des exemples d’utilisation d’un RESULTSET :

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');

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;

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

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;
$$;

Appeler la procédure stockée :

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

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

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;

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

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;
$$
;

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

Exemple : déclarer une variable RESULSET 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;

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

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;
$$;

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;

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

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;
$$;

Appeler la procédure stockée :

CALL test_sp_03();
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+
Revenir au début