Écriture de procédures stockées dans Exécution de scripts Snowflake¶
Cette rubrique fournit une introduction à l’écriture d’une procédure stockée en SQL en utilisant Snowflake Scripting. Pour plus d’informations sur les Snowflake Scripting, consultez Guide du développeur Exécution de scripts Snowflake.
Dans ce chapitre :
Introduction¶
Pour écrire une procédure stockée qui utilise Exécution de scripts Snowflake :
Utilisez la commande CREATE PROCEDURE ou WITH … CALL … avec le LANGUAGE SQL.
Dans le corps de la procédure stockée (la clause AS), vous utilisez un bloc Exécution de scripts Snowflake.
Note
Si vous créez une procédure Exécution de scripts Snowflake dans SnowSQL ou Classic Console , vous devez utiliser les délimiteurs de littéral de chaîne (
'
ou$$
) autour de la définition de la procédure stockée.Pour plus de détails, voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL, Classic Console, et le connecteur Python.
Vous pouvez capturer des données d’enregistrement et de trace pendant l’exécution du code de votre gestionnaire. Pour plus d’informations, reportez-vous à Journalisation, traçage et métriques.
Remarques :
Les mêmes règles concernant les droits de l’appelant et les droits du propriétaire s’appliquent à ces procédures stockées.
Les mêmes considérations et directives dans Travailler avec des procédures stockées s’appliquent aux procédures stockées d’Exécution de scripts Snowflake.
Voici un exemple de procédure stockée simple qui renvoie la valeur de l’argument qui lui est transmis :
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
RETURN message;
END;
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 output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL output_message('Hello World');
Voici un exemple de création et d’appel d’une procédure stockée anonyme à l’aide de la commande WITH … CALL … :
WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
CALL anonymous_output_message('Hello World');
Dans une procédure stockée anonyme, vous devez utiliser les délimiteurs de littéral de chaîne ('
ou $$
) autour de la définition de la procédure.
Utilisation des arguments transmis à une procédure stockée¶
Si vous transmettez des arguments à votre procédure stockée, vous pouvez faire référence à ces arguments par leur nom dans toute expression Exécution de scripts Snowflake. Voir les sections suivantes pour plus de détails.
Exemple simple d’utilisation des arguments transmis à une procédure stockée
Utilisation d’un argument dans une instruction SQL (liaison)
Utilisation d’un argument lors de la construction d’une chaîne pour une instruction SQL
Exemple simple d’utilisation des arguments transmis à une procédure stockée¶
La procédure stockée suivante utilise les valeurs des arguments dans les instructions IF et RETURN.
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
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 return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL return_greater(2, 3);
Utilisation d’un argument dans une instruction SQL (liaison)¶
Comme c’est le cas avec les variables Exécution de scripts Snowflake, si vous devez utiliser un argument dans une instruction SQL, mettez le signe deux-points (:
) devant le nom de l’argument. (Voir Utiliser une variable dans une instruction SQL (liaison).)
Les sections suivantes contiennent des exemples d’utilisation de variables de liaison dans des procédures stockées :
Exemple d’utilisation d’une variable de liaison dans une clause WHERE
Exemple d’utilisation d’une variable de liaison pour définir la valeur d’une propriété
Exemple d’utilisation de variables de liaison pour définir les paramètres d’une commande
Exemple d’utilisation d’une variable de liaison dans une clause WHERE¶
La procédure stockée suivante utilise l’argument id
dans la clause WHERE d’une instruction SELECT. Dans la clause WHERE, l’argument est spécifié comme :id
.
CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
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 find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL find_invoice_by_id('2');
En outre, la fonction TO_QUERY fournit une syntaxe simple pour accepter une chaîne SQL directement dans la clause FROM d’une instruction SELECT. Pour une comparaison entre la fonction TO_QUERY et le SQL dynamique, voir Construction d’instructions SQL au moment de l’exécution.
Exemple d’utilisation d’une variable de liaison pour définir la valeur d’une propriété¶
La procédure stockée suivante utilise l’argument comment
pour ajouter un commentaire pour une table dans une instruction CREATE TABLE. Dans l’instruction, l’argument est spécifié sous la forme :comment
.
CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
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_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL test_bind_comment('My Test Table');
Voir le commentaire de la table en effectuant une requête sur la vue TABLES dans INFORMATION_SCHEMA :
SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';
+---------------+
| COMMENT |
|---------------|
| My Test Table |
+---------------+
Vous pouvez également voir le commentaire en lançant une commande SHOW TABLES.
Exemple d’utilisation de variables de liaison pour définir les paramètres d’une commande¶
Supposons que vous ayez une zone de préparation interne nommée st
avec des fichiers CSV :
CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;
Vous souhaitez charger les données des fichiers CSV dans une table nommée test_bind_stage_and_load
:
CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);
La procédure stockée suivante utilise les paramètres FROM, ON_ERROR et VALIDATION_MODE dans une instruction COPY INTO <table>. Dans l’instruction, les valeurs des paramètres sont spécifiées comme suit : :my_stage_name
, :on_error
et :valid_mode
.
CREATE OR REPLACE PROCEDURE test_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
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_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');
Utilisation d’un argument comme identificateur d’objet¶
Si vous devez utiliser un argument pour faire référence à un objet (par exemple, un nom de table dans la clause FROM d’une instruction SELECT), utilisez le mot-clé IDENTIFIER pour indiquer que l’argument représente un identificateur d’objet. Par exemple :
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
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_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL get_row_count('invoices');
Cet exemple exécute une instruction CREATE TABLE. .. AS SELECT (CTAS) dans une procédure stockée en fonction des noms de table fournis en arguments.
CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
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 ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
$$
;
Avant d’appeler la procédure, créez une table simple et insérez des données :
CREATE OR REPLACE TABLE test_table_for_ctas_sp (
id NUMBER(2),
v VARCHAR(2))
AS SELECT
column1,
column2,
FROM
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
Appelez la procédure stockée pour créer une nouvelle table basée sur cette table :
CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');
Utilisation d’un argument lors de la construction d’une chaîne pour une instruction SQL¶
Notez que si vous construisez une instruction SQL comme une chaîne à transmettre à EXECUTE IMMEDIATE (voir Attribution d’une requête à un RESULTSET déclaré), ne préfixez pas l’argument par un signe deux-points. Par exemple :
CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
select_statement VARCHAR;
res RESULTSET;
BEGIN
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
RETURN TABLE(res);
END;
Renvoi de données tabulaires¶
Si vous devez renvoyer des données tabulaires (par exemple, des données provenant d’un RESULTSET) à partir de votre procédure stockée, spécifiez RETURNS TABLE(…) dans votre instruction CREATE PROCEDURE.
Si vous connaissez les types de données Snowflake des colonnes de la table renvoyée, spécifiez les noms et les types de colonnes dans RETURNS TABLE().
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...
Sinon (par exemple, si vous déterminez les types de colonnes pendant l’exécution), vous pouvez omettre les noms et les types de colonnes :
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...
Note
Actuellement, dans la clause RETURNS TABLE(...)
, vous ne pouvez pas spécifier GEOGRAPHY comme type de colonne. Ceci s’applique, que vous créiez une procédure stockée ou anonyme.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
Si vous tentez de spécifier GEOGRAPHY comme type de colonne, l’appel de la procédure stockée entraîne une erreur :
Stored procedure execution error: data type of returned table does not match expected returned table type
Pour contourner ce problème, vous pouvez omettre les arguments et les types de colonne dans RETURNS TABLE()
.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();
Si vous devez renvoyer les données dans un RESULTSET, utilisez TABLE() dans votre instruction RETURN.
Par exemple :
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
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_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL get_top_sales();
Appel d’une procédure stockée depuis une autre procédure stockée¶
Dans une procédure stockée, si vous devez appeler une autre procédure stockée, utilisez l’une des approches suivantes :
Appel d’une procédure stockée sans utiliser la valeur renvoyée
Utilisation de la valeur renvoyée depuis un appel de procédure stockée
Appel d’une procédure stockée sans utiliser la valeur renvoyée¶
Utilisez une instruction CALL pour appeler la procédure stockée (comme vous le feriez normalement).
Si vous devez transmettre des variables ou des arguments comme arguments d’entrée dans l’instruction CALL, n’oubliez pas d’utiliser le signe deux-points (:
) devant le nom de la variable. (Voir Utiliser une variable dans une instruction SQL (liaison).)
Voici un exemple de procédure stockée qui appelle une autre procédure stockée, mais qui ne dépend pas de la valeur de renvoi.
Tout d’abord, créez une table à utiliser dans l’exemple :
-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);
Ensuite, créez la procédure stockée que vous appellerez depuis une autre procédure stockée :
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
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 a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;
Ensuite, créez une deuxième procédure stockée qui appelle la première procédure stockée :
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
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 insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
$$
;
Enfin, appelez la deuxième procédure stockée :
CALL insert_two_values(4, 5);
Utilisation de la valeur renvoyée depuis un appel de procédure stockée¶
Si vous appelez une procédure stockée qui renvoie une valeur scalaire et que vous devez accéder à cette valeur, utilisez la clause INTO :snowflake_scripting_variable
dans l’instruction CALL pour capturer la valeur dans une variable Écriture de scripts Snowflake.
L’exemple suivant appelle la procédure stockée get_row_count
qui a été définie dans Utilisation d’un argument comme identificateur d’objet.
CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
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 count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$
;
Voici un exemple d’appel de la procédure stockée :
CALL count_greater_than('invoices', 3);
Si la procédure stockée renvoie une table, vous pouvez capturer la valeur de retour en définissant un RESULTSET sur une chaîne contenant l’instruction CALL. (Voir Attribution d’une requête à un RESULTSET déclaré.)
Pour récupérer la valeur de renvoi de l’appel, vous pouvez utiliser un CURSOR à la place du RESULTSET. Par exemple :
DECLARE
res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
IF (row_variable.col1 > 0) THEN
...;
ELSE
...;
END IF;
END FOR;
...
Utiliser et définir des variables SQL dans une procédure stockée¶
Par défaut, les procédures stockées de Snowflake Scripting s’exécutent avec les droits du propriétaire. Lorsqu’une procédure stockée s’exécute avec les droits du propriétaire, elle ne peut pas accéder aux variables SQL (ou de session).
Toutefois, une procédure stockée avec droits de l’appelant peut lire les variables de session de l’appelant et les utiliser dans la logique de la procédure stockée. Par exemple, une procédure stockée sur les droits de l’appelant peut utiliser la valeur d’une variable SQL dans une requête. Pour créer une procédure stockée qui s’exécute avec les droits de l’appelant, spécifiez le paramètre EXECUTE AS CALLER
dans l’instruction CREATE PROCEDURE.
Ces exemples illustrent cette différence clé entre les droits de l’appelant et les droits du propriétaire des procédures stockées. Ils tentent d’utiliser les variables SQL de deux manières :
Définir une variable SQL avant d’appeler la procédure stockée, puis utiliser la variable SQL à l’intérieur de la procédure stockée.
Définir une variable SQL à l’intérieur de la procédure stockée, puis utiliser la variable SQL après avoir quitté la procédure stockée.
L’utilisation de la variable SQL et le paramètre de la variable SQL fonctionnent tous deux correctement dans une procédure stockée relative aux droits de l’appelant. Les deux échouent lors de l’utilisation d’une procédure stockée avec les droits du propriétaire, même si l’appelant est le propriétaire.
Pour plus d’informations sur les droits du propriétaire et les droits de l’appelant, voir Présentation des procédures stockées des droits de l’appelant et des droits du propriétaire.
Utiliser une variable SQL dans une procédure stockée¶
Cet exemple utilise une variable SQL dans une procédure stockée.
Tout d’abord, définissez une variable SQL dans une session :
SET example_use_variable = 2;
Créez une procédure stockée simple qui s’exécute avec les droits de l’appelant et utilise cette variable SQL :
CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
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 use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
$$
;
Appelez la procédure stockée :
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 4 |
+-----------------------+
Définissez la variable SQL sur une valeur différente :
SET example_use_variable = 9;
Appelez à nouveau la procédure pour voir si la valeur retournée a changé :
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 18 |
+-----------------------+
Définir une variable SQL dans une procédure stockée¶
Vous pouvez définir une variable SQL dans une procédure stockée qui s’exécute avec les droits de l’appelant. Pour de plus amples informations, notamment sur l’utilisation des variables SQL dans les procédures stockées, voir Procédures stockées avec droits de l’appelant.
Note
Bien que vous puissiez définir une variable SQL à l’intérieur d’une procédure stockée et la laisser ensemble après la fin de la procédure, Snowflake ne recommande pas de faire cela.
Cet exemple permet de définir une variable SQL dans une procédure stockée.
Tout d’abord, définissez une variable SQL dans une session :
SET example_set_variable = 55;
Confirmez la valeur de la variable SQL :
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
Par exemple, la procédure stockée suivante donne une nouvelle valeur à la variable SQL example_set_variable
et renvoie cette nouvelle valeur :
CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
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 set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
$$
;
Appelez la procédure stockée :
CALL set_sql_variable_proc();
+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
| 52 |
+-----------------------+
Confirmez la nouvelle valeur de la variable SQL :
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+