使用 JavaScript 写入存储过程

本主题说明如何编写存储过程的 JavaScript 代码。

备注

要创建和调用匿名过程，请使用 CALL（使用匿名过程）。创建和调用匿名过程不需要具有 CREATE PROCEDURE 架构权限的角色。

本主题内容：

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

了解 JavaScript API

用于存储过程的 JavaScript API 与 Snowflake 连接器和驱动程序（Node.js、JDBC、Python 等）中的 APIs 类似，但并不相同。

借助 API，您能够执行以下操作：

  • 执行 SQL 语句。

  • 检索查询结果（即结果集）。

  • 检索结果集的元数据（列数、列的数据类型等）。

这些操作是通过在以下对象上调用方法来执行的：

  • snowflake 具有创建 Statement 对象和执行 SQL 命令的方法。

  • Statement 可以帮助您执行准备好的语句和访问这些准备好的语句的元数据，并允许您返回 ResultSet 对象。

  • ResultSet 可保存查询结果（例如，为 SELECT 语句检索的数据行）。

  • SfDate 是 JavaScript Date 的扩展（带有附加方法），是 Snowflake SQL 数据类型 TIMESTAMP_LTZ、TIMESTAMP_NTZ 和 TIMESTAMP_TZ 的返回类型。

JavaScript 存储过程 API 中详细描述了这些对象。

典型的存储过程包含类似于以下伪代码的代码：

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();
Copy

此代码使用名为 snowflake 的对象，这是一个需声明即可存在的特殊对象。该对象在每个存储过程的上下文中提供，并公开 API，进而允许您与服务器进行交互。

其他变量（例如 statement1）是通过 JavaScript var 语句创建的。例如：

var statement1 = ...;
Copy

如上面的代码示例所示，snowflake 对象允许您通过调用 API 中的一个方法来创建 Statement 对象。

以下是检索 ResultSet 并对其进行迭代的示例：

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;
Copy

示例 部分（位于本主题末尾）提供了其他示例，这些示例可用于练习存储过程 JavaScript API 中的每个对象和许多方法。

SQL 和 JavaScript 数据类型映射

在调用、使用存储过程并从存储过程获取值时，通常需要将 Snowflake SQL 数据类型转换为 JavaScript 数据类型，反之亦然。

在以下情况下，可能会发生 SQL 到 JavaScript 的转换：

  • 调用带有实参的存储过程。该实参是一种 SQL 数据类型；当它存储在存储过程内的 JavaScript 变量中时，必须对其进行转换。

  • 将值从 ResultSet 对象检索到 JavaScript 变量时。ResultSet 将值保存为 SQL 数据类型，而 JavaScript 变量必须将该值存储为 JavaScript 数据类型之一。

在以下情况下，可能会发生 JavaScript 到 SQL 的转换：

  • 从存储过程返回值。return 语句通常包含必须转换为 SQL 数据类型的 JavaScript 变量。

  • 动态构造使用 JavaScript 变量的 SQL 语句时。

  • 将 JavaScript 变量的值绑定到预编译语句时。

有关 Snowflake 如何映射 JavaScript 和 SQL 数据类型的更多信息，请参阅 SQL-JavaScript 数据类型映射。

一般提示

续行

SQL 语句可能很长，将它们放在一行中并不总是切合实际。JavaScript 将换行符视为语句的结尾。如果要将长 SQL 语句分成多行，可以使用处理长字符串的常用 JavaScript 技术，包括：

  • 在行尾前加一个反斜杠（续行字符）。例如：

    var sql_command = "SELECT * \
                       FROM table1;";
    Copy

  • 在字符串周围使用反引号（单反引号）而不是双引号。例如：

    var sql_command = `SELECT *
                       FROM table1;`;
    Copy

  • 累积字符串。例如：

    var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"
    Copy

JavaScript 存储过程注意事项

JavaScript 数字范围

精度完好无损的数字的范围是

-(2^53 -1)

至

(2^53 -1)

Snowflake NUMBER(p, s)和 DOUBLE 数据类型中的有效值范围更大。从 Snowflake 检索值并将其存储到 JavaScript 数值变量中可能会导致精度损失。例如：

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;
Copy
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+
Copy

前两列应匹配，第三列应包含 0.0。

该问题适用于 JavaScript 用户定义的函数 (UDFs) 和存储过程。

如果您在存储过程中使用 getColumnValue() 时遇到问题，则可以通过以字符串形式检索值来避免该问题，例如：

getColumnValueAsString()
Copy

然后，您可以返回存储过程中的字符串，并在 SQL 中将该字符串转换为数字数据类型。

JavaScript 错误处理。

由于存储过程是使用 JavaScript 编写的，所以可以使用 JavaScript 的 try/catch 语法。

存储过程可以抛出预定义异常或自定义异常。点击 此处 可查看抛出自定义异常的简单示例。

您可以在 try 块中执行 SQL 语句。如果发生错误，则 catch 块可以回滚所有语句（如果将语句放入事务中）。示例部分包含 :ref:` 在存储过程中回滚事务 <label-example__using_transaction_in_stored_procedures>` 的示例。

对存储过程的限制

存储过程有以下限制：

  • JavaScript 代码无法调用 JavaScript eval() 函数。

  • JavaScript 存储过程支持访问标准 JavaScript 库。请注意，这不包括浏览器通常提供的许多对象和方法。没有导入、包含或调用其他库的机制。允许使用第三方库可能会造成安全漏洞。

  • JavaScript 代码是在受限的引擎内执行的，因此无法从 JavaScript 上下文的系统调用系统资源（例如，无法访问网络和磁盘），并限制了引擎可用的系统资源，特别是内存。

JavaScript 实参区分大小写

存储过程代码 SQL 部分中的实参名称不区分大小写，但 JavaScript 部分区分大小写。

对于使用 JavaScript 的存储过程（和 UDFs），语句的 SQL 部分中的标识符（例如实参名称）会自动转换为大写（除非使用双引号分隔标识符），而 JavaScript 部分中的实参名称将保留其原始大小写。这可能会导致存储过程在不返回明确错误消息的情况下失败，因为无法查看实参。

以下是一个存储过程的示例，其中，JavaScript 代码中的实参名称与 SQL 代码中的实参名称不匹配，仅仅是因为大小写会有所不同：

在下面的示例中，第一个赋值语句不正确，因为名称 argument1 是小写的。

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;
Copy

在 SQL 语句和 JavaScript 代码中统一使用大写标识符（尤其是实参名称）往往可以减少无提示错误。

JavaScript 分隔符

存储过程代码的 JavaScript 部分必须放在单引号 ' 或双美元符号 $$ 内。

使用 $$ 可以更轻松地处理包含单引号的 JavaScript 代码，而无需“转义”这些引号。

重载存储过程名称

有关重载和命名约定的信息，请参阅 命名和重载过程和 UDFs。

绑定变量

将变量 绑定 到 SQL 语句后，就可以在语句中使用变量的值。

您可以绑定 NULL 值和非 NULL 值。

变量的数据类型应与 SQL 语句中使用的值相匹配。当前，只能绑定数字、字符串和 SfDate 类型的 JavaScript 变量。（有关 SQL 数据类型和 JavaScript 数据类型之间映射的详细信息，请参阅 SQL 和 JavaScript 数据类型映射。）

以下是绑定的简短示例：

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);
Copy

下面是一个更完整的示例。此示例绑定了 TIMESTAMP 信息。由于不支持 SQL TIMESTAMP 数据的直接绑定，因此本例以 VARCHAR 的形式传递时间戳，然后将其绑定到语句。请注意，SQL 语句本身会通过调用 TO_TIMESTAMP() 函数将 VARCHAR 转换为 TIMESTAMP：

如果指定的时间戳早于现在，则此简单函数返回 TRUE，否则返回 FALSE。

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;
Copy
CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+
Copy

此示例展示了如何将 VARCHAR、TIMESTAMP_LTZ 和其他数据类型绑定到 INSERT 语句。TIMESTAMP_LTZ 绑定了在存储过程中创建的 SfDate 变量。

创建表。

CREATE TABLE table1 (v VARCHAR,
                     ts1 TIMESTAMP_LTZ(9), 
                     int1 INTEGER,
                     float1 FLOAT,
                     numeric1 NUMERIC(10,9),
                     ts_ntz1 TIMESTAMP_NTZ,
                     date1 DATE,
                     time1 TIME
                     );
Copy

创建存储过程。此过程接受 VARCHAR，并使用 SQL 将 VARCHAR 转换为 TIMESTAMP_LTZ。然后，该过程从 ResultSet 检索转换后的值。该值存储在 SfDate 类型的 JavaScript 变量中。然后，存储过程将原始 VARCHAR 和 TIMESTAMP_LTZ 绑定到 INSERT 语句。此示例还演示了 JavaScript 数字数据的绑定。

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an SfDate variable.
    var my_sfDate = resultSet.getColumnValue(1); 

    f = 3.1415926;

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);` 
    // Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
    result = snowflake.execute(
        { 
        sqlText: sql_command, 
        binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ] 
        }
        ); 

    return my_sfDate; 
$$ ;
Copy

调用过程。

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+
Copy

验证该行是否已插入。

SELECT * FROM table1;
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
| V                   | TS1                           | INT1 |   FLOAT1 |    NUMERIC1 | TS_NTZ1                 | DATE1      | TIME1    |
|---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |    3 | 3.141593 | 3.141593000 | 2008-11-18 16:00:00.000 | 2008-11-18 | 12:30:00 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
Copy

有关在 JavaScript 中绑定数据的其他示例，请参阅 绑定语句参数。

代码要求

JavaScript 代码必须定义单个字面量 JavaScript 对象才能使存储过程有效。

如果 JavaScript 代码不满足此要求，则将创建存储过程；但是将无法调用该存储过程。

代码大小

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

运行时错误

存储过程中的大多数错误都出现在运行时，因为 JavaScript 代码是在存储过程运行时解释的，而不是在创建存储过程时解释的。

支持动态 SQL

存储过程可用于动态构造 SQL 语句。例如，您可以构建一个 SQL 命令字符串，其中包含预配置的 SQL 和用户输入（例如用户的账户）的组合。

有关示例，请参阅 动态创建 SQL 语句 和 示例 部分。

同步 API

Snowflake 存储过程的 API 是同步的。在存储过程中，一次只能运行一个线程。

请注意，这与使用 Node.js 连接器执行 JavaScript 的规则不同，后者可允许您运行异步线程。

示例

基本示例

以下示例展示了创建和调用存储过程的基本语法。它不执行任何 SQL 或过程代码。不过，它为以后更实际的示例提供了一个起点：

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;
Copy

请注意，$$ 分隔符表示 JavaScript 代码的开头和结尾。

现在调用刚才创建的过程：

CALL sp_pi();
+-----------+
|     SP_PI |
|-----------|
| 3.1415926 |
+-----------+
Copy

以下示例说明如何在存储过程中执行 SQL 语句：

  1. 创建表：

    CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));
    Copy

  2. 创建存储过程。这将在名为 stproc_test_table1 的现有表中插入一行，并返回值“Succeeded.”。从 SQL 的角度来看，返回值并不是特别有用，但它允许向用户返回状态信息（例如“Succeeded.”或“Failed.”）。

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;
    Copy

  3. 调用存储过程：

    call stproc1(5.14::FLOAT);
+------------+
| STPROC1    |
|------------|
| Succeeded. |
+------------+
    Copy

  4. 确认存储过程插入了该行：

    select * from stproc_test_table1;
+-----------+
|  NUM_COL1 |
|-----------|
| 5.1400000 |
+-----------+
    Copy

以下示例将检索结果：

  1. 创建过程来计算表中的行数（等效于 select count(*) from table）：

    create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  // Dynamically compose the SQL statement to execute.
  var sql_command = "select count(*) from " + TABLE_NAME;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  // Get back the row count. Specifically, ...
  // ... get the first (and in this case only) row from the result set ...
  res.next();
  // ... and then get the returned value, which in this case is the number of
  // rows in the table.
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;
    Copy

  2. 询问存储过程表中有多少行：

    call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+
    Copy

  3. 独立检查您获得的号码是否正确：

    select count(*) from stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+
    Copy

递归存储过程示例

以下示例展示了一个基本递归存储过程，但并不是特别现实：

create or replace table stproc_test_table2 (col1 FLOAT);
Copy
create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;
Copy
call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+
Copy
SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
Copy

动态创建 SQL 语句

以下示例展示了如何动态创建 SQL 语句：

备注

正如 SQL 注入 （本主题内容）中所述，使用动态 SQL 时要注意防范攻击。

  1. 创建存储过程。此过程允许您传递表的名称并获取该表中的行数（等效于 select count(*) from table_name）：

    create or replace procedure get_row_count(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    // Note that we uppercased the input parameter name.
    var sql_command = "select count(*) from " + TABLE_NAME;
    // Run the statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    // Get back the row count. Specifically, ...
    // ... first, get the first (and in this case only) row from the
    //  result set ...
    res.next();
    // ... then extract the returned value (which in this case is the
    // number of rows in the table).
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;
    Copy

  2. 调用存储过程：

    call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+
    Copy

  3. 显示同一个表的 select count(*) 结果：

    SELECT COUNT(*) FROM stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+
    Copy

检索结果集元数据

此示例演示了如何从结果集中检索少量元数据：

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );
Copy
create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;
Copy
call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy
call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+
Copy
call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy

使用 Try/Catch 捕获错误

此示例演示了使用 JavaScript try/catch 块来捕获存储过程中的错误：

  1. 创建存储过程：

        create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;
    Copy

  2. 调用存储过程。这应该返回一个显示错误编号和其他信息的错误：

        -- This is expected to fail.
    call broken();
+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 1003                                      |
|   State: 42000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| Snowflake.execute, line 4 position 20                   |
+---------------------------------------------------------+
    Copy

以下示例演示了抛出自定义异常：

  1. 创建存储过程：

    CREATE OR REPLACE PROCEDURE validate_age (age float)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    try {
        if (AGE < 0) {
            throw "Age cannot be negative!";
        } else {
            return "Age validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;
    Copy

  2. 使用有效和无效值调用存储过程：

    CALL validate_age(50);
+----------------+
| VALIDATE_AGE   |
|----------------|
| Age validated. |
+----------------+
CALL validate_age(-2);
+--------------------------------+
| VALIDATE_AGE                   |
|--------------------------------|
| Error: Age cannot be negative! |
+--------------------------------+
    Copy

在存储过程中使用事务

以下示例将多个相关语句封装在一个事务中，并使用 try/catch 来提交或回滚。参数 force_failure 允许调用方在成功执行和故意错误之间进行选择。

-- Create the procedure
create or replace procedure cleanup(force_failure varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "BEGIN WORK;"} );
  try {
      snowflake.execute( {sqlText: "DELETE FROM child;"} );
      snowflake.execute( {sqlText: "DELETE FROM parent;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
          }
      snowflake.execute( {sqlText: "COMMIT WORK;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

call cleanup('fail');

call cleanup('do not fail');
Copy

记录错误

您可以使用 JavaScript API 中的 snowflake 对象从 JavaScript 处理程序代码中获取日志和跟踪数据。这样，日志消息和跟踪数据就会存储在事件表中，您可以使用查询对其进行分析。

有关更多信息，请参阅以下内容：

使用 RESULT_SCAN 从存储过程检索结果

此示例说明如何使用 RESULT_SCAN 函数从 CALL 语句中检索结果：

  1. 创建并加载表：

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    Copy
    INSERT INTO western_provinces(ID, province) VALUES
    (1, 'Alberta'),
    (2, 'British Columbia'),
    (3, 'Manitoba')
    ;
    Copy

  2. 创建存储过程。此过程返回一个格式良好的字符串，它看起来像一个由三行组成的结果集，但实际上是一个单个的字符串：

    CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      var command = "SELECT * FROM western_provinces ORDER BY province;"
      var stmt = snowflake.createStatement( {sqlText: command } );
      var rs = stmt.execute();
      if (rs.next())  {
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      while (rs.next())  {
          return_value += "\n";
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return return_value;
  $$
  ;
    Copy

  3. 调用存储过程，然后使用 RESULT_SCAN 检索结果：

    CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
    Copy

您可以对 RESULT_SCAN 函数返回的值执行更复杂的操作。在本例中，由于返回值是单个字符串，因此可能需要提取该字符串中包含的单个“行”，并将这些行存储到另一个表中。

以下示例是上一个示例的延续，说明了实现这一目的的一种方法：

  1. 创建用于长期存储的表。此表包含省份名称和您从 CALL 命令返回的字符串中提取省份后的省份 ID：

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    Copy

  2. 调用存储过程，然后使用 RESULT_SCAN 检索结果，再从字符串中提取三行并将这些行放入表中：

    INSERT INTO all_provinces
  WITH 
    one_string (string_col) AS
      (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
    three_strings (one_row) AS
      (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
  SELECT
         STRTOK(one_row, ',', 1) AS ID,
         STRTOK(one_row, ',', 2) AS province
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
    Copy

  3. 通过显示表中的行来验证此方法是否有效：

    SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+
    Copy

以下代码大致相同，但步骤较少：

  1. 创建名为 one_string 的表。此表暂时存储 CALL 命令的结果。CALL 的结果是单个字符串，因此此表仅存储一个 VARCHAR 值。

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    Copy

  2. 调用存储过程后使用 RESULT_SCAN 检索结果（字符串），然后将结果存储到名为 one_string 的中间表中：

    CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
INSERT INTO one_string
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+
    Copy

    这显示了 one_string 表中的新行。请记住，尽管它的格式看起来像三行，但实际上，它是单个字符串：

    SELECT string_col FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
-- Show that it's one string, not three rows:
SELECT '>>>' || string_col || '<<<' AS string_col 
    FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| >>>1, Alberta       |
| 2, British Columbia |
| 3, Manitoba<<<      |
+---------------------+
SELECT COUNT(*) FROM one_string;
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+
    Copy

    以下命令显示如何从字符串中提取多行：

    SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+-----+-------+---------------------+
| STRING_COL          | SEQ | INDEX | VALUE               |
|---------------------+-----+-------+---------------------|
| 1, Alberta          |   1 |     1 | 1, Alberta          |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     2 | 2, British Columbia |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     3 | 3, Manitoba         |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
+---------------------+-----+-------+---------------------+
SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+
| VALUE               |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
    Copy

  3. 接下来，创建一个名为 three_strings 的表。将结果拆分为单个行/字符串后，此表将保存拆分结果：

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    Copy

  4. 现在将 one_string 表中的一个字符串转换为三个单独的字符串，并显示它现在实际上是三个字符串：

    INSERT INTO three_strings
  SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT string_col 
    FROM three_strings;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
SELECT COUNT(*) 
    FROM three_strings;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+
    Copy

  5. 现在，在我们名为 all_provinces 的长期表中将三个字符串转换为三行：

    INSERT INTO all_provinces
  SELECT 
         STRTOK(string_col, ',', 1) AS ID, 
         STRTOK(string_col, ',', 2) AS province 
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
    Copy

  6. 显示长期表中的三行：

    SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+
SELECT COUNT(*) 
    FROM all_provinces;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+
    Copy

返回错误消息数组

存储过程可能会执行多条 SQL 语句，所以您可能需要为每条 SQL 语句返回状态/错误消息。但是，存储过程只返回单行；而不是用于返回多行。

如果所有消息都符合 ARRAY 类型的单个值，那么只需稍加努力，您就可以从存储过程中获取所有消息。

以下示例展示了一种执行此操作的方法（显示的错误消息不是真实的，但您可以扩展此代码并将其与实际 SQL 语句配合使用）：

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;
Copy
CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+
Copy

请注意，这 不是 通用解决方案。ARRAY 数据类型的最大大小是有限制的，整个结果集必须包含在一个 ARRAY 中。

返回结果集

本节扩展了先前在 返回错误消息数组 中描述的示例。此示例更为通用，并允许您从查询中返回结果集。

存储过程只返回包含单列的单行；而不是用于返回结果集。但是，如果结果集足够小，小到可以容纳进 VARIANT 或 ARRAY 类型的单个值中，那么只需编写一些附加代码，就可以从存储过程中返回结果集：

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');
Copy
-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      var cmd1_dict = {sqlText: command};
      var stmt = snowflake.createStatement(cmd1_dict);
      var rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          var col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;
Copy
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+
Copy

此示例展示了如何将前两行合并为一行：

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+
Copy

为方便起见，您可以将前一行封装在视图中。此视图还将字符串“null”转换为真 NULL。您只需要创建一次视图。但是，每次 使用该视图时，都 必须 在从该视图中进行选择 之前立即 调用该存储过程。请记住，视图中对 RESULT_SCAN 的调用通过最新语句执行，该语句必须是 CALL：

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);
Copy
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+
Copy

您甚至可以将其用作真正的视图（即选择其中的一个子集）：

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+
Copy

请注意，这 不是 通用解决方案。VARIANT 和 ARRAY 数据类型的最大大小是有限制的，整个结果集必须包含在一个 VARIANT 或 ARRAY 中。

保护隐私

此示例展示了对在线零售商有用的存储过程。该存储过程尊重客户隐私，同时保护零售商和客户的合法利益。如果客户出于隐私原因要求零售商删除自己的数据，则此存储过程会删除该客户的大部分数据，但如果满足以下任一条件，则会保留客户的购买历史记录：

  • 所购商品的保修期尚未到期。

  • 客户仍然欠款（或者客户需要退款）。

更现实的版本是删除已付款且保修期已过的单行。

  1. 首先创建并加载表：

    create table reviews (customer_ID VARCHAR, review VARCHAR);
create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                               product_ID VARCHAR, purchase_date DATE);
    Copy
    insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
    (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
    (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
    (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);

insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');
    Copy

  2. 创建存储过程：

    create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
    returns varchar not null
    language javascript
    as
    $$

    // If the customer posted reviews of products, delete those reviews.
    var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
    snowflake.execute( {sqlText: sql_cmd} );

    // Delete any other records not needed for warranty or payment info.
    // ...

    var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;

    // Find out if the customer has any net unpaid balance (or surplus/prepayment).
    sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    var net_amount_owed = rs.getColumnValue(1);

    // Look up the number of purchases still under warranty...
    var number_purchases_under_warranty = 0;
    // Assuming a 1-year warranty...
    sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
    sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
    // Can't use CURRENT_DATE() because that changes. So assume that today is 
    // always June 15, 2019.
    sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::DATE)";
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    number_purchases_under_warranty = rs.getColumnValue(1);

    // Check whether need to keep some purchase history data; if not, then delete the data.
    if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
        // Delete the purchase history of this customer ...
        sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
        // ... and delete anything else that that should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;
    Copy

  3. 在删除任何数据之前，先显示表中的数据：

    SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 1           | Loved the milk chocolate! |
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+
    Copy

  4. #1 客户的保修期仍然有效。存储过程会删除他们发布的评论，但由于保修期的原因，会保留他们的购买记录：

    call delete_nonessential_customer_data(1);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 1 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+
    Copy

  5. #2 客户仍然欠款。存储过程会删除他们的评论，但保留他们的购买记录：

    call delete_nonessential_customer_data(2);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 2 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+
    Copy

  6. #3 客户没有欠款（也没有退款）。他们的保修期已过，因此存储过程会删除他们的评论和购买记录：

    call delete_nonessential_customer_data(3);
+-------------------------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
|-------------------------------------------------------------------------|
| Deleted all data, including financial and warranty data, for customer 3 |
+-------------------------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
+-------------+-------+-------+------------+---------------+
    Copy

在调用方权限和所有者权限存储过程中使用会话变量

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

  • 在调用存储过程之前设置会话变量，然后在存储过程中使用会话变量。

  • 在存储过程中设置会话变量，然后在从存储过程返回后使用该会话变量。

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

调用方权限存储过程

以下示例演示了调用方权限存储过程。

  1. 创建并加载表：

    create table sv_table (f float);
insert into sv_table (f) values (49), (51);
    Copy

  2. 设置会话变量：

    set SESSION_VAR1 = 50;
    Copy

  3. 创建一个调用方权限存储过程，该过程使用一个会话变量并设置另一个会话变量：

    create procedure session_var_user()
  returns float
  language javascript
  EXECUTE AS CALLER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  // Run a query using the first session variable
  stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;
    Copy

  4. 调用过程：

    CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+
    Copy

  5. 查看在存储过程中设置的会话变量值：

    SELECT $SESSION_VAR2;
+------------------------------+
| $SESSION_VAR2                |
|------------------------------|
| I was set inside the StProc. |
+------------------------------+
    Copy

备注

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

所有者权限存储过程

以下示例演示了所有者权限存储过程。

  1. 创建使用会话变量的所有者权限存储过程：

    create procedure cannot_use_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Run a query using the first session variable
  var stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  var rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;
    Copy

  2. 调用过程（应该会失败）：

    CALL cannot_use_session_vars();
    Copy

  3. 创建所有者权限存储过程，尝试设置会话变量：

    create procedure cannot_set_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  return 3.0;   // dummy value.
  $$
  ;
    Copy

  4. 调用过程（应该会失败）：

    CALL cannot_set_session_vars();
    Copy

故障排除

一般的故障排除技术是使用 JavaScript try/catch 块来捕获错误并显示错误信息。错误对象包含：

  • 错误代码。

  • 错误消息。

  • 错误状态。

  • 故障点的堆栈跟踪。

有关如何使用此信息的更多信息（包括示例），请参阅 使用 Try/Catch 捕获错误 （本主题内容）。

以下部分提供了其他建议，以帮助调试特定问题。

存储过程或 UDF 意外返回 NULL

原因:

存储过程/UDF 有一个参数，在存储过程/UDF 中，该参数用小写名称表示，但是 Snowflake 已自动将该名称转换为大写。

解决方案:

要么：

  • 在 JavaScript 代码中使用大写变量名，或

  • 在 SQL 代码中将变量名放在双引号内。

有关更多详细信息，请参阅 JavaScript 实参和返回值。

存储过程从未完成运行

原因:

JavaScript 代码中可能存在无限循环。

解决方案:

检查并修复任何无限循环。

错误：Failed: empty argument passed

原因:

存储过程可能包含“sqltext”，而它本应包含“sqlText”（前者全部小写；后者大小写混合）。

解决方案:

使用“sqlText”。

错误：JavaScript out of memory error: UDF thread memory limit exceeded

原因:

JavaScript 代码中可能存在无限循环。

解决方案:

检查并修复任何无限循环。尤其要确保在结果集用完时（即 resultSet.next() 返回 false 时）停止调用下一行。

语言: 中文