Construction d’instructions SQL au moment de l’exécution¶

Snowflake prend en charge plusieurs techniques différentes pour construire des chaînes d’instructions SQL de manière dynamique au moment de l’exécution. En utilisant ces techniques, vous pouvez spécifier des chaînes SQL plus générales et plus flexibles pour les cas d’utilisation où le texte intégral des instructions SQL est inconnu jusqu’au moment de l’exécution.

Une procédure stockée ou une application peut accepter une entrée utilisateur, puis utiliser cette entrée dans une instruction SQL. Par exemple, une table peut stocker des informations sur les commandes client. Une application ou une procédure stockée peut accepter un ID de commande en entrée et exécuter une requête qui renvoie uniquement les résultats de cette commande spécifique.

Un développeur peut écrire du code de procédure stockée ou du code d’application avec des instructions SQL qui contiennent des espaces réservés, puis lier des variables à ces espaces réservés dans le code. Ces espaces réservés sont appelés variables de liaison. Un développeur peut également écrire du code qui construit des instructions SQL à partir d’une chaîne d’entrée (par exemple, en concaténant des chaînes contenant une commande SQL, des paramètres et des valeurs).

Les techniques suivantes sont disponibles pour construire des instructions SQL dynamiquement au moment de l’exécution :

Fonction TO_QUERY - Cette fonction prend une chaîne SQL avec des paramètres facultatifs en entrée.
Instruction SQL dynamique - Le code d’une procédure stockée ou d’une application prend une entrée et construit une instruction SQL dynamique avec cette entrée. Le code peut faire partie d’une procédure stockée Snowflake Scripting ou Javascript ou d’un bloc anonyme Snowflake Scripting. Vous pouvez également utiliser cette technique dans votre code d’application qui utilise un pilote Snowflake ou le API REST SQL Snowflake.

Note

Lorsque les programmes construisent des instructions SQL avec saisie utilisateur, il existe des risques de sécurité potentiels, comme l’injection SQL. Si les entrées effectuées dans les instructions SQL proviennent de sources externes, assurez-vous qu’elles sont validées. Pour plus d’informations, voir injection SQL.

Utilisation de la fonction TO_QUERY¶

Vous pouvez utiliser la fonction TO_QUERY dans le code pour les procédures stockées et les applications qui construisent des instructions SQL de manière dynamique. Cette fonction de table prend une chaîne SQL en entrée. La chaîne SQL peut également contenir des paramètres et vous pouvez spécifier les arguments à transmettre aux paramètres en tant que variables de liaison.

Voici un exemple simple d’appel de la fonction :

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

L’exemple suivant utilise la fonction TO_QUERY dans une procédure stockée :

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
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 get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

Appelez la procédure stockée :

CALL get_num_results_tq('SELECT 1');

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Utilisation de l’instruction SQL dynamique dans les procédures stockées et les applications¶

Pour construire des instructions SQL qui prennent en compte la saisie de l’utilisateur, vous pouvez utiliser une instruction SQL dynamique dans une procédure stockée Snowflake Scripting ou Javascript ou dans un bloc anonyme Snowflake Scripting. Vous pouvez également utiliser l’instruction SQL dynamique dans votre code d’application qui utilise un pilote Snowflake ou le API REST SQL Snowflake.

Cet exemple crée une procédure stockée avec Snowflake Scripting. La procédure stockée prend le texte SQL en entrée et construit une chaîne contenant une instruction SQL en y ajoutant le texte. L’instruction SQL dynamique est ensuite exécutée à l’aide de la commande EXECUTE IMMEDIATE.

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;

Copy

L’exemple suivant appelle la procédure :

CALL get_num_results('SELECT 1');

Copy

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

L’instruction SQL dynamique prend en charge les variables de liaison. L’exemple de Snowflake Scripting suivant utilise des variables de liaison représentées par les espaces réservés ? pour construire des instructions SQL de manière dynamique au moment de l’exécution. Ce bloc sélectionne des données à partir de la table invoices suivante :

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);

Copy

Exécuter le bloc anonyme :

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

Comparaison des techniques de construction d’instructions SQL de manière dynamique¶

Le tableau suivant décrit les avantages et les inconvénients des techniques de construction d’instructions SQL de manière dynamique.

Technique	Avantages	Inconvénients
Fonction TO_QUERY	Syntaxe simple Gestion des erreurs intégrée Sémantique spécifique pour le cas d’utilisation de la construction d’instructions SQL de manière dynamique Jeu de résultats déterminé automatiquement	Les requêtes ne peuvent pas être décrites ou expliquées avant l’exécution Uniquement valide dans la clause FROM d’une instruction SELECT Propre à Snowflake
Instruction SQL dynamique	Plus général et plus flexible que la fonction TO_QUERY Les requêtes peuvent être décrites ou expliquées avant l’exécution	Plus complexe que la fonction TO_QUERY Gestion manuelle des erreurs