Utilisation des RESULTSETs¶

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

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

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

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

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

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;

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.

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

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

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

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;

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

• Autres exemples d’utilisation d’un RESULTSET

Pour des exemples utilisant le mot-clé ASYNC pour exécuter des requêtes spécifiées pour RESULTSETs en tant que tâches enfants asynchrones, voir Exemples d’utilisation de tâches enfants asynchrones.

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

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;

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

CALL test_sp();

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

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  ORDER BY 1;

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

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;

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

CALL test_sp_dynamic('t001');

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

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;

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

CALL test_sp_02();

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

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;

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

CALL test_sp_03();

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