使用变量¶

在 Snowflake Scripting 中，可以在表达式、Snowflake Scripting 语句和 SQL 语句中使用变量。

声明变量¶

在使用变量之前，必须声明该变量。声明变量时，必须采用以下方式之一指定变量的类型：

显式指定数据类型。
为变量指定其初始值表达式。Snowflake Scripting 使用表达式来确定变量的数据类型。请参阅 Snowflake Scripting 如何推断变量的数据类型。

您可通过以下方式声明变量：

在块的 DECLARE 部分，使用以下任一方法：

<variable_name> <type> ;

<variable_name> DEFAULT <expression> ;

<variable_name> <type> DEFAULT <expression> ;

Copy

在块的 BEGIN ...END 部分中（在使用变量之前），通过以下任一方法使用 LET 命令：

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

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

Copy

其中：

variable_name
变量的名称。名称必须遵循对象标识符的命名规则。

type
变量的数据类型。数据类型可以是以下任何类型：

SQL 数据类型

CURSOR

RESULTSET

EXCEPTION

DEFAULT expression 或 . := expression
将 expression 的值赋给变量。

如果同时指定了 type 和 expression，表达式的计算结果必须是匹配的数据类型。如果类型不匹配，则可以将该值 :doc:` 转换 </sql-reference/functions/cast>` 为指定的 type 。

以下示例在块的 DECLARE 部分和 BEGIN ... END 部分声明了变量：

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

注意：如果您在 Python Connector 代码中使用 SnowSQL、Classic Console 或者 execute_stream 或 execute_string 方法，请改用本示例（请参阅在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting）：

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

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

以下章节将说明如何确定变量的数据类型和范围：

Snowflake Scripting 如何推断变量的数据类型
了解声明的范围

有关为变量赋值的信息，请参阅为已声明的变量赋值。

Snowflake Scripting 如何推断变量的数据类型¶

当您在未显式指定数据类型的情况下声明变量时，Snowflake Scripting 会根据您分配给变量的表达式推断数据类型。

如果选择从声明中省略数据类型，请注意以下事项：

如果表达式可以解析为不同大小的不同数据类型，Snowflake 通常会选择灵活（例如 FLOAT，而不是 NUMBER(3, 1)）且具有高存储容量（例如 VARCHAR，而不是 VARCHAR(4)）的类型。

例如，如果将变量设置为值 12.3，Snowflake 可以为该变量选择多种数据类型之一，包括：
- NUMBER(3, 1)
- NUMBER(38, 1)
- FLOAT
在此示例中，Snowflake 选择了 FLOAT。

如果需要变量的特定数据类型（尤其是数字或时间戳类型），Snowflake 建议您显式指定数据类型，即使您提供了初始值也是如此。
如果 Snowflake 无法推断预期的数据类型，则 Snowflake 会报告 SQL 编译错误。

例如，以下代码声明了变量，但未显式指定数据类型。该代码将变量设置为游标中的值。
```
...
FOR current_row IN cursor_1 DO:
  LET price := current_row.price_column;
  ...
```
Copy
编译 Snowflake Scripting 块时（例如，执行 CREATE PROCEDURE 命令时），游标尚未打开，游标中列的数据类型未知。因此，Snowflake 报告 SQL 编译错误：
```
092228 (P0000): SQL compilation error:
  error line 7 at position 4 variable 'PRICE' cannot have its type inferred from initializer
```
Copy

了解声明的范围¶

Snowflake Scripting 使用 ` 词法作用域 <https://en.wikipedia.org/wiki/Scope_(computer_science (link removed))#Lexical_scope>`_ 。当在块的 DECLARE 部分中声明值、结果集、游标或异常的变量时，所声明对象的作用域（或可见性）是该块以及嵌套在该块中的任何块。

如果块声明的对象与外部块中声明的对象同名，则在内部块（以及该块内的任何块）中，只有内部块的对象在作用域内。引用对象名称时，Snowflake 会查找具有该名称的对象，方法是首先从当前块开始查找，然后每次向外查找一个块，直到找到具有匹配名称的对象。

例如，如果在存储过程中声明了异常，则异常的作用域仅限于该存储过程。该存储过程调用的存储过程无法引发（或处理）该异常。调用该过程的存储过程无法处理（或引发）该异常。

为已声明的变量赋值¶

要为已声明的变量赋值，请使用 := 运算符：

<variable_name> := <expression> ;

Copy

其中：

variable_name
变量的名称。名称必须遵循对象标识符的命名规则。

expression
计算表达式，并将结果值赋给变量。

表达式的计算结果必须是与变量类型匹配的数据类型。如果表达式与类型不匹配，可以将值 :doc:` 转换 </sql-reference/functions/cast>` 为变量类型。

在表达式中，您可以使用函数，包括 :doc:` 内置 SQL 函数 </sql-reference-functions>` 和 UDFs （用户定义的函数）。

使用变量¶

您可以在表达式中使用变量，也可以将变量与 Snowflake Scripting 语言元素（如 RETURN）结合使用。例如，以下代码在表达式中使用变量 revenue 和 cost，在 RETURN 语句中使用变量 profit：

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

Copy

在 SQL 语句中使用变量（绑定）¶

您可以在SQL 语句中使用变量，有时将这称为：绑定一个变量。为此，请在变量名称前加上冒号。例如：

INSERT INTO my_table (x) VALUES (:my_variable)

Copy

如果使用变量作为对象的名称（例如，SELECT 语句的 FROM 子句中的表名称），请使用 IDENTIFIER 关键字来指示该变量表示对象标识符。例如：

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

Copy

如果在表达式中使用变量，或将变量与 Snowflake Scripting 语言元素）结合使用，则无需在变量前加冒号。

例如，在以下情况下，您无需添加冒号前缀：

您将变量与 RETURN 结合使用。在以下示例中，变量 profit 与 Snowflake Scripting 语言元素结合使用，不需要在变量前面加冒号。
```
RETURN profit;
```
Copy
您在构建一个包含要执行的 SQL 语句的字符串。在以下示例中，变量 id_variable 用在表达式中，不需要在变量前面加冒号。
```
LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
```
Copy

将变量设置为 SELECT 语句的结果¶

在 Snowflake Scripting 块中，您可以使用 INTO 子句将变量设置为 SELECT 子句中指定的表达式的值：

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

Copy

使用此语法时，请执行以下操作：

将 variable1 设置为 expression1 的值。
将 variable2 设置为 expression2 的值。

SELECT 语句必须返回一行。

以下示例包含返回单行的 SELECT 语句。该示例依赖于此表中的数据：

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

Copy

该示例将 Snowflake Scripting 变量 id 和 name 设置为具有这些名称的列返回的值。

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

Copy

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

该示例从 SELECT 语句返回的行中打印出 id 和 name。

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

Copy

将变量设置为存储过程的返回值¶

请参阅使用从存储过程调用返回的值。

使用变量的示例¶

以下示例演示了如何声明变量、为变量赋值或分配表达式，以及将值转换为变量的数据类型：

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

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

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

以下示例在表达式中使用内置 SQL 函数：

my_variable := SQRT(variable_x);

Copy

以下声明隐式指定了变量 profit、cost 和 revenue 的数据类型，方法是指定每个变量的预期数据类型初始值。

该示例还演示了如何使用 LET 语句在块的 DECLARE 部分之外声明 cost 和 revenue 变量：

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

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

Copy

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

以下示例演示了变量的作用域。此示例包括两个变量和一个参数，它们都具有相同的名称，但作用域不同。

该示例包含三个块：最外层、中间和最内层的块。

在最内层块中，PV_NAME 解析为在该最内层块中声明和设置的变量（即设置为 innermost block variable）。
在中间块内（以及最内层块之外），PV_NAME 解析为在中间块中声明和设置的变量（即设置为 middle block variable）。
在最外层块内（以及任何嵌套块之外），PV_NAME 解析为传递给存储过程的参数（即通过 CALL 语句设置为 parameter）。

该示例依赖于以下表：

CREATE OR REPLACE TABLE names (v VARCHAR);

Copy

在以下示例中，将字符串 innermost block variable 赋值给最内层块中的 PV_NAME不会影响中间块内变量的值。最内层块中的变量与中间块中的变量不同，即使两个变量具有相同的名称。

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;

Copy

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

Copy

调用存储过程：

CALL duplicate_name('parameter');

Copy

检查表中的值：

SELECT *
    FROM names
    ORDER BY v;

Copy

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

该输出显示：

在最内层的嵌套块（该块嵌套了两层）中，使用了内部块的变量 PV_NAME。
在中间块（该块嵌套了一层）中，使用了该中间块的变量 PV_NAME。
在最外层的块中，使用了该参数。

有关在打开游标时绑定变量的示例，请参阅打开游标的示例。