SQL 变量¶

变量标识符¶

SQL 变量使用不区分大小写的名称进行全局标识。

变量 DDL¶

Snowflake 提供了以下使用 SQL 变量的 DDL 命令：

• SET

• UNSET

• SHOW VARIABLES

使用 SQL 初始化会话中的变量¶

You can initialize variables in SQL using the SET command. The data type of the variable is derived from the data type of the result of the evaluated expression. The following examples initialize variables:

SET my_variable1 = 10;
SET my_variable2 = 'example';

您可以使用返回单个结果的查询来初始化变量。以下示例使用查询来初始化变量：

SET cust_last_name = (SELECT lname FROM customers WHERE customer_id=100);
SET timestamp_variable = (SELECT CURRENT_TIMESTAMP());

You can initialize multiple variables in the same statement, thereby reducing the number of round-trip communications with the server. The following examples initialize multiple variables:

SET (var1, var2, var3) = (10, 20, 30);
SET (current_user, current_warehouse) = ((SELECT CURRENT_USER()), (SELECT CURRENT_WAREHOUSE()));

在连接时设置变量¶

除了使用 SET 在会话中设置变量之外，在 Snowflake 中用于初始化会话的连接字符串中，您还可以将变量作为实参传递。如果连接字符串的规范是唯一可能的自定义工具，则此选项特别有用。

For example, using the Snowflake JDBC driver, you can set additional connection properties that are interpreted as parameters. The JDBC API requires SQL variables to be strings.

// Build connection properties
Properties properties = new Properties();

// Required connection properties
properties.put("user"    ,  "jsmith"      );
properties.put("password",  "mypassword");
properties.put("account" ,  "myaccount");

// Set some additional variables.
properties.put("$variable_1", "some example");
properties.put("$variable_2", "1"           );

// Create a new connection
String connectStr = "jdbc:snowflake://localhost:8080";

// Open a connection under the snowflake account and enable variable support
Connection con = DriverManager.getConnection(connectStr, properties);

查看会话的变量¶

若要查看当前会话中定义的所有变量，请使用 SHOW VARIABLES 命令：

SET (min, max)=(40, 70);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SHOW VARIABLES;

+----------------+-------------------------------+-------------------------------+------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name | value | type  | comment |
|----------------+-------------------------------+-------------------------------+------+-------+-------+---------|
| 10363773891062 | 2024-06-28 10:09:57.990 -0700 | 2024-06-28 10:09:58.032 -0700 | MAX  | 70    | fixed |         |
| 10363773891062 | 2024-06-28 10:09:57.990 -0700 | 2024-06-28 10:09:58.021 -0700 | MIN  | 40    | fixed |         |
+----------------+-------------------------------+-------------------------------+------+-------+-------+---------+

会话变量函数¶

The following convenience functions are provided for manipulating session variables to support compatibility with other database systems and to issue SQL through tools that do not support the $ syntax for accessing variables. All of these functions accept and return session variable values as strings:

• SYS_CONTEXT 和 SET_SYS_CONTEXT 中的限制

• SESSION_CONTEXT 和 SET_SESSION_CONTEXT 中的限制

• GETVARIABLE 和 SETVARIABLE 中的限制

以下是使用 GETVARIABLE 的示例。首先，使用 SET 命令定义变量：

SET var_artist_name = 'Jackson Browne';

+----------------------------------+
| status                           |
+----------------------------------+
| Statement executed successfully. |
+----------------------------------+

返回变量值：

SELECT GETVARIABLE('var_artist_name');

在此示例中，输出是 NULL，因为 Snowflake 存储的变量都是大写字母。

更新大小写：

SELECT GETVARIABLE('VAR_ARTIST_NAME');

+--------------------------------+
| GETVARIABLE('VAR_ARTIST_NAME') |
+--------------------------------+
| Jackson Browne                 |
+--------------------------------+

您可以在 WHERE 子句中使用变量名称，例如：

SELECT album_title
  FROM albums
  WHERE artist = $var_artist_name;