EXECUTE IMMEDIATE¶

执行包含 SQL 语句或 Snowflake Scripting 语句的字符串。

您可以使用 EXECUTE IMMEDIATE 执行以下操作：

在 Snowflake Scripting 块中，执行动态 SQL，其中部分 SQL 语句在运行时前是未知的。有关示例，请参阅在 Snowflake Scripting 块中执行动态 SQL。
将会话变量设置为 SQL 语句，并引用会话变量来运行 SQL 语句。有关示例，请参阅将会话变量设置为语句并执行它。
If you are using SnowSQL or Snowsight, run a Snowflake Scripting anonymous block. For an example, see Running an anonymous block in SnowSQL or Snowsight.

语法¶

EXECUTE IMMEDIATE '<string_literal>'
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

EXECUTE IMMEDIATE <variable>
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

EXECUTE IMMEDIATE $<session_variable>
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

Copy

必填参数¶

'string_literal' 或 . variable 或 . session_variable

包含语句的字符串字面量、Snowflake Scripting 变量或会话变量。语句可以是以下任一语句：

单个 SQL 语句
存储过程调用
控制流语句（例如循环语句或分支语句）
块

如果使用会话变量，则语句的长度不得超过 :ref:` 会话变量的最大大小（256 字节）<label-initializing_session_variables>`。

可选参数¶

USING ( bind_variable [ , bind_variable ... ] ): 指定一个或多个绑定变量，这些变量保存要在游标的查询定义中（例如，在 WHERE 子句中）使用的值。

返回¶

EXECUTE IMMEDIATE 返回已执行语句的结果。例如，如果字符串或变量包含 SELECT 语句，则返回 SELECT 语句的结果集。

使用说明¶

string_literal、variable 或 session_variable 必须只包含一个语句。（一个块被视为一个语句，即使该块的主体包含多个语句。）
session_variable 前面必须有一个美元符号 ($)。
局部 variable 前面不得有美元符号 ($)。

示例¶

下面是使用 EXECUTE IMMEDIATE 命令的示例。

在 Snowflake Scripting 块中执行动态 SQL¶

以下示例在 Snowflake Scripting 块中执行动态 SQL。

执行包含变量的语句¶

此示例将执行在 Snowflake Scripting 存储过程中两个局部变量中定义的语句。此示例也表明，EXECUTE IMMEDIATE 不仅适用于字符串字面量，还适用于计算结果为字符串 (VARCHAR) 的表达式。

CREATE PROCEDURE execute_immediate_local_variable()
RETURNS VARCHAR
AS
DECLARE
  v1 VARCHAR DEFAULT 'CREATE TABLE temporary1 (i INTEGER)';
  v2 VARCHAR DEFAULT 'INSERT INTO temporary1 (i) VALUES (76)';
  result INTEGER DEFAULT 0;
BEGIN
  EXECUTE IMMEDIATE v1;
  EXECUTE IMMEDIATE v2  ||  ',(80)'  ||  ',(84)';
  result := (SELECT SUM(i) FROM temporary1);
  RETURN result::VARCHAR;
END;

Copy

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector):

CREATE PROCEDURE execute_immediate_local_variable()
RETURNS VARCHAR
AS
$$
DECLARE
  v1 VARCHAR DEFAULT 'CREATE TABLE temporary1 (i INTEGER)';
  v2 VARCHAR DEFAULT 'INSERT INTO temporary1 (i) VALUES (76)';
  result INTEGER DEFAULT 0;
BEGIN
  EXECUTE IMMEDIATE v1;
  EXECUTE IMMEDIATE v2  ||  ',(80)'  ||  ',(84)';
  result := (SELECT SUM(i) FROM temporary1);
  RETURN result::VARCHAR;
END;
$$;

Copy

调用存储过程：

CALL execute_immediate_local_variable();

Copy

+----------------------------------+
| EXECUTE_IMMEDIATE_LOCAL_VARIABLE |
|----------------------------------|
| 240                              |
+----------------------------------+

执行包含绑定变量的语句¶

此示例使用 EXECUTE IMMEDIATE 执行 Snowflake Scripting 存储过程中 USING 参数中包含绑定变量的 SELECT 语句。首先，创建表并插入数据：

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);

Copy

创建存储过程：

CREATE OR REPLACE PROCEDURE min_max_invoices_sp(
    minimum_price NUMBER(12,2),
    maximum_price NUMBER(12,2))
  RETURNS TABLE (id INTEGER, price NUMBER(12, 2))
  LANGUAGE SQL
AS
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

CREATE OR REPLACE PROCEDURE min_max_invoices_sp(
    minimum_price NUMBER(12,2),
    maximum_price NUMBER(12,2))
  RETURNS TABLE (id INTEGER, price NUMBER(12, 2))
  LANGUAGE SQL
AS
$$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

调用存储过程：

CALL min_max_invoices_sp(20, 30);

Copy

+----+-------+
| ID | PRICE |
|----+-------|
|  2 | 22.22 |
+----+-------+

将会话变量设置为语句并执行它¶

此示例执行会话变量中定义的语句：

SET stmt =
$$
    SELECT PI();
$$
;

Copy

EXECUTE IMMEDIATE $stmt;

Copy

+-------------+
|        PI() |
|-------------|
| 3.141592654 |
+-------------+

Running an anonymous block in SnowSQL or Snowsight¶

When you run a Snowflake Scripting anonymous block in SnowSQL or Snowsight, you must specify the block as a string literal (delimited by single quotes or double dollar signs), and you must pass the block to the EXECUTE IMMEDIATE command. For more information, see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector.

此示例会运行传递给 EXECUTE IMMEDIATE 命令的匿名块：

EXECUTE IMMEDIATE $$
DECLARE
  radius_of_circle FLOAT;
  area_of_circle FLOAT;
BEGIN
  radius_of_circle := 3;
  area_of_circle := PI() * radius_of_circle * radius_of_circle;
  RETURN area_of_circle;
END;
$$
;

Copy

+-----------------+
| anonymous block |
|-----------------|
|    28.274333882 |
+-----------------+