使用 Snowflake Scripting 编写存储过程¶

本主题介绍如何借助 Snowflake Scripting 使用 SQL 编写存储过程。有关 Snowflake Scripting 的更多信息，请参阅 Snowflake Scripting 开发者指南。

本主题内容：

简介¶

要编写使用 Snowflake Scripting 的存储过程，请执行以下操作：

将 CREATE PROCEDURE 或 WITH ... CALL ... 命令与 LANGUAGE SQL 一起使用。
在存储过程（AS 子句）的正文中，使用 Snowflake Scripting 块。

备注

如果要在 SnowSQL 或 Classic Console 中创建 Snowflake Scripting 过程，则必须在存储过程的正文周围使用 :ref:` 字符串字面量分隔符 <label-quoted_string_constants>` （' 或 $$）。

有关详细信息，请参阅在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting。

您可以在处理程序代码执行时获取日志和跟踪数据。有关更多信息，请参阅日志记录、跟踪和指标。

请注意以下事项：

有关调用方权限与所有者权限的相同规则也适用于这些存储过程。
使用存储过程中相同的注意事项和准则适用于 Snowflake Scripting 存储过程。

以下是一个简单存储过程的示例，该过程返回传入的实参的值：

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
END;

Copy

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

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  RETURN message;
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL output_message('Hello World');

Copy

以下是使用 WITH ... CALL ... 命令创建和调用匿名存储过程的示例：

WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
  RETURNS VARCHAR NOT NULL
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN message;
  END;
  $$
CALL anonymous_output_message('Hello World');

Copy

请注意，在匿名存储过程中，必须在过程正文周围使用 :ref:` 字符串字面量分隔符 <label-quoted_string_constants>` （' 或 $$）

使用传递给存储过程的实参¶

如果将任意实参传递给存储过程，则可以在任意 Snowflake Scripting 表达式中按名称引用这些实参。有关详细信息，请参阅以下部分：

使用传递给存储过程的实参的简单示例
在 SQL 语句中使用实参（绑定）
使用实参作为对象标识符
为 SQL 语句构建字符串时使用实参

使用传递给存储过程的实参的简单示例¶

以下存储过程使用 IF 和 RETURN 语句中的实参值。

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;

Copy

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL return_greater(2, 3);

Copy

在 SQL 语句中使用实参（绑定）¶

与 Snowflake Scripting 变量一样，如果需要在 SQL 语句中使用实参，请在实参名称前添加冒号 (:)。（请参阅在 SQL 语句中使用变量（绑定）。）

以下各部分包含在存储过程中使用绑定变量的示例：

在 WHERE 子句中使用绑定变量的示例
使用绑定变量设置属性值的示例
使用绑定变量在命令中设置参数的示例

在 WHERE 子句中使用绑定变量的示例¶

以下存储过程在 SELECT 语句的 WHERE 子句中使用 id 实参。在 WHERE 子句中，实参指定为 :id。

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL find_invoice_by_id('2');

Copy

此外，TO_QUERY 函数提供了一种简单的语法，可直接在 SELECT 语句的 FROM 子句中接受 SQL 字符串。有关 TO_QUERY 函数与动态 SQL 的比较，请参阅运行时构造 SQL。

使用绑定变量设置属性值的示例¶

以下存储过程在 CREATE TABLE 语句中使用 comment 实参为表添加注释。在该语句中，实参指定为 :comment。

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;

Copy

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL test_bind_comment('My Test Table');

Copy

通过查询 INFORMATION_SCHEMA 中的 TABLES 视图来查看表的注释：

SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';

Copy

+---------------+
| COMMENT       |
|---------------|
| My Test Table |
+---------------+

您也可以通过运行 SHOW TABLES 命令查看注释。

使用绑定变量在命令中设置参数的示例¶

假设您有一个具有 CSV 文件，且名为 st 的暂存区：

CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;

Copy

您要将 CSV 文件中的数据加载到名为 test_bind_stage_and_load 的表中：

CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);

Copy

以下存储过程在 COPY INTO <table> 语句中使用 FROM、ON_ERROR 和 VALIDATION_MODE 参数。在该语句中，参数值分别指定为 :my_stage_name、:on_error 和 :valid_mode。

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;

Copy

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');

Copy

使用实参作为对象标识符¶

如果需要使用实参来引用对象（例如，SELECT 语句的 FROM 子句的表名），请使用 IDENTIFIER 关键字来指示该实参表示对象标识符。例如：

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL get_row_count('invoices');

Copy

此示例根据实参中提供的表名称在存储过程中执行 CREATE TABLE ...AS SELECT (CTAS) 语句。

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;

Copy

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;
$$
;

Copy

在调用该过程之前，请创建一个简单的表并插入数据：

CREATE OR REPLACE TABLE test_table_for_ctas_sp (
  id NUMBER(2),
  v  VARCHAR(2))
AS SELECT
  column1,
  column2,
FROM
  VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c');

Copy

调用存储过程以创建基于此表的新表：

CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');

Copy

为 SQL 语句构建字符串时使用实参¶

请注意，如果将 SQL 语句构建为要传递给 EXECUTE IMMEDIATE 的字符串（请参阅将查询分配给已声明的 RESULTSET），请勿在实参前添加冒号。例如：

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

返回表格数据¶

如果需要从存储过程返回表格数据（例如来自 RESULTSET 的数据），请在 CREATE PROCEDURE 语句中指定 RETURNS TABLE(...)。

如果您知道返回表中列的 Snowflake 数据类型，请在 RETURNS TABLE() 中指定列名称和类型。

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...

Copy

否则（例如，如果您在运行时确定列类型），则可以省略列名称和类型：

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...

Copy

备注

目前，在 RETURNS TABLE(...) 子句中，您无法指定 GEOGRAPHY 作为列类型。这在创建存储过程和匿名过程中均适用。

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

Copy

如果尝试指定 GEOGRAPHY 为列类型，则调用存储过程会导致该错误：

Stored procedure execution error: data type of returned table does not match expected returned table type

Copy

要解决这个问题，可以省略 RETURNS TABLE() 中的列实参和类型。

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE()
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

Copy

如果需要返回 RESULTSET 中的数据，请在 RETURN 语句中使用 TABLE()。

例如：

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

以下是调用存储过程的示例：

CALL get_top_sales();

Copy

从另一个存储过程调用存储过程¶

在存储过程中，如果需要调用另一个存储过程，请使用下列方法之一：

在不使用返回值的情况下调用存储过程
使用从存储过程调用返回的值

在不使用返回值的情况下调用存储过程¶

使用 CALL 语句调用存储过程（通常使用）。

如果需要将任意变量或实参作为 CALL 语句中的输入实参传入，请记住在变量名称前添加冒号 (:)。（请参阅在 SQL 语句中使用变量（绑定）。）

以下是存储过程的示例，该存储过程调用另一个存储过程，但不依赖于返回值。

首先，创建一个用于示例的表：

-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);

Copy

然后，创建将从另一个存储过程调用的存储过程：

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;

Copy

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;

Copy

接下来，创建调用第一个存储过程的第二个存储过程：

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;

Copy

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;
$$
;

Copy

最后，调用第二个存储过程：

CALL insert_two_values(4, 5);

Copy

使用从存储过程调用返回的值¶

如果调用返回标量值的存储过程并且需要访问该值，请使用 CALL 语句的 INTO :snowflake_scripting_variable 子句来获取 Snowflake Scripting 变量中的值。

以下示例调用 label-stored_procedure_snowscript_arguments_identifier`中定义的 ``get_row_count` 存储过程。

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;

Copy

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  $$
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;
  $$
  ;

Copy

以下是调用存储过程的示例：

CALL count_greater_than('invoices', 3);

Copy

如果存储过程返回表，则可以通过将 RESULTSET 设置为包含 CALL 语句的字符串来获取返回值。（请参阅将查询分配给已声明的 RESULTSET。）

若要从调用中检索返回值，可以将 CURSOR 用于 RESULTSET。例如：

DECLARE
  res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
  IF (row_variable.col1 > 0) THEN
    ...;
  ELSE
    ...;
  END IF;
END FOR;
...

Copy

在存储过程中使用和设置 SQL 变量¶

默认情况下，Snowflake Scripting 存储过程以所有者权限运行。当存储过程以所有者权限运行时，它无法访问 SQL（或会话）变量。

但是，调用方权限存储过程可以读取调用方的会话变量，并在存储过程的逻辑中使用它们。例如，调用方权限存储过程可以使用查询中 SQL 变量的值。要创建以调用方权限运行的存储过程，请在 CREATE PROCEDURE 语句中指定 EXECUTE AS CALLER 参数。

这些示例说明了调用方权限存储过程和所有者权限存储过程之间的这个关键区别。它们尝试以两种方式使用 SQL 变量：

在调用存储过程之前设置 SQL 变量，然后在存储过程中使用 SQL 变量。
在存储过程中设置 SQL 变量，然后在从存储过程返回后使用 SQL 变量。

在调用方权限存储过程中，使用 SQL 变量和设置 SQL 变量都能正常工作。在使用所有者权限存储过程时，即使调用方是所有者，这两种操作也都会失败。

有关所有者权限和调用方权限的更多信息，请参阅了解调用方权限和所有者权限存储过程。

在存储过程中使用 SQL 变量¶

此示例在存储过程中使用 SQL 变量。

首先，在会话中设置 SQL 变量：

SET example_use_variable = 2;

Copy

创建以调用方权限运行的简单存储过程，并使用以下 SQL 变量：

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;

Copy

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;
$$
;

Copy

调用存储过程：

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                     4 |
+-----------------------+

将 SQL 变量设置为其他值：

SET example_use_variable = 9;

Copy

再次调用该过程，看看返回的值是否已更改：

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                    18 |
+-----------------------+

在存储过程设置 SQL 变量¶

您可以在以调用方权限运行的存储过程中设置 SQL 变量。有关更多信息，包括在存储过程中使用 SQL 变量的准则，请参阅调用方权限存储过程。

备注

尽管您可以在存储过程中设置 SQL 变量，并在过程结束后保留其设置，但 Snowflake 不建议这样做。

此示例在存储过程中设置 SQL 变量。

首先，在会话中设置 SQL 变量：

SET example_set_variable = 55;

Copy

确认 SQL 变量的值：

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+

例如，以下存储过程将 SQL 变量 example_set_variable 设置为新值并返回该新值：

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;

Copy

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;
$$
;

Copy

调用存储过程：

CALL set_sql_variable_proc();

Copy

+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
|                    52 |
+-----------------------+

确认 SQL 变量的新值：

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+