使用 RESULTSETs¶

本主题介绍如何在 Snowflake Scripting 中使用 RESULTSET。

简介¶

在 Snowflake Scripting 中， RESULTSET 是一种指向查询结果集的 SQL 数据类型。

由于 RESULTSET 只是指向结果的指针，因此必须执行下列操作之一，才能通过 RESULTSET 访问结果：

使用 TABLE(...) 语法，以表的形式检索结果。
使用 :doc:` 游标 <cursors>` 循环访问 RESULTSET。

下文列出了这两种情况的示例。

了解游标和 RESULTSET 之间的差异¶

RESULTSET 和 :doc:` 游标 <cursors>` 都可以访问查询的结果集。但是，这些对象在以下方面有所不同：

执行查询的时间点。
- 对于游标，基于游标执行 OPEN 命令时就会执行查询。
- 对于 RESULTSET，当您将查询分配给 RESULTSET（在 DECLARE 部分或 BEGIN ...END 块中）时，将执行该查询。
是否支持在 OPEN 命令中绑定。
- 声明游标时，可以指定绑定参数（? 字符）。之后，在执行 OPEN 命令时，可以在 USING 子句中将变量与这些参数绑定。
- RESULTSET 不支持 OPEN 命令。但是，您可在返回结果集之前绑定 SQL 命令中的变量。

一般来说，当您希望返回包含查询结果集的表时，使用 RESULTSET 会更简单。但是，您也可以从带有游标的 Snowflake Scripting 块返回一个表。为此，您可将游标传递给 RESULTSET_FROM_CURSOR(cursor) 以返回 RESULTSET，并将该 RESULTSET 传递给 TABLE(...)。请参阅为游标返回表。

声明 RESULTSET¶

您可以在块的 DECLARE 部分中声明 RESULTSET,也可以在块的 BEGIN ...END 部分进行声明。

在 DECLARE 部分，使用 RESULTSET 声明语法中所述的语法。例如：

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);

Copy

在 BEGIN ...END 块中，使用 RESULTSET 赋值语法中描述的语法。例如：

BEGIN
  ...
  LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);

Copy

将查询分配给已声明的 RESULTSET¶

若要将查询结果分配给已声明的 RESULTSET，请使用以下语法：

<resultset_name> := [ ASYNC ] ( <query> ) ;

Copy

其中：

resultset_name
RESULTSET 的名称。

该名称在当前范围内必须是唯一的。

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

ASYNC
作为异步子作业来运行查询。

query
要分配给 RESULTSET 的查询。

要将查询分配给 RESULTSET，请执行以下操作：

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

要将查询分配给 RESULTSET 并作为异步子作业运行查询，请执行以下操作：

DECLARE
  res RESULTSET;
BEGIN
  res := ASYNC (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

如果为查询动态构建 SQL 字符串，请将 query 设置为 (EXECUTE IMMEDIATE string_of_sql)。例如：

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

虽然您可以为 RESULTSET 将 query 设置为 EXECUTE IMMEDIATE 语句，但不能对游标执行此操作。

使用 RESULTSET¶

对 RESULTSET 的查询会在对象与该查询关联时执行。例如：

声明 RESULTSET 并将 DEFAULT 子句设置为查询时，查询将在该时间点执行。
使用 := 运算符将查询分配给 RESULTSET 时，查询将在该时间点执行。

备注

由于 RESULTSET 指向查询的结果集（但并不包含查询的结果集），因此 RESULTSET 只有在查询结果被缓存时才有效（通常为 24 小时）。有关查询结果缓存的详细信息，请参阅使用持久化查询结果。

执行查询后，便可以使用游标来访问结果。还可以通过存储过程以表的形式返回结果。

使用游标访问 RESULTSET 中的数据
以表的形式返回 RESULTSET

使用游标访问 RESULTSET 中的数据¶

要使用游标访问 RESULTSET 中的数据，请在对象上 :ref:` 声明游标 <label-snowscript_cursors_declare>`。例如：

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;

Copy

在 RESULTSET 上声明游标时，该游标可访问 RESULTSET 中的现有数据。在游标上执行 OPEN 命令并不会再次对 RESULTSET 执行查询。

然后就可以打开游标，并使用游标提取数据。

备注

如果结果包含 GEOGRAPHY 值，则必须先将这些值转换为 GEOGRAPHY 类型，然后才能将其传递给需要 GEOGRAPHY 输入值的任何函数。请参阅使用游标检索 GEOGRAPHY 值。

以表的形式返回 RESULTSET¶

如果要返回 RESULTSET 指向的结果，请将 RESULTSET 传递给 TABLE(...)。例如：

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...

Copy

这类似于 TABLE(...) 与 :doc:` 表函数 </sql-reference/functions-table>` 配合使用的方式（如 RESULT_SCAN）。

如示例所示，如果编写能够返回表的存储过程，则必须将该存储过程声明为返回表。

备注

目前，只有 RETURN 语句支持 TABLE(resultset_name) 语法。

即便使用游标从 RESULTSET 中提取行到，由 TABLE(resultset_name) 返回的表仍然包含所有行（而不仅仅是从游标内部行指针开始的行）。

RESULTSET 数据类型的限制¶

虽然 RESULTSET 是一种数据类型，但 Snowflake 尚不支持以下操作：

声明 RESULTSET 类型的列。
声明 RESULTSET 类型的参数。
将存储过程的返回类型声明为 RESULTSET。

Snowflake 仅在 Snowflake Scripting 内部支持 RESULTSET。

此外，您不能直接将 RESULTSET 用作表。例如，以下操作无效：

SELECT * FROM my_result_set;

Copy

使用 RESULTSET 的示例¶

以下各节提供了使用 RESULTSET 的示例：

为示例设置数据
示例：从存储过程返回表
示例：动态构造 SQL 语句
示例：不使用 DEFAULT 子句声明 RESULTSET 变量
示例：将 CURSOR 与 RESULTSET 配合使用
使用 RESULTSET 的其他示例

有关使用 ASYNC 关键字将为 RESULTSETs 指定的查询作为异步子作业运行的示例，请参阅使用异步子作业的示例。

为示例设置数据¶

下面的许多示例都使用了如下所示的表和数据：

CREATE OR REPLACE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
  (1, 'row1'),
  (2, 'row2');

Copy

示例：从存储过程返回表¶

以下代码演示了如何声明 RESULTSET 并返回 RESULTSET 指向的结果。CREATE PROCEDURE 命令中的 RETURNS 子句声明了存储过程返回一个表，该表包含一个 INTEGER 类型的列。

块中的 RETURN 语句使用 TABLE(...) 语法将结果作为表返回。

创建存储过程：

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  BEGIN
    RETURN TABLE(res);
  END;

Copy

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

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET default (SELECT a FROM t001 ORDER BY a);
  BEGIN
      RETURN TABLE(res);
  END;
$$;

Copy

调用存储过程：

CALL test_sp();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

您可以使用竖线运算符 (->>) 来处理存储过程调用的结果：

CALL test_sp()
  ->> SELECT *
        FROM $1
        WHERE a > 1;

Copy

+---+
| A |
|---|
| 2 |
+---+

在调用存储过程后，您也可以使用 RESULT_SCAN 函数来处理结果：

CALL test_sp();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  WHERE a < 2;

Copy

+---+
| A |
|---|
| 1 |
+---+

示例：动态构造 SQL 语句¶

您可以动态构造 SQL。下面是一个示例，它执行的查询与上述存储过程相同，但使用的是动态构造的 SQL 语句：

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
  RETURNS TABLE(a INTEGER)
  LANGUAGE SQL
AS
DECLARE
  res RESULTSET;
  query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    RETURN TABLE(res);
  END
$$
;

Copy

若要运行该示例，请调用存储过程并传入表名称：

CALL test_sp_dynamic('t001');

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

示例：不使用 DEFAULT 子句声明 RESULTSET 变量¶

以下代码演示了如何在不使用 DEFAULT 子句的情况下声明 RESULTSET（即不将查询与 RESULTSET 关联），随后再将 RESULTSET 与查询关联。

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (SELECT a FROM t001 ORDER BY a);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET;
  BEGIN
      res := (SELECT a FROM t001 ORDER BY a);
      RETURN TABLE(res);
  END;
$$;

Copy

若要运行该示例，请调用存储过程：

CALL test_sp_02();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

示例：将 CURSOR 与 RESULTSET 配合使用¶

以下代码演示了如何使用游标循环访问 RESULTSET 中的行：

创建存储过程：

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

DECLARE
  accumulator INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  cur1 CURSOR FOR res1;
BEGIN
  FOR row_variable IN cur1 DO
    accumulator := accumulator + row_variable.a;
  END FOR;
  RETURN accumulator::VARCHAR;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
        accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator;
  END;
$$;

Copy

调用存储过程，结果会将表 (1 + 2) 中 a 的值相加：

CALL test_sp_03();

Copy

+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

使用 RESULTSET 的其他示例¶

以下是使用 RESULTSET 的其他示例：

使用基于 RESULTSET 的 FOR 循环

此示例演示了如何使用在 RESULTSET 上循环访问的 FOR 循环。
为游标返回表

此示例演示了如何使用游标返回 RESULTSET 中的数据表。
使用用户输入更新表数据

此示例演示了如何根据用户输入，使用绑定变量来更新表中的数据。它使用带有条件逻辑的 FOR 循环来循环访问 RESULTSET 中的行。
筛选和收集数据

此示例演示了如何使用 RESULTSET 收集数据，并将该数据插入表中以跟踪历史趋势。