Utilisation des variables

Dans Exécution de scripts Snowflake, vous pouvez utiliser des variables dans des expressions et des instructions SQL.

Dans ce chapitre :

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 :

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

Où :

nom_variable

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 :

DEFAULT expression ou . := expression

Affecte la valeur de l’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 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) :

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

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

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

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

Comme mentionné précédemment, lorsque vous déclarez une variable sans spécifier explicitement le type de données, Exécution de scripts Snowflake déduit le type de données de l’expression que vous assignez à la variable.

Si vous choisissez d’omettre le type de données dans la déclaration, notez ce qui suit :

  • Si l’expression peut se résoudre en différents types de données de tailles différentes, Snowflake choisit généralement le type qui est flexible (par exemple, FLOAT plutôt que NUMBER(3, 1)) et qui a une grande capacité de stockage (par exemple, VARCHAR plutôt que VARCHAR(4)).

    Par exemple, si vous attribuez à une variable la valeur 12.3, Snowflake peut choisir l’un des nombreux types de données pour cette variable, notamment :

    • NUMBER(3, 1)

    • NUMBER(38, 1)

    • FLOAT

    Dans cet exemple, Snowflake choisit FLOAT.

    Si vous avez besoin d’un type de données spécifique pour une variable (en particulier un type numérique ou d’horodatage), Snowflake vous recommande de spécifier le type de données explicitement, même si vous fournissez une valeur initiale.

  • Si Snowflake est incapable de déduire le type de données prévu, Snowflake signale une erreur de compilation SQL.

    Par exemple, le code suivant déclare une variable sans spécifier explicitement le type de données. Le code définit la variable sur la valeur d’un curseur.

    ...
    for current_row in cursor_1 do:
        let price := current_row.price_column;
        ...
    

    Lorsque le bloc Exécution de scripts Snowflake est compilé (par exemple, lorsque la commande CREATE PROCEDURE est exécutée), le curseur n’a pas été ouvert et le type de données de la colonne dans le curseur est inconnu. En conséquence, Snowflake signale une erreur de compilation SQL :

    092228 (P0000): SQL compilation error:
        error line 7 at position 4 variable 'PRICE' cannot have its type inferred from initializer
    

Comprendre la portée des déclarations

Exécution de scripts Snowflake utilise la portée lexicale. Lorsqu’une variable pour une valeur, un jeu de résultats, un curseur ou une exception est déclarée dans la section DECLARE d’un bloc, la portée (ou visibilité) de l’objet déclaré est ce bloc et tous les blocs imbriqués dans ce bloc.

Si un bloc déclare un objet portant le même nom qu’un objet déclaré dans un bloc externe, dans le bloc interne (et dans tous les blocs à l’intérieur de ce bloc), seul l’objet du bloc interne est dans la portée. Lorsqu’un nom d’objet est référencé, Snowflake recherche l’objet portant ce nom en commençant d’abord par le bloc actuel, puis en allant vers l’extérieur, un bloc à la fois, jusqu’à ce qu’un objet portant le même nom soit trouvé.

Par exemple, si une exception est déclarée dans une procédure stockée, la portée de l’exception est limitée à cette procédure stockée. Les procédures stockées appelées par cette procédure stockée ne peuvent pas lever (ou traiter) cette exception. Les procédures stockées qui appellent cette procédure ne peuvent pas gérer (ou lever) cette exception.

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

nom_variable

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 une variable dans une expression. Par exemple, le code ci-dessous utilise les variables revenue et cost dans une expression :

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

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)

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)

Notez que si vous construisez une instruction SQL en tant que chaîne à transmettre dans EXECUTE IMMEDIATE (voir Attribution d’une requête à un RESULTSET déclaré), ne préfixez pas la variable par un deux-points. Par exemple :

LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);

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.

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

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

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 nom de la ligne renvoyée par l’instruction SELECT.

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

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

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

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

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

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;

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

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

Revenir au début