Utilisation des RESULTSETs¶

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

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. Toutefois, vous pouvez lier des variables dans les commandes SQL avant de renvoyer le jeu de résultats.

En général, il est plus simple d’utiliser RESULTSET lorsque vous souhaitez renvoyer une table contenant le jeu de résultats d’une requête. Cependant, vous pouvez également renvoyer une table à partir d’un bloc Exécution de scripts Snowflake avec un curseur. Pour ce faire, vous pouvez transmettre le curseur à RESULTSET_FROM_CURSOR(cursor) pour obtenir un RESULTSET et passer ce RESULTSET dans 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);
```
Copy
Dans le bloc BEGIN … END, utilisez la syntaxe décrite dans Syntaxe d’affectation de RESULTSET. Par exemple :
```
BEGIN
  ...
  LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
```
Copy

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> := [ ASYNC ] ( <query> ) ;

Copy

Où :

resultset_name
Nom de l” RESULTSET.

Le nom doit être unique dans le scope actuel.

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

ASYNC

Fonctionnalité en avant-première — En accès libre

Disponible pour tous les comptes.

Exécute la requête en tant que tâche enfant asynchrone.

La requête peut être n’importe quelle instruction SQL valide, y compris des instructions SELECT et DML telles que INSERT ou UPDATE.

Lorsque ce mot-clé est omis, la procédure stockée exécute les tâches enfants de manière séquentielle, et chaque tâche enfant attend que la tâche enfant en cours se termine avant de démarrer.

Vous pouvez utiliser ce mot-clé pour exécuter plusieurs tâches enfants simultanément, ce qui peut améliorer l’efficacité et réduire le temps d’exécution global.

Vous pouvez utiliser les instructions AWAIT et CANCEL pour gérer des tâches enfants asynchrones pour un RESULTSET.

query
La requête à attribuer au RESULTSET.

Pour affecter une requête à un RESULTSET :

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

Copy

Pour affecter une requête à un RESULTSET et exécuter la requête en tant que tâche enfant asynchrone :

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

Copy

Pour construire une chaîne SQL de manière dynamique 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;

Copy

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
Retourner un RESULTSET comme une table

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;

Copy

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

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.

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 table. Par exemple, ce qui suit n’est pas valide :

SELECT * FROM my_result_set;

Copy

Exemples d’utilisation d’un RESULTSET¶

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

Configuration des données pour les exemples
Exemple : renvoyer une table à partir d’une procédure stockée
Exemple : construire l’instruction SQL dynamiquement
Exemple : déclarer une variable RESULTSET sans clause DEFAULT
Exemple : utiliser un CURSOR avec un RESULTSET
Exemple : exécution de tâches enfants qui effectuent des requêtes simultanées sur les tables
Exemple : exécution simultanée de tâches enfant qui insèrent des lignes dans les tables
Autres 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 OR REPLACE 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éez 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, Classic Console, ou la méthode execute_stream ou execute_string dans le code Python Connector, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL, Classic Console, et le connecteur Python) :

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

Appelez la procédure stockée :

CALL test_sp();

Copy

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

Vous pouvez aussi 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;

Copy

+---+
| 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 IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;

Copy

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 IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    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');

Copy

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

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

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

Pour exécuter l’exemple, appelez la procédure stockée :

CALL test_sp_02();

Copy

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

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éez 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

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

Appelez la procédure stockée et les résultats ajoutent les valeurs de a dans la table (1 + 2) :

CALL test_sp_03();

Copy

+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

Exemple : exécution de tâches enfants qui effectuent des requêtes simultanées sur les tables¶

Le code suivant montre comment utiliser le mot-clé ASYNC pour exécuter plusieurs tâches enfants qui interrogent des tables de requête simultanément.

Cet exemple utilise les données des tables suivantes :

CREATE OR REPLACE TABLE orders_q1_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q1_2024 VALUES (1, 500.00);
INSERT INTO orders_q1_2024 VALUES (2, 225.00);
INSERT INTO orders_q1_2024 VALUES (3, 725.00);
INSERT INTO orders_q1_2024 VALUES (4, 150.00);
INSERT INTO orders_q1_2024 VALUES (5, 900.00);

CREATE OR REPLACE TABLE orders_q2_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q2_2024 VALUES (1, 100.00);
INSERT INTO orders_q2_2024 VALUES (2, 645.00);
INSERT INTO orders_q2_2024 VALUES (3, 275.00);
INSERT INTO orders_q2_2024 VALUES (4, 800.00);
INSERT INTO orders_q2_2024 VALUES (5, 250.00);

Copy

La procédure stockée suivante effectue les actions suivantes :

Effectue une requête dans les deux tables pour obtenir les valeurs order_amount dans toutes les lignes et renvoie les résultats à différents RESULTSETs (un pour chaque table).
Spécifie que les requêtes s’exécutent en tant que tâches enfant simultanées en utilisant le mot-clé ASYNC.
Exécute l’instruction AWAIT pour chaque RESULTSET afin que la procédure attende la fin des requêtes avant de poursuivre. Les résultats de la requête pour un RESULTSET ne sont pas accessibles tant que AWAIT n’est pas exécuté pour le RESULTSET.
Utilise un curseur pour calculer la somme des lignes order_amount pour chaque table.
Additionne les totaux des tables et renvoie la valeur.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  accumulator1 INTEGER DEFAULT 0;
  accumulator2 INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
  res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
BEGIN
  AWAIT res1;
  LET cur1 CURSOR FOR res1;
  OPEN cur1;
  AWAIT res2;
  LET cur2 CURSOR FOR res2;
  OPEN cur2;
  FOR row_variable IN cur1 DO
      accumulator1 := accumulator1 + row_variable.order_amount;
  END FOR;
  FOR row_variable IN cur2 DO
      accumulator2 := accumulator2 + row_variable.order_amount;
  END FOR;
  RETURN accumulator1 + accumulator2;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator1 INTEGER DEFAULT 0;
    accumulator2 INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
    res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
  BEGIN
    AWAIT res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;
    AWAIT res2;
    LET cur2 CURSOR FOR res2;
    OPEN cur2;
    FOR row_variable IN cur1 DO
        accumulator1 := accumulator1 + row_variable.order_amount;
    END FOR;
    FOR row_variable IN cur2 DO
        accumulator2 := accumulator2 + row_variable.order_amount;
    END FOR;
    RETURN accumulator1 + accumulator2;
  END;
$$;

Copy

Appelez la procédure stockée :

CALL test_sp_async_child_jobs_query();

Copy

+--------------------------------+
| TEST_SP_ASYNC_CHILD_JOBS_QUERY |
|--------------------------------|
|                           4570 |
+--------------------------------+

Exemple : exécution simultanée de tâches enfant qui insèrent des lignes dans les tables¶

Le code suivant montre comment utiliser le mot-clé ASYNC pour exécuter simultanément plusieurs tâches enfants qui insèrent des lignes dans une table.

La procédure stockée suivante effectue les actions suivantes :

Crée la table orders_q3_2024 si elle n’existe pas.
Crée deux RESULTSETs, insert_1 et insert_2, qui contiennent les résultats des insertions dans la table. Les arguments de la procédure stockée spécifient les valeurs qui sont insérées dans la table.
Spécifie que les insertions s’exécutent en tant que tâches enfant simultanées en utilisant le mot-clé ASYNC.
Exécute l’instruction AWAIT pour chaque RESULTSET afin que la procédure attende la fin des insertions avant de poursuivre. Les résultats d’un RESULTSET ne sont pas accessibles tant que AWAIT n’a pas été exécuté pour le RESULTSET.
Crée un nouveau site RESULTSET res qui contient les résultats d’une requête sur la table orders_q3_2024.
Renvoie les résultats de la requête.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
$$
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
$$;

Copy

Appelez la procédure stockée :

CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);

Copy

+----------+--------------+
| ORDER_ID | ORDER_AMOUNT |
|----------+--------------|
|        1 |       325.00 |
|        2 |       241.00 |
+----------+--------------+

Autres exemples d’utilisation d’un RESULTSET¶

Voici d’autres exemples qui utilisent RESULTSET :

Utilisez une boucle basée sur RESULTSET FOR

Cet exemple vous montre comment utiliser une boucle FOR qui itère sur RESULTSET.
Renvoi d’une table pour un curseur

Cet exemple vous montre comment utiliser un curseur pour renvoyer une table de données dans un RESULTSET.
Mettre à jour les données d’une table à l’aide d’une entrée de l’utilisateur

Cet exemple vous montre comment utiliser des variables de liaison basées sur l’entrée de l’utilisateur pour mettre à jour les données d’une table. Il utilise une boucle FOR avec une logique conditionnelle pour parcourir les lignes d’un RESULTSET.
Filtrer et collecter les données

Cet exemple vous montre comment utiliser un RESULTSET pour collecter des données et les insérer dans une table afin de suivre les tendances historiques.