Utilisation des variables¶
Dans Exécution de scripts Snowflake, vous pouvez utiliser des variables dans des expressions, des instructions Exécution de scripts Snowflake 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 :
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> ;
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ù :
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 :
DEFAULT expression
ou .:= expression
Affecte la valeur de
expression
à la variable.Si
type
etexpression
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 letype
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”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; $$ ;
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 queNUMBER(3, 1)
) et qui a une grande capacité de stockage (par exemple,VARCHAR
plutôt queVARCHAR(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ù :
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;
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 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;
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;
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_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;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; $$ ;
L’exemple imprime l’ID et le nom 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 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; $$ ;
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”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; $$ ;
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”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; $$ ;
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.