Écriture de procédures stockées dans Exécution de scripts Snowflake¶

Cette rubrique explique comment écrire une procédure stockée en SQL en utilisant Exécution de scripts Snowflake.

Dans ce chapitre :

Introduction
Utilisation des arguments transmis à une procédure stockée
Renvoi de données tabulaires
Appel d’une procédure stockée depuis une autre procédure stockée
- 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

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 et l”Classic Console.

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 à Vue d’ensemble de la journalisation et du traçage.

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;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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. 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 comme identificateur d’objet
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;

Copy

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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. (Voir Utiliser une variable dans une instruction SQL (liaison).)

Par exemple, 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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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

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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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

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

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 colonnes 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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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

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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

-- 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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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 SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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