É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 :

Snowflake limite la taille maximale du code source dans le corps d’une procédure stockée Snowflake Scripting. Snowflake recommande de limiter la taille à 100 KB. (Le code est stocké sous une forme compressée et la limite exacte dépend de la capacité de compression du code).

Vous pouvez capturer des données d’enregistrement et de trace pendant l’exécution du code de votre gestionnaire. Pour plus d’informations, voir Journalisation, traçage et métriques.

Note

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL output_message('Hello World');
Copy

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

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. Les procédures stockées Snowflake Scripting prennent en charge des arguments d’entrée (IN) et de sortie (OUT).

Lorsque vous spécifiez un argument de sortie dans la définition d’une procédure stockée Snowflake Scripting, la procédure stockée peut renvoyer la valeur actuelle de l’argument de sortie à un programme appelant, tel qu’un bloc anonyme ou une procédure stockée différente. La procédure stockée prend une valeur initiale pour l’argument de sortie, enregistre la valeur dans une variable du corps de la procédure et effectue éventuellement des opérations pour modifier la valeur de la variable, avant de renvoyer la valeur mise à jour au programme appelant.

Par exemple, l’identificateur d’utilisateur d’un commercial et un trimestre de vente peuvent être transmis à une procédure stockée intitulée emp_quarter_calling_sp_demo. Cette procédure stockée appelle une procédure stockée différente intitulée sales_total_out_sp_demo. La procédure stockée sales_total_out_sp_demo possède un argument de sortie qui effectue des opérations pour renvoyer le total des ventes trimestrielles du commercial à la procédure stockée appelante emp_quarter_calling_sp_demo. Pour un exemple de ce scénario, voir Utilisation d’un argument de sortie pour renvoyer le total des ventes d’un employé au cours d’un trimestre.

En cas d’inadéquation entre le type de données de la valeur transmise et le type de données de l’argument de sortie, les coercitions prises en charge sont exécutées automatiquement. Pour un exemple, voir Utilisation d’un argument de sortie avec un type de données autre que la valeur d’entrée d’une procédure appelante. Pour plus d’informations sur les coercitions que Snowflake peut effectuer automatiquement, voir Types de données pouvant être convertis.

La fonction GET_DDL et la commande SHOW PROCEDURES affichent le type (soit IN ou OUT) d’arguments d’une procédure stockée en sortie. D’autres commandes et vues qui affichent des métadonnées sur les procédures stockées n’affichent pas le type des arguments, telles que la commande DESCRIBE PROCEDURE, le schéma d’information Vue PROCEDURES, et la PROCEDURES vue d’utilisation du compte.

Une procédure stockée ne peut pas être surchargée en spécifiant différents types d’arguments dans la signature. Par exemple, supposons qu’une procédure stockée possède cette signature :

CREATE PROCEDURE test_overloading(a IN NUMBER)
Copy

La commande suivante CREATE PROCEDURE échoue et renvoie une erreur indiquant que la procédure existe déjà, car elle tente de créer une nouvelle procédure stockée qui ne diffère de l’exemple précédent que par le type d’argument :

CREATE PROCEDURE test_overloading(a OUT NUMBER)
Copy

Syntaxe

Utilisez la syntaxe suivante pour spécifier un argument dans une définition de procédure stockée Snowflake Scripting :

<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>
Copy

Où :

arg_name

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

{ IN | INPUT | OUT | OUTPUT }

Mot clé facultatif qui spécifie si l’argument est un argument d’entrée ou un argument de sortie.

  • IN ou INPUT – L’argument est initialisé avec la valeur fournie et cette valeur est attribuée à une variable de procédure stockée. La variable peut être modifiée dans le corps de la procédure stockée, mais sa valeur finale ne peut pas être transmise à un programme appelant.

    IN et INPUT sont synonymes.

  • OUT ou OUTPUT – L’argument est initialisé avec la valeur fournie et cette valeur est attribuée à une variable de procédure stockée. La variable peut être modifiée dans le corps de la procédure stockée et sa valeur finale peut être transmise à un programme appelant. Dans le corps d’une procédure stockée, des valeurs ne peuvent être attribuées à des arguments de sortie qu’en utilisant des variables.

    Les arguments de sortie peuvent également recevoir des variables non initialisées. Lorsque la variable associée n’est pas attribuée, l’argument de sortie renvoie NULL.

    OUT et OUTPUT sont synonymes.

Par défaut : IN

arg_data_type

Un type de données SQL.

Limitations

  • Les arguments de sortie doivent être spécifiés dans la définition d’une procédure stockée.

  • Les arguments de sortie ne peuvent pas être spécifiés en tant qu” arguments facultatifs. En d’autres termes, les arguments de sortie ne peuvent pas être spécifiés à l’aide du mot-clé DEFAULT.

  • Dans le corps d’une procédure stockée, des variables doivent être utilisées pour attribuer des valeurs aux arguments de sortie.

  • La même variable ne peut pas être utilisée pour plusieurs arguments de sortie.

  • Les variables de session ne peuvent pas être passées aux arguments de sortie.

  • Les fonctions définies par l’utilisateur (UDFs) ne prennent pas en charge les arguments de sortie.

  • Les procédures stockées écrites dans des langages autres que SQL ne prennent pas en charge les arguments de sortie.

  • Les arguments de sortie ne peuvent pas être utilisés dans les tâches enfants asynchrones.

  • Les procédures stockées sont limitées à 500 arguments, y compris les arguments d’entrée et de sortie.

Exemples

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL return_greater(2, 3);
Copy

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. Pour plus d’informations, 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

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL find_invoice_by_id('2');
Copy

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL test_bind_comment('My Test Table');
Copy

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';
Copy
+---------------+
| 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;
Copy

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

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');
Copy
Exemples d’utilisation d’une variable de liaison pour un tableau

Vous pouvez développer une variable de liaison qui représente un tableau dans une liste de valeurs individuelles en utilisant l’opérateur de diffusion (**). Pour plus d’informations et d’exemples, voir Opérateurs d’expansion.

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL get_row_count('invoices');
Copy

L’exemple suivant exécute une instruction CREATE TABLE … AS SELECT (CTAS) dans une procédure stockée en fonction des noms de tableau 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;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

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

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

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

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

Utilisation d’un argument de sortie pour renvoyer une valeur unique

L’exemple suivant crée la procédure stockée simple_out_sp_demo avec l’argument de sortie xout dans sa définition. La procédure stockée définit la valeur de xout dans 2.

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  xout := 2;
  RETURN 'Done';
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  xout := 2;
  RETURN 'Done';
END;
$$
;
Copy

Le bloc anonyme suivant définit la valeur de la variable x à 1. Ensuite, il appelle la procédure stockée simple_out_sp_demo et spécifie la variable comme argument.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
$$
;
Copy

La sortie montre que la procédure stockée simple_out_sp_demo a effectué une opération pour définir la valeur de l’argument de sortie sur 2 et a ensuite renvoyé cette valeur au bloc anonyme.

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

Le bloc anonyme suivant appelle la procédure stockée simple_out_sp_demo et renvoie une erreur, car elle tente d’attribuer une valeur à l’argument de sortie en utilisant une expression au lieu d’une variable.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
$$
;
Copy

Utilisation d’arguments de sortie pour renvoyer plusieurs valeurs pour plusieurs appels à une procédure stockée

L’exemple suivant illustre le comportement suivant lié aux procédures stockées et aux arguments d’entrée et de sortie :

  • Une procédure stockée peut avoir plusieurs arguments d’entrée et de sortie dans sa définition.

  • Un programme peut appeler une procédure stockée avec des arguments de sortie plusieurs fois, et les valeurs des arguments de sortie sont conservées après chaque appel.

  • Les arguments d’entrée ne renvoient pas de valeurs au programme appelant.

Créez la procédure stockée multiple_out_sp_demo avec plusieurs arguments d’entrée et de sortie dans sa définition. La procédure stockée effectue les mêmes opérations sur les arguments d’entrée et de sortie équivalents. Par exemple, la procédure stockée ajoute 1 à l’argument d’entrée p1_in et à l’argument de sortie p1_out.

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
$$
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
$$
;
Copy

Le bloc anonyme suivant attribue des valeurs aux variables qui correspondent aux arguments de la procédure stockée``multiple_out_sp_demo`` et appelle ensuite la procédure stockée plusieurs fois. Le premier appel utilise les valeurs des variables spécifiées dans le bloc anonyme, mais chaque appel suivant utilise les valeurs renvoyées par les arguments de sortie dans la procédure stockée``multiple_out_sp_demo``.

BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

EXECUTE IMMEDIATE
$$
BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;
Copy
+------------------------+
| anonymous block        |
|------------------------|
| [                      |
|   1,                   |
|   4,                   |
|   "hello",             |
|   "hello hi  hi  hi ", |
|   true,                |
|   false                |
| ]                      |
+------------------------+

Utilisation d’un argument de sortie avec un type de données autre que la valeur d’entrée d’une procédure appelante

Pour certains cas d’utilisation, il peut y avoir une inadéquation entre le type de données de la valeur transmise à une procédure stockée et le type de données de l’argument de sortie de la procédure. Dans ces cas, des coercitions prises en charge sont effectués automatiquement.

Note

Bien que la coercition soit prise en charge dans certains cas, elle n’est pas recommandée.

Cet exemple illustre la conversion automatique d’une valeurFLOAT transmise à un argument de sortie avec un type de données NUMBER. La valeur FLOAT est automatiquement convertie en valeur NUMBER puis retransmise au bloc anonyme appelant.

Créez la procédure stockée sp_out_coercion, qui prend un argument de sortie de type NUMBER :

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
$$
;
Copy

Exécutez un bloc anonyme qui transmet une valeur FLOAT vers la procédure stockée sp_out_coercion :

BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

EXECUTE IMMEDIATE
$$
BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;
Copy

La sortie affiche à la fois la valeur renvoyée et le type de données de la valeur renvoyée, en appelant la fonction SYSTEM$TYPEOF. Notez que la valeur est contrainte à se transformer d’une valeur NUMBER vers une valeur FLOAT après son renvoi depuis la procédure stockée :

+---------------------------+
| anonymous block           |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+

Utilisation d’un argument de sortie pour renvoyer le total des ventes d’un employé au cours d’un trimestre

Cet exemple utilise les données du tableau quarterly_sales suivant :

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');
Copy

Créez la procédure stockée sales_total_out_sp_demo qui prend deux arguments d’entrée pour l’identificateur de l’employé et le trimestre, et un argument de sortie pour calculer le total des ventes pour l’employé et le trimestre donnés.

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
    id INT,
    quarter VARCHAR(20),
    total_sales OUT NUMBER(38,0))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  SELECT SUM(amount) INTO total_sales FROM quarterly_sales
    WHERE empid = :id AND
          quarter = :quarter;
  RETURN 'Done';
END;
$$
;
Copy

Créez la procédure stockée emp_quarter_calling_sp_demo qui appelle la procédure stockée sales_total_out_sp_demo. Cette procédure stockée prend également deux arguments d’entrée pour l’identificateur de l’employé et le trimestre.

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le Classic Console et le connecteur Python) :

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;
Copy

Appelez emp_quarter_calling_sp_demo avec les arguments 2 (pour l’identificateur de l’employé) et '2023_Q4' (pour le trimestre).

CALL emp_quarter_calling_sp_demo(2, '2023_Q4');
Copy
+-----------------------------------------------------+
| emp_quarter_calling_sp_demo                         |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+

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

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

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)
  ...
Copy
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();
Copy

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
Copy

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()
  ...
Copy
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();
Copy

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL get_top_sales();
Copy

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

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

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Enfin, appelez la deuxième procédure stockée :

CALL insert_two_values(4, 5);
Copy

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
  $$
  ;
Copy

Voici un exemple d’appel de la procédure stockée :

CALL count_greater_than('invoices', 3);
Copy

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

Transmission des valeurs des arguments de sortie d’une procédure stockée à une procédure stockée appelante

Lorsqu’un argument de sortie est spécifié dans la définition d’une procédure stockée Snowflake Scripting, la procédure stockée peut renvoyer la valeur actuelle de l’argument de sortie à une procédure stockée appelante. La procédure stockée prend une valeur initiale pour l’argument de sortie, enregistre la valeur dans une variable du corps de la procédure et effectue éventuellement des opérations pour modifier la valeur de la variable. La procédure stockée renvoie ensuite la valeur mise à jour à la procédure stockée appelante.

Pour un exemple, voir Utilisation d’un argument de sortie pour renvoyer le total des ventes d’un employé au cours d’un trimestre.

Utilisation de procédures stockées imbriquées

Une procédure stockée imbriquée est une procédure stockée qui est définie dans la portée d’un bloc anonyme ou d’un bloc dans une autre procédure stockée (la procédure stockée parente).

Vous déclarez une procédure stockée imbriquée dans la section DECLARE d’un bloc, qui peut faire partie d’une instruction CREATE PROCEDURE. L’exemple suivant montre la déclaration d’une procédure stockée imbriquée :

DECLARE
  <nested_stored_procedure_name> PROCEDURE (<arguments>)
     RETURNS <data_type>
     AS
     BEGIN
       <nested_procedure_procedure_statements>
     END;
BEGIN
  <statements>
END;
Copy

Pour plus d’informations sur la syntaxe de la déclaration d’une procédure stockée imbriquée, consultez Syntaxe de déclaration des procédures stockées imbriquées.

Une procédure stockée imbriquée n’existe que dans la portée de son bloc. Elle peut être appelée depuis n’importe quelle section de son bloc (DECLARE, BEGIN … END, et EXCEPTION). Un seul bloc peut contenir plusieurs procédures stockées imbriquées, et une procédure stockée imbriquée peut appeler une autre procédure stockée imbriquée dans le même bloc. Une procédure imbriquée ne peut pas être appelée ou accessible depuis l’extérieur de son bloc.

Une procédure stockée imbriquée fonctionne dans le même contexte de sécurité que le bloc qui la définit. Lorsqu’une procédure stockée imbriquée est définie dans une procédure stockée parent, elle s’exécute automatiquement avec les mêmes privilèges que la procédure stockée parente.

Note

La déclaration d’une procédure stockée imbriquée et la commande CALL WITH créent une procédure stockée temporaire avec une portée limitée. Elles diffèrent des manières suivantes :

  • Une instructionCALL WITH peut apparaître partout où une instruction SQL peut également apparaître, y compris dans une procédure stockée, mais une déclaration de procédure stockée imbriquée doit être dans un bloc Snowflake Scripting.

  • Une procédure stockée CALL WITH n’existe que dans la portée de son instruction, mais une procédure stockée imbriquée existe dans la portée de son bloc Snowflake Scripting.

Avantages des procédures stockées imbriquées

Les procédures stockées imbriquées offrent les avantages suivants :

  • Elles peuvent améliorer et simplifier la sécurité en encapsulant la logique dans un bloc anonyme ou une procédure stockée parente, ce qui en empêche l’accès depuis l’extérieur du bloc ou du parent.

  • Elles conservent le code modulaire en le divisant logiquement en morceaux plus petits, ce qui peut faciliter sa maintenance et son débogage.

  • Elles améliorent la maintenance en réduisant le besoin de variables globales ou d’arguments supplémentaires, car une procédure stockée imbriquée peut accéder directement aux variables locales de son bloc.

Notes sur l’utilisation pour l’appel de procédures stockées imbriquées

Les notes sur l’utilisation suivantes s’appliquent à l’appel d’une procédure stockée imbriquée :

Notes sur l’utilisation des variables dans une procédure stockée imbriquée

Les notes sur l’utilisation suivantes s’appliquent aux variables dans une procédure stockée imbriquée :

  • Une procédure stockée imbriquée peut référencer des variables de son bloc qui ont été déclarées avant la déclaration de la procédure stockée imbriquée dans la section DECLARE de son bloc. Elle ne peut pas référencer des variables déclarées après elle dans la section DECLARE.

  • Une procédure stockée imbriquée ne peut pas accéder aux variables déclarées dans une instruction LET dans la section BEGIN … END d’un bloc.

  • La valeur d’une variable référencée reflète sa valeur au moment où la procédure stockée imbriquée est appelée.

  • Une procédure stockée imbriquée peut modifier une valeur de variable référencée, et la valeur modifiée persiste dans le bloc et dans plusieurs invocations de la même procédure imbriquée dans une seule exécution de son bloc anonyme ou dans un seul appel à sa procédure stockée parente.

  • La valeur d’une variable qui a été déclarée avant l’appel d’une procédure stockée imbriquée peut être transmise en tant qu’argument à la procédure stockée imbriquée. La valeur de la variable peut être transmise en tant qu’argument dans un appel même si la variable a été déclarée après la déclaration de la procédure stockée imbriquée ou dans une instruction LET.

Par exemple, la procédure stockée suivante déclare plusieurs variables :

CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  var_before_nested_proc NUMBER DEFAULT 1;
  test_nested_variables PROCEDURE(arg1 NUMBER)
    -- <nested_sp_logic>
  var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
  LET var_let_before_call NUMBER DEFAULT 3;
  LET result := CALL nested_proc(:<var_name>);
  LET var_let_after_call NUMBER DEFAULT 3;
  RETURN result;
END;
$$;
Copy

Dans cet exemple, seulement var_before_nested_proc peut être référencée dans nested_sp_logic.

Dans l’appel de la procédure stockée imbriquée, la valeur de l’une des variables suivantes peut être transmise à la procédure stockée imbriquée comme argument dans var_name :

  • var_before_nested_proc

  • var_after_nested_proc

  • var_let_before_call

La valeur de var_let_after_call ne peut pas être transmise en tant qu’argument à la procédure stockée imbriquée.

Limites des procédures stockées imbriquées

Les limites suivantes s’appliquent à la définition de procédures stockées imbriquées :

  • Elles ne peuvent pas être définies à l’intérieur d’autres procédures stockées imbriquées ou à l’intérieur de structures de contrôle, comme les boucles FOR ou WHILE.

  • Chaque procédure stockée imbriquée doit avoir un nom unique dans son bloc. En d’autres termes, les procédures stockées imbriquées ne peuvent pas être surchargées.

  • Elles ne prennent pas en charge les arguments de sortie (OUT).

  • Elles ne prennent pas en charge les arguments facultatifs avec des valeurs par défaut.

Les limites suivantes s’appliquent à l’appel de procédures stockées imbriquées :

  • Elles ne peuvent pas être appelées dans une instruction EXECUTE IMMEDIATE.

  • Elles ne peuvent pas être appelées dans des tâches enfants asynchrones.

  • Elles ne prennent pas en charge les arguments d’entrée nommés (arg_name => arg). Les arguments doivent être spécifiés par position. Pour plus d’informations, voir CALL.

Exemples de procédures stockées imbriquées

Les exemples suivants utilisent des procédures stockées imbriquées :

Définir une procédure stockée imbriquée qui renvoie des données tabulaires

L’exemple suivant définit une procédure stockée imbriquée qui renvoie une donnée tabulaire. L’exemple crée une procédure stockée parente appelée nested_procedure_example_table avec une procédure stockée imbriquée appelée nested_return_table. Le code comprend la logique suivante :

  • Déclare une variable appelée res de type RESULTSET.

  • Inclut la logique suivante dans la procédure stockée imbriquée :

    • Déclare une variable appelée res2.

    • Insère des valeurs dans une table appelée nested_table.

    • Définit la variable res2 vers les résultats d’un SELECT sur le tableau.

    • Renvoie les données tabulaires dans le jeu de résultats.

  • Crée le tableau nested_table dans la procédure stockée parente.

  • Appelle la procédure stockée imbriquée nested_return_table et définit la variable res sur les résultats de l’appel à la procédure stockée imbriquée.

  • Renvoie les résultats tabulaires dans la variable res.

CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
  nested_return_table PROCEDURE()
    RETURNS TABLE()
    AS
    DECLARE
      res2 RESULTSET;
    BEGIN
      INSERT INTO nested_table VALUES(1);
      INSERT INTO nested_table VALUES(2);
      res2 := (SELECT * FROM nested_table);
      RETURN TABLE(res2);
    END;
BEGIN
  CREATE OR REPLACE TABLE nested_table(col1 INT);
  res := (CALL nested_return_table());
  RETURN TABLE(res);
END;
$$;
Copy

Appelez la procédure stockée :

CALL nested_procedure_example_table();
Copy
+------+
| COL1 |
|------|
|    1 |
|    2 |
+------+

Définition d’une procédure stockée imbriquée qui renvoie une valeur scalaire

L’exemple suivant définit une procédure stockée imbriquée qui renvoie une valeur scalaire. L’exemple crée une procédure stockée parente appelée nested_procedure_example_scalar avec une procédure stockée imbriquée appelée simple_counter. Le code comprend la logique suivante :

  • Déclare une variable appelée counter de type NUMBER, et définit la valeur de cette variable sur 0.

  • Spécifie que la procédure stockée imbriquée ajoute 1 à la valeur actuelle de la variable counter.

  • Appelle la procédure stockée imbriquée trois fois dans la procédure stockée parente. La valeur de la variable counter est reportée entre les invocations de la procédure stockée imbriquée.

  • Renvoie la valeur de la variable counter, qui est 3.

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;
Copy

Appelez la procédure stockée :

CALL nested_procedure_example_scalar();
Copy
+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3                               |
+---------------------------------+

Définition d’une procédure stockée imbriquée dans un bloc anonyme

L’exemple suivant est le même que l’exemple dans Définition d’une procédure stockée imbriquée qui renvoie une valeur scalaire, sauf qu’il définit une procédure stockée imbriquée dans un bloc anonyme au lieu d’une procédure stockée :

EXECUTE IMMEDIATE $$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;
Copy
+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

Définition d’une procédure stockée imbriquée à laquelle des arguments sont transmis

L’exemple suivant définit une procédure stockée imbriquée à laquelle des arguments sont transmis. Dans l’exemple, la procédure stockée imbriquée insère des valeurs dans le tableau suivant :

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);
Copy

L’exemple crée une procédure stockée parente appelée nested_procedure_example_arguments avec une procédure stockée imbriquée appelée log_and_multiply_numbers. La procédure stockée imbriquée prend deux arguments de type NUMBER. Le code comprend la logique suivante :

  • Déclare les variables a, b, et x de type NUMBER.

  • Comprend une procédure stockée imbriquée qui effectue les actions suivantes :

    • Insère les deux valeurs numériques qui lui sont transmises par la procédure stockée parente dans le tableau log_nested_values utilisant des variables de liaison.

    • Définit la valeur de la variable x au résultat de la multiplication des deux valeurs d’argument.

    • Renvoie la valeur de x à la procédure stockée parente.

  • Définit la valeur de la variable a à 5 et de la variable b à 10.

  • Appelle la procédure stockée imbriquée.

  • Renvoie la valeur de la variable x, qui a été définie dans la procédure stockée imbriquée.

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  a NUMBER;
  b NUMBER;
  x NUMBER;
  log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
    RETURNS NUMBER
    AS
    BEGIN
      INSERT INTO log_nested_values VALUES(:num1, :num2);
      x := :num1 * :num2;
      RETURN x;
    END;
BEGIN
  a := 5;
  b := 10;
  CALL log_and_multiply_numbers(:a, :b);
  RETURN x;
END;
$$;
Copy

Appelez la procédure stockée :

CALL nested_procedure_example_arguments();
Copy
+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
|                                 50 |
+------------------------------------+

Interrogez le tableau log_nested_values pour confirmer que la procédure stockée imbriquée a inséré les valeurs qui lui sont transmises :

SELECT * FROM log_nested_values;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    5 |   10 |
+------+------+

Définition d’une procédure stockée imbriquée qui appelle une autre procédure stockée imbriquée

L’exemple suivant définit une procédure stockée imbriquée qui appelle une autre procédure stockée imbriquée. L’exemple crée une procédure stockée parente appelée nested_procedure_example_call_from_nested avec deux procédures stockées imbriquées appelées counter_nested_proc et call_counter_nested_proc. Le code comprend la logique suivante :

  • Déclare une variable appelée counter de type NUMBER, et définit la valeur de cette variable sur 0.

  • Inclut la procédure stockée imbriquée counter_nested_proc qui ajoute 10 à la valeur de counter.

  • Inclut la procédure stockée imbriquée call_counter_nested_proc qui ajoute 15 à la valeur counter et appelle également counter_nested_proc (ce qui ajoute un autre 10 à la valeur de counter).

  • Appelle les deux procédures stockées imbriquées dans la procédure stockée parente.

  • Renvoie la valeur de la variable counter, qui est 35.

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var1 NUMBER := 10;
    BEGIN
      counter := counter + var1;
    END;
  call_counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var2 NUMBER := 15;
    BEGIN
      counter := counter + var2;
      CALL counter_nested_proc();
    END;
BEGIN
  counter := 0;
  CALL counter_nested_proc();
  CALL call_counter_nested_proc();
  RETURN counter;
END;
$$;
Copy

Appelez la procédure stockée :

CALL nested_procedure_example_call_from_nested();
Copy
+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
|                                        35 |
+-------------------------------------------+

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

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

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Appelez la procédure stockée :

CALL use_sql_variable_proc();
Copy
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                     4 |
+-----------------------+

Définissez la variable SQL sur une valeur différente :

SET example_use_variable = 9;
Copy

Appelez à nouveau la procédure pour voir si la valeur retournée a changé :

CALL use_sql_variable_proc();
Copy
+-----------------------+
| 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 définit 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;
Copy

Confirmez la valeur de la variable SQL :

SHOW VARIABLES LIKE 'example_set_variable';
Copy
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     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;
Copy

Remarque : Si vous utilisez Snowflake CLI, SnowSQL, Classic Console, ou la méthode execute_stream ou execute_string dans le code Connecteur Python, utilisez cet exemple à la place (voir Utilisation de Snowflake Scripting dans Snowflake CLI, SnowSQL, le 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;
$$
;
Copy

Appelez la procédure stockée :

CALL set_sql_variable_proc();
Copy
+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
|                    52 |
+-----------------------+

Confirmez la nouvelle valeur de la variable SQL :

SHOW VARIABLES LIKE 'example_set_variable';
Copy
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     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 |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+