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

• 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. Cela peut être :

• Un 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;

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

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

Copy

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;

Copy

Utiliser une variable dans une instruction SQL (liaison)¶

Vous pouvez utiliser une variable dans une instruction SQL. (Cette opération est parfois appelée liaison d’une variable). Faites précéder le nom de la variable d’un deux-points. Par exemple :

INSERT INTO my_table (x) VALUES (:my_variable)

Copy

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)

Copy

Notez que 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 si :

• 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

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

Copy

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.

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

Copy

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

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

Copy

L’exemple imprime l’ID et le nom de la ligne renvoyée par l’instruction SELECT.

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

Copy

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;

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

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

Copy

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

my_variable := SQRT(variable_x);

Copy

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;

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

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

Copy

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

Par exemple, l’affectation de la chaîne innermost block variable à PV_NAME dans le bloc le plus à l’intérieur n’affecte 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 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;

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

Copy

Appelez la procédure stockée :

CALL duplicate_name('parameter');

Copy

Vérifiez les valeurs dans la table. Cela montre les points suivants :

• 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é.

  SELECT *
      FROM names
      ORDER BY v;
  +--------------------------+
  | V                        |
  |--------------------------|
  | innermost block variable |
  | middle block variable    |
  | parameter                |
  +--------------------------+
  

  Copy

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