使用 Snowflake Scripting 编写存储过程¶

本主题介绍如何使用 Snowflake Scripting 在 SQL 中编写存储过程。

本主题内容：

简介¶

要编写使用 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 语句中使用变量（绑定）。）

例如，以下存储过程在 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

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

如果需要使用实参来引用对象（例如，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

为 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