Utilisation des variables¶

Déclaration d’une variable¶

Avant de pouvoir utiliser une variable, vous devez la déclarer. Lorsque vous déclarez une variable, vous devez spécifier le type de la variable de plusieurs façons :

• Spécifier explicitement le type de données.

• Spécifier une expression pour la valeur initiale de la variable. Exécution de scripts Snowflake utilise l’expression pour déterminer le type de données de la variable. Voir Comment Exécution de scripts Snowflake déduit le type de données d’une variable.

Vous pouvez déclarer une variable des manières suivantes :

• Dans la section DECLARE du bloc, en utilisant l’une des méthodes suivantes :

  <variable_name> <type> ;
  
  <variable_name> DEFAULT <expression> ;
  
  <variable_name> <type> DEFAULT <expression> ;
  

  Copy

• Dans la section BEGIN … END du bloc (avant d’utiliser la variable) en utilisant la commande LET de l’une des manières suivantes :

  LET <variable_name> <type> { DEFAULT | := } <expression> ;
  
  LET <variable_name> { DEFAULT | := } <expression> ;
  

  Copy

Où :

variable_name

Nom de la variable. Le nom doit suivre les règles de dénomination pour Identificateurs d’objet.

type

Le type de données de la variable. Le type de données peut être l’un des suivants :

• Type de données SQL

• CURSOR

• RESULTSET

• EXCEPTION

DEFAULT expression ou . := expression

Affecte la valeur de expression à la variable.

Si type et expression sont tous deux spécifiés, l’expression doit correspondre à un type de données correspondant. Si les types ne correspondent pas, vous pouvez convertir la valeur vers le type spécifié.

L’exemple suivant déclare des variables dans la section DECLARE et dans la section BEGIN … END du bloc :

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
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) :

EXECUTE IMMEDIATE 
$$
DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|           10.00 |
+-----------------+

Les sections suivantes expliquent comment le type de données et le scope d’une variable sont déterminés :

• Comment Exécution de scripts Snowflake déduit le type de données d’une variable

• Comprendre la portée des déclarations

Pour plus d’informations sur l’attribution d’une valeur à une variable, voir Attribution d’une valeur à une variable déclarée.

Attribution d’une valeur à une variable déclarée¶

Pour attribuer une valeur à une variable qui a déjà été déclarée, utilisez l’opérateur := :

<variable_name> := <expression> ;

Où :

variable_name

Nom de la variable. Le nom doit suivre les règles de dénomination pour Identificateurs d’objet.

expression

L’expression est évaluée et la valeur résultante est affectée à la variable.

L’expression doit être évaluée à un type de données qui correspond au type de la variable. Si l’expression ne correspond pas au type, vous pouvez convertir la valeur au type de la variable.

Dans l’expression, vous pouvez utiliser des fonctions, y compris les fonctions SQL intégrées et les UDFs (fonctions définies par l’utilisateur).

Utilisation d’une variable¶

Vous pouvez utiliser des variables dans des expressions et avec des éléments du langage Exécution de scripts Snowflake (comme RETURN). Par exemple, le code ci-dessous utilise les variables revenue et cost dans une expression et la variable profit dans une instruction RETURN :

DECLARE
  profit NUMBER(38, 2);
  revenue NUMBER(38, 2);
  cost NUMBER(38, 2);
BEGIN
  ...
  profit := revenue - cost;
  ...
RETURN profit;

Pour utiliser une variable dans un gestionnaire d’exceptions (la section EXCEPTION d’un bloc), la variable doit être déclarée dans la section DECLARE ou transmise en argument à une procédure stockée. Elle ne peut pas être déclarée dans la section BEGIN. .. END. Pour plus d’informations, voir Passer des variables à un gestionnaire d’exceptions.

Utiliser une variable dans une instruction SQL (liaison)¶

Vous pouvez utiliser une variable dans une instruction SQL, ce qui est parfois appelé variable de liaison. Pour ce faire, faites précéder le nom de la variable d’un deux-points. Par exemple :

INSERT INTO my_table (x) VALUES (:my_variable)

Si vous utilisez la variable comme nom d’un objet (par exemple, le nom d’une table dans la clause FROM d’une instruction SELECT), utilisez le mot-clé IDENTIFIER pour indiquer que la variable représente un identificateur d’objet. Par exemple :

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

Si vous utilisez une variable dans une expression ou avec un élément du langage Exécution de scripts Snowflake (par exemple RETURN), vous n’avez pas besoin de préfixer la variable par deux points.

Par exemple, vous n’avez pas besoin du préfixe deux-points dans les cas suivants :

• Vous utilisez la variable avec RETURN. Dans cet exemple, la variable profit est utilisée avec un élément du langage Exécution de scripts Snowflake et n’a pas besoin du préfixe deux-points.

  RETURN profit;
  

  Copy

• Vous construisez une chaîne contenant une instruction SQL à exécuter. Dans cet exemple, la variable id_variable est utilisée dans une expression et n’a pas besoin du préfixe deux-points.

  LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
  

  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.

Définir des variables en fonction des résultats d’une instruction SELECT¶

Dans un bloc Snowflake Scripting, vous pouvez utiliser la clause INTO pour définir des variables sur les valeurs des expressions spécifiées dans une clause SELECT :

SELECT <expression1>, <expression2>, ... INTO :<variable1>, :<variable2>, ... FROM ... WHERE ...;

Lorsque vous utilisez cette syntaxe :

• variable1 est définie comme la valeur de l”expression1.

• variable2 est définie comme la valeur de l”expression2.

L’instruction SELECT doit retourner une seule ligne.

L’exemple suivant contient une instruction SELECT qui renvoie une seule ligne. L’exemple s’appuie sur les données de cette table :

CREATE OR REPLACE TABLE some_data (id INTEGER, name VARCHAR);
INSERT INTO some_data (id, name) VALUES
  (1, 'a'),
  (2, 'b');

L’exemple définit les variables Exécution de scripts Snowflake id et name aux valeurs retournées pour les colonnes portant ces noms.

DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN id || ' ' || name;
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) :

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN :id || ' ' || :name;
END;
$$
;

L’exemple imprime l”id et le name de la ligne renvoyée par l’instruction SELECT.

+-----------------+
| anonymous block |
|-----------------|
| 1 a             |
+-----------------+

Définir une variable sur la valeur de retour d’une procédure stockée¶

Voir Utilisation de la valeur renvoyée depuis un appel de procédure stockée.

Exemples d’utilisation de variables¶

L’exemple suivant montre comment déclarer une variable, affecter une valeur ou une expression à une variable, et convertir une valeur en type de données d’une variable :

DECLARE
  w INTEGER;
  x INTEGER DEFAULT 0;
  dt DATE;
  result_string VARCHAR;
BEGIN
  w := 1;                     -- Assign a value.
  w := 24 * 7;                -- Assign the result of an expression.
  dt := '2020-09-30'::DATE;   -- Explicit cast.
  dt := '2020-09-30';         -- Implicit cast.
  result_string := w::VARCHAR || ', ' || dt::VARCHAR;
  RETURN result_string;
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) :

EXECUTE IMMEDIATE $$
DECLARE
    w INTEGER;
    x INTEGER DEFAULT 0;
    dt DATE;
    result_string VARCHAR;
BEGIN
    w := 1;                     -- Assign a value.
    w := 24 * 7;                -- Assign the result of an expression.
    dt := '2020-09-30'::DATE;   -- Explicit cast.
    dt := '2020-09-30';         -- Implicit cast.
    result_string := w::VARCHAR || ', ' || dt::VARCHAR;
    RETURN result_string;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
| 168, 2020-09-30 |
+-----------------+

L’exemple suivant utilise une fonction SQL intégrée dans l’expression :

my_variable := SQRT(variable_x);

La déclaration suivante spécifie implicitement les types de données des variables profit, cost et revenue en spécifiant une valeur initiale du type de données prévu pour chaque variable.

L’exemple montre également comment utiliser l’instruction LET pour déclarer les variables cost et revenue en dehors de la partie DECLARE du bloc :

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
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) :

EXECUTE IMMEDIATE $$
DECLARE
    profit DEFAULT 0.0;
BEGIN
    LET cost := 100.0;
    LET revenue DEFAULT 110.0;
    profit := revenue - cost;
    RETURN profit;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|              10 |
+-----------------+

L’exemple suivant illustre le scope d’une variable. Cet exemple comprend deux variables et un paramètre qui ont tous le même nom mais une portée différente.

L’exemple contient trois blocs : le bloc le plus extérieur, le bloc central et le bloc le plus intérieur.

• Dans le bloc le plus interne, PV_NAME se résout en la variable déclarée et définie dans ce bloc le plus interne (qui a pour valeur innermost block variable).

• À l’intérieur du bloc central (et à l’extérieur du bloc le plus intérieur), PV_NAME se résout en la variable déclarée et définie dans le bloc central (qui a pour valeur middle block variable).

• Dans le bloc le plus externe (et à l’extérieur de tout bloc imbriqué), PV_NAME se résout en le paramètre transmis à la procédure stockée (qui est défini comme parameter par l’instruction CALL).

L’exemple s’appuie sur cette table :

CREATE OR REPLACE TABLE names (v VARCHAR);

Dans cet exemple, l’attribution de la chaîne innermost block variable à PV_NAME dans le bloc le plus à l’intérieur n’impacte pas la valeur de la variable dans le bloc du milieu. La variable du bloc le plus à l’intérieur est différente de celle du bloc du milieu, même si les deux variables ont le même nom.

CREATE OR REPLACE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
      PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
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 duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
    PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
END;
$$
;

Appelez la procédure stockée :

CALL duplicate_name('parameter');

Vérifiez les valeurs dans la table :

SELECT *
    FROM names
    ORDER BY v;

+--------------------------+
| V                        |
|--------------------------|
| innermost block variable |
| middle block variable    |
| parameter                |
+--------------------------+

La sortie s’affiche :

• Dans le bloc imbriqué le plus à l’intérieur (qui était imbriqué sur deux couches), la variable PV_NAME du bloc intérieur a été utilisée.

• Dans le bloc du milieu (qui était imbriqué sur une couche), la variable PV_NAME de ce bloc était utilisée.

• Dans le bloc le plus extérieur, le paramètre a été utilisé.

Pour un exemple de liaison d’une variable lors de l’ouverture d’un curseur, voir les exemples d’ouverture de curseurs.