É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

Pour écrire une procédure stockée qui utilise Exécution de scripts Snowflake :

Remarques :

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

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

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

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

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

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;

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

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

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

CALL find_invoice_by_id('2');

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

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

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(...) de CREATE PROCEDURE, vous ne pouvez pas spécifier GEOGRAPHY comme type de colonne.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...

Si vous le faites, l’appel de la procédure stockée entraîne une erreur :

CALL test_return_geography_table_1();
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 colonnes dans RETURNS TABLE().

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...

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

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

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

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

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 devez utiliser la valeur de renvoi de la procédure stockée que vous appelez, définissez 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. Notez que lorsque vous accédez au résultat de l’appel, le nom de la colonne contenant le résultat est le nom de la procédure stockée que vous avez appelée.

Par exemple, si vous avez appelé la procédure stockée get_row_count, vous pouvez obtenir la valeur de la variable variable_ligne.get_row_count.

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
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;

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

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;
$$
;

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

CALL count_greater_than('invoices', 3);