使用 Snowflake Scripting 编写存储过程¶

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

简介¶

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

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

备注

If you are creating a Snowflake Scripting procedure in SnowSQL or Snowsight, you must use string literal delimiters (' or $$) around the body of the stored procedure.

有关详细信息，请参阅 Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector。

Snowflake 限制了 Snowflake Scripting 存储过程正文中源代码的最大大小。Snowflake 建议将大小限制为 100 KB。（代码以压缩形式存储，具体限制取决于代码的可压缩性。）

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

备注

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

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

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
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 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 表达式中按名称引用这些实参。Snowflake Scripting 存储过程支持输入 (IN) 和输出 (OUT) 实参。

当您在 Snowflake Scripting 存储过程的定义中指定输出实参时，存储过程可以将输出实参的当前值返回给进行调用的程序，例如匿名块或其他存储过程。存储过程获取输出实参的初始值，将该值保存到过程主体内的变量中，随后可选择性执行操作以更改变量的值，然后将更新后的值返回给进行调用的程序。

例如，可以将销售人员的用户标识符和季度销售额传递给名为 emp_quarter_calling_sp_demo 的存储过程。此存储过程会调用另一个名为 sales_total_out_sp_demo 的存储过程。sales_total_out_sp_demo 存储过程有一个输出实参，该实参会执行操作以将销售人员本季度的总销售额返回给进行调用的存储过程 emp_quarter_calling_sp_demo。有关此场景的示例，请参阅使用输出实参返回员工在一个季度的总销售额。

当传入值的数据类型与输出实参的数据类型不匹配时，系统将自动执行受支持的强制转换。有关示例，请参阅使用数据类型与进行调用的存储过程输入值不同的输出实参。有关 Snowflake 可以自动执行哪些强制转换的信息，请参阅可转换的数据类型。

GET_DDL 函数和 SHOW PROCEDURES 命令在输出中显示存储过程实参的类型（IN 或 OUT）。其他用于显示存储过程元数据的命令和视图不显示实参的类型，例如 DESCRIBE PROCEDURE 命令、Information Schema PROCEDURES 视图和 Account Usage PROCEDURES 视图。

存储过程无法通过在其签名中指定不同的实参类型来实现重载。例如，假设存储过程具有以下签名：

CREATE PROCEDURE test_overloading(a IN NUMBER)

Copy

以下 CREATE PROCEDURE 命令会失败并报错，提示存储过程已存在，因为该命令尝试创建的新存储过程与前例相比仅实参类型不同：

CREATE PROCEDURE test_overloading(a OUT NUMBER)

Copy

语法¶

使用以下语法在 Snowflake Scripting 存储过程定义中指定实参：

<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>

Copy

其中：

arg_name

实参的名称。名称必须遵循对象标识符的命名规则。

{ IN | INPUT | OUT | OUTPUT }

可选关键字，用于指定实参是输入实参还是输出实参。

IN 或 INPUT – 使用提供的值初始化实参，然后将该值赋给存储过程变量。可以在存储过程主体中修改该变量，但不能将其最终值传递给进行调用的程序。

IN 和 INPUT 是同义的。
OUT 或 OUTPUT – 使用提供的值初始化实参，然后将该值赋给存储过程变量。可以在存储过程主体中修改该变量，并且可以将其最终值传递给进行调用的程序。在存储过程主体中，只能使用变量为输出实参赋值。

也可以将未初始化的变量传递给输出实参。当没有为关联的变量赋值时，输出实参会返回 NULL。

OUT 和 OUTPUT 是同义的。

默认值：IN

arg_data_type

SQL 数据类型。

限制¶

必须在存储过程的定义中指定输出实参。
不能将输出实参指定为可选实参。也就是说，不能使用 DEFAULT 关键字指定输出实参。
在存储过程的主体中，必须使用变量为输出实参赋值。
同一个变量不能用于多个输出实参。
不能将会话变量传递给输出实参。
用户定义的函数 (UDFs) 不支持输出实参。
使用 SQL 以外的其他语言编写的存储过程不支持输出实参。
输出实参不能用于异步子作业。
存储过程最多支持 500 个实参，包括输入实参和输出实参。

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

以下存储过程使用 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

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

使用输出实参返回单个值¶

以下示例会创建存储过程 simple_out_sp_demo，并且其定义中包含输出实参 xout。存储过程将 xout 的值设置为 2。

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  xout := 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  xout := 2;
  RETURN 'Done';
END;
$$
;

Copy

以下匿名块将 x 变量的值设置为 1。然后，它调用 simple_out_sp_demo 存储过程并将变量指定为实参。

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
$$
;

Copy

输出显示 simple_out_sp_demo 存储过程执行了将输出实参的值设置为 2 的操作，然后将该值返回给了匿名块。

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

以下匿名块会调用 simple_out_sp_demo 存储过程并返回错误，因为它尝试使用表达式而不是变量为输出实参赋值。

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
$$
;

Copy

使用输出实参返回多个值用于多次调用存储过程¶

以下示例演示了与存储过程以及输入实参和输出实参相关的以下行为：

存储过程的定义中可以有多个输入实参和输出实参。
程序可以多次调用带有输出实参的存储过程，并且每次调用后都会保留输出实参的值。
输入实参不会将值返回给进行调用的程序。

创建存储过程 multiple_out_sp_demo 并在其定义中包括多个输入实参和输出实参。存储过程针对等效的输入实参和输出实参执行相同的操作。例如，存储过程将 1 添加到 p1_in 输入实参和 p1_out 输出实参。

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;

Copy

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
$$
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
$$
;

Copy

以下匿名块为与 multiple_out_sp_demo 存储过程的实参相对应的变量赋值，然后多次调用该存储过程。第一次调用使用在匿名块中指定的变量值，但后续每次调用都使用 multiple_out_sp_demo 存储过程中的输出实参返回的值。

BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;

Copy

+------------------------+
| anonymous block        |
|------------------------|
| [                      |
|   1,                   |
|   4,                   |
|   "hello",             |
|   "hello hi  hi  hi ", |
|   true,                |
|   false                |
| ]                      |
+------------------------+

使用数据类型与进行调用的存储过程输入值不同的输出实参¶

在某些用例中，传递给存储过程的值的数据类型与该存储过程的输出实参的数据类型可能不匹配。在这些情况下，系统会自动执行受支持的强制转换。

备注

尽管在某些情况下支持强制转换，但不建议这样做。

此示例演示了自动转换传递给数据类型为 NUMBER 的输出实参的 FLOAT 值。FLOAT 值自动转换为 NUMBER 值，然后传递回进行调用的匿名块。

创建 sp_out_coercion 存储过程，该存储过程采用类型为 NUMBER 的输出实参：

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  x := x * 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
$$
;

Copy

执行一个匿名块，以将 FLOAT 值传递给 sp_out_coercion 存储过程：

BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;

Copy

通过调用 SYSTEM$TYPEOF 函数，输出既显示返回值，又显示返回值的数据类型。请注意，从存储过程返回值后，该值会从 NUMBER 值强制转换回 FLOAT 值：

+---------------------------+
| anonymous block           |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+

使用输出实参返回员工在一个季度的总销售额¶

此示例使用以下 quarterly_sales 表：

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Copy

创建存储过程 sales_total_out_sp_demo，该存储过程采用两个输入实参表示员工标识符和季度，并采用一个输出实参来计算给定员工和季度的总销售额。

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
    id INT,
    quarter VARCHAR(20),
    total_sales OUT NUMBER(38,0))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  SELECT SUM(amount) INTO total_sales FROM quarterly_sales
    WHERE empid = :id AND
          quarter = :quarter;
  RETURN 'Done';
END;
$$
;

Copy

创建 emp_quarter_calling_sp_demo 存储过程，以调用 sales_total_out_sp_demo 存储过程。此存储过程还采用两个输入实参表示员工标识符和季度。

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;

Copy

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;

Copy

调用具有实参 2``（表示员工标识符）和 ``'2023_Q4'``（表示季度）的 ``emp_quarter_calling_sp_demo。

CALL emp_quarter_calling_sp_demo(2, '2023_Q4');

Copy

+-----------------------------------------------------+
| emp_quarter_calling_sp_demo                         |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+

返回表格数据¶

如果需要从存储过程返回表格数据（例如来自 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

将一个存储过程的输出实参值传递给进行调用的存储过程¶

当在 Snowflake Scripting 存储过程的定义中指定输出实参时，存储过程可以将输出实参的当前值返回给进行调用的存储过程。存储过程采用输出实参的初始值，将该值保存到存储过程主体内的变量中，随后可选择性执行操作以更改变量的值。然后，存储过程将更新后的值返回给进行调用的存储过程。

有关示例，请参阅使用输出实参返回员工在一个季度的总销售额。

使用嵌套存储过程¶

嵌套存储过程 是在匿名块或另一个存储过程（父存储过程）中块的作用域中定义的存储过程。

您在块（可以是 CREATE PROCEDURE 语句的一部分）的 DECLARE 部分中声明嵌套存储过程。以下示例显示了嵌套存储过程声明：

DECLARE
  <nested_stored_procedure_name> PROCEDURE (<arguments>)
     RETURNS <data_type>
     AS
     BEGIN
       <nested_procedure_procedure_statements>
     END;
BEGIN
  <statements>
END;

Copy

有关嵌套存储过程的声明语法的信息，请参阅嵌套存储过程声明语法。

嵌套存储过程仅存在于其块的作用域中。可以从其块（DECLARE、BEGIN ... END 和 EXCEPTION）的任意部分调用它。一个块可以包含多个嵌套存储过程，一个嵌套存储过程可以调用同一个块中的另一个嵌套存储过程。嵌套存储过程不能从其块外部调用或访问。

嵌套存储过程在与定义它的块相同的安全上下文中运行。在父存储过程中定义嵌套存储过程时，它会自动以与父存储过程相同的权限运行。

备注

嵌套存储过程声明和 CALL WITH 命令都会创建作用域有限的临时存储过程。它们在以下方面有所不同：

CALL WITH 语句可以出现在 SQL 语句能出现的任何地方（包括存储过程中），但是嵌套存储过程声明必须位于 Snowflake Scripting 块中。
CALL WITH 存储过程仅存在于其语句的作用域中，但嵌套存储过程存在于其 Snowflake Scripting 块的作用域中。

嵌套存储过程的优势¶

嵌套存储过程提供以下优势：

它们可以通过将逻辑封装在匿名块或父存储过程内来增强和简化安全性，从而防止从块或父存储过程外部访问它们。
它们通过在逻辑上将代码拆分成更小的块来保持代码模块化，这可使维护和调试更轻松。
它们通过减少对全局变量或其他实参的需求来提高可维护性，因为嵌套存储过程可以直接访问其块的局部变量。

调用嵌套存储过程的使用说明¶

以下使用说明适用于调用嵌套存储过程：

要将实参传递给嵌套存储过程，块可以使用常量值、Snowflake Scripting 变量、绑定变量、SQL（会话）变量和对用户定义函数的调用。
当传入值的数据类型与实参的数据类型不匹配时，Snowflake 会自动执行受支持的强制转换。有关 Snowflake 可以自动执行哪些强制转换的信息，请参阅数据类型转换。

嵌套存储过程中变量的使用说明¶

以下使用说明适用于嵌套存储过程中的变量：

嵌套存储过程可以引用其块中的变量，但这些变量须在其块的 DECLARE 部分中在嵌套存储过程声明之前之前声明。对于 DECLARE 部分中在它之后声明的变量，它无法引用。
嵌套存储过程无法访问在块的 BEGIN ... END 部分内的 LET 语句中声明的变量。
所引用变量的值反映了其在调用嵌套存储过程时的值。
嵌套存储过程可以修改引用的变量值，并且修改后的值会保留在块中，同时在同一个匿名块的单次执行中，或者在父存储过程的单次调用中，即使同一嵌套存储过程被多次调用，修改的值也会持续生效。
可将在嵌套存储过程调用之前声明的变量的值作为实参传递给嵌套存储过程。可以在调用中将变量值作为实参传递，即使变量是在嵌套存储过程声明之后或在 LET 语句中声明也不例外。

例如，以下存储过程会声明多个变量：

CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  var_before_nested_proc NUMBER DEFAULT 1;
  test_nested_variables PROCEDURE(arg1 NUMBER)
    -- <nested_sp_logic>
  var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
  LET var_let_before_call NUMBER DEFAULT 3;
  LET result := CALL nested_proc(:<var_name>);
  LET var_let_after_call NUMBER DEFAULT 3;
  RETURN result;
END;
$$;

Copy

在此示例中，仅可在 nested_sp_logic 中引用 var_before_nested_proc。

在嵌套存储过程调用中，以下任何变量的值都可以作为 var_name 中的实参传递给嵌套存储过程：

var_before_nested_proc
var_after_nested_proc
var_let_before_call

不能将 var_let_after_call 的值作为实参传递给嵌套存储过程。

针对嵌套存储过程的限制¶

以下限制适用于定义嵌套存储过程：

它们不能在其他嵌套存储过程内部或控制结构（例如 FOR 或 WHILE 循环）内部定义。
每个嵌套存储过程的名称在其块中必须是唯一的。也就是说，嵌套存储过程不能重载。
它们不支持输出 (OUT) 实参。
它们不支持带有默认值的可选实参。

以下限制适用于调用嵌套存储过程：

不能在 EXECUTE IMMEDIATE 语句中调用它们。
不能在异步子作业中调用它们。
它们不支持命名输入实参 (arg_name => arg)。实参必须通过位置指定。有关更多信息，请参阅 CALL。

嵌套存储过程的示例¶

以下示例使用嵌套存储过程：

定义一个返回表格数据的嵌套存储过程
定义一个返回标量值的嵌套存储过程
在匿名块中定义嵌套存储过程
定义一个传递实参的嵌套存储过程
定义调用另一个嵌套存储过程的嵌套存储过程

定义一个返回表格数据的嵌套存储过程¶

以下示例会定义一个返回表格数据的嵌套存储过程。该示例会创建名为 nested_procedure_example_table 的父存储过程，其具有名为 nested_return_table 的嵌套存储过程。该代码包含以下逻辑：

声明一个名为 res、类型为 RESULTSET 的变量。
在嵌套存储过程中包括以下逻辑：
- 声明一个名为 res2 的变量。
- 将值插入名为 nested_table 的表中。
- 将 res2 变量设置为针对表执行 SELECT 的结果。
- 返回结果集中的表格数据。
在父存储过程中创建 nested_table 表。
调用嵌套存储过程 nested_return_table 并将 res 变量设置为调用嵌套存储过程的结果。
返回 res 变量中的表格结果。

CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
  nested_return_table PROCEDURE()
    RETURNS TABLE()
    AS
    DECLARE
      res2 RESULTSET;
    BEGIN
      INSERT INTO nested_table VALUES(1);
      INSERT INTO nested_table VALUES(2);
      res2 := (SELECT * FROM nested_table);
      RETURN TABLE(res2);
    END;
BEGIN
  CREATE OR REPLACE TABLE nested_table(col1 INT);
  res := (CALL nested_return_table());
  RETURN TABLE(res);
END;
$$;

Copy

调用存储过程：

CALL nested_procedure_example_table();

Copy

+------+
| COL1 |
|------|
|    1 |
|    2 |
+------+

定义一个返回标量值的嵌套存储过程¶

以下示例会定义一个返回标量值的嵌套存储过程。该示例会创建名为 nested_procedure_example_scalar 的父存储过程，其具有名为 simple_counter 的嵌套存储过程。该代码包含以下逻辑：

声明一个类型为 NUMBER 的变量 counter，并将该变量的值设置为 0。
指定嵌套存储过程将 1 加到 counter 变量的当前值。
在父存储过程中调用嵌套存储过程三次。counter 变量的值会在嵌套存储过程的多次调用之间保持不变。
返回 counter 变量的值，即 3。

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

调用存储过程：

CALL nested_procedure_example_scalar();

Copy

+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3                               |
+---------------------------------+

在匿名块中定义嵌套存储过程¶

以下示例与定义一个返回标量值的嵌套存储过程中的示例大致相同，不同之处在于它在匿名块中定义的是嵌套存储过程，而不是存储过程：

EXECUTE IMMEDIATE $$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

定义一个传递实参的嵌套存储过程¶

以下示例会定义一个传递实参的嵌套存储过程。在该示例中，嵌套存储过程将值插入到下表中：

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);

Copy

该示例会创建名为 nested_procedure_example_arguments 的父存储过程，其具有名为 log_and_multiply_numbers 的嵌套存储过程。该嵌套存储过程采用两个类型为 NUMBER 的实参。该代码包含以下逻辑：

声明类型为 NUMBER 的变量 a、b 和 x。
包括执行以下操作的嵌套存储过程：
- 使用绑定变量将父存储过程传递给它的两个数字值插入 log_nested_values 表中。
- 将变量 x 的值设置为两个实参值相乘的结果。
- 将 x 的值返回给父存储过程。
将变量 a 的值设置为 5，将变量 b 的值设置为 10。
调用该嵌套存储过程。
返回在嵌套存储过程中设置的 x 变量的值。

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  a NUMBER;
  b NUMBER;
  x NUMBER;
  log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
    RETURNS NUMBER
    AS
    BEGIN
      INSERT INTO log_nested_values VALUES(:num1, :num2);
      x := :num1 * :num2;
      RETURN x;
    END;
BEGIN
  a := 5;
  b := 10;
  CALL log_and_multiply_numbers(:a, :b);
  RETURN x;
END;
$$;

Copy

调用存储过程：

CALL nested_procedure_example_arguments();

Copy

+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
|                                 50 |
+------------------------------------+

查询 log_nested_values 表以确认嵌套存储过程插入了传递给它的值：

SELECT * FROM log_nested_values;

Copy

+------+------+
| COL1 | COL2 |
|------+------|
|    5 |   10 |
+------+------+

定义调用另一个嵌套存储过程的嵌套存储过程¶

以下示例会定义一个嵌套存储过程，该存储过程会调用另一个嵌套存储过程。该示例会创建一个名为 nested_procedure_example_call_from_nested 的父存储过程，其中有两个名为 counter_nested_proc 和 call_counter_nested_proc 的嵌套存储过程。该代码包含以下逻辑：

声明一个类型为 NUMBER 的变量 counter，并将该变量的值设置为 0。
包括将 10 加到 counter 的值的嵌套存储过程 counter_nested_proc。
包括嵌套存储过程 call_counter_nested_proc，它会将 15 加到 counter 的值，还会调用 counter_nested_proc``（这会将另一个 ``10 加到 counter 的值）。
调用父存储过程中的两个嵌套存储过程。
返回 counter 变量的值，即 35。

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var1 NUMBER := 10;
    BEGIN
      counter := counter + var1;
    END;
  call_counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var2 NUMBER := 15;
    BEGIN
      counter := counter + var2;
      CALL counter_nested_proc();
    END;
BEGIN
  counter := 0;
  CALL counter_nested_proc();
  CALL call_counter_nested_proc();
  RETURN counter;
END;
$$;

Copy

调用存储过程：

CALL nested_procedure_example_call_from_nested();

Copy

+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
|                                        35 |
+-------------------------------------------+

在存储过程中使用和设置 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 |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+