运行时构造 SQL¶

Snowflake 支持几种不同的技术，用于在运行时动态构造 SQL 语句字符串。通过使用这些技术，您可以为直到运行时才知道 SQL 语句全文的用例指定更通用而灵活的 SQL 字符串。

存储过程或应用程序可以接受用户输入，然后在 SQL 语句中使用该输入。例如，一个表可能存储有关销售订单的信息。应用程序或存储过程可能会接受订单 ID 作为输入，并运行仅返回该特定订单结果的查询。

开发者可以使用包含占位符的 SQL 语句编写存储过程代码或应用程序代码，然后将变量绑定到代码中的这些占位符。这些占位符称为绑定变量。开发者还可以编写代码，以便从输入字符串构造 SQL 语句（例如，通过连接包含 SQL 命令、参数和值的字符串）。

以下技术可用于在运行时动态构造 SQL 语句：

TO_QUERY 函数 - 此函数接受将可选参数作为输入的 SQL 字符串。
动态 SQL - 存储过程或应用程序中的代码接受输入，并使用此输入构造动态 SQL 语句。代码可以是 Snowflake Scripting 或 Javascript 存储过程的一部分，也可以是 Snowflake Scripting 匿名块的一部分。您也可以在使用 Snowflake 驱动程序或 Snowflake SQL API 的应用程序代码中使用这种技术。

备注

当程序根据用户输入构造 SQL 语句时，存在潜在的安全风险，例如 SQL 注入。如果 SQL 语句的输入来自外部来源，请确保它们有效。有关更多信息，请参阅 SQL 注入。

使用 TO_QUERY 函数¶

对于动态构造 SQL 语句的存储过程和应用程序，可以在代码中使用 TO_QUERY 函数。此表函数接受 SQL 字符串作为输入。或者，SQL 字符串可以包含参数，您可以指定要作为绑定变量传递给形参的实参。

下面是一个调用函数的简单示例：

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

下面的示例在存储过程中使用 TO_QUERY 函数：

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;

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 OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;

调用存储过程：

CALL get_num_results_tq('SELECT 1');

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

在存储过程和应用程序中使用动态 SQL¶

要构造接受用户输入的 SQL 语句，可以在 Snowflake Scripting 或 Javascript 存储过程或 Snowflake Scripting 匿名块中使用动态 SQL。您也可以在使用 Snowflake 驱动程序或 Snowflake SQL API 的应用程序代码中使用动态 SQL。

此示例使用 Snowflake Scripting 创建存储过程。存储过程接受 SQL 文本作为输入，通过向其中附加文本来构造一个包含 SQL 语句的字符串。然后，使用 EXECUTE IMMEDIATE 命令执行动态 SQL。

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;

下面的示例调用此过程：

CALL get_num_results('SELECT 1');

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

动态 SQL 支持绑定变量。下面的 Snowflake Scripting 示例使用 ? 占位符表示的绑定变量在运行时动态构造 SQL 语句。此块从以下 invoices 表中选择数据：

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);

执行匿名块：

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

比较用于动态构建 SQL 的技术¶

下表介绍了用于动态构建 SQL 的技术的优缺点。


技术	优点	缺点
TO_QUERY 函数	简单语法内置错误处理动态构造 SQL 的用例的特定语义自动确定的结果集	在执行之前无法描述或解释查询仅在 SELECT 语句的 FROM 子句中有效 Snowflake 特定
动态 SQL	比 TO_QUERY 函数更通用而灵活可以在执行之前描述或解释查询	比 TO_QUERY 函数更复杂手动错误处理