使用 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

预览版功能 – 开放

适用于所有账户。

功能支持的区域

此功能在中华人民共和国不可用。

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

查询可以是任何有效的 SQL 语句，包括 SELECT 语句和 DML 语句，例如 INSERT 或 UPDATE。

当省略此关键字时，存储过程将按顺序运行子作业，每个子作业都要等待正在运行的子作业完成后才能开始。

您可以使用此关键字并发运行多个子作业，这样可以提高效率并减少总体运行时间。

您可以使用 AWAIT 和 CANCEL 语句管理 RESULTSET 的异步子作业。

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 的其他示例

为示例设置数据¶

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

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 代码中使用 SnowSQL、Classic Console 或者 execute_stream 或 execute_string 方法，请改用本示例（请参阅在 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 |
+---+

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

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  ORDER BY 1;

Copy

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

示例：动态构造 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          |
+------------+

示例：运行并发查询表的子作业¶

以下代码显示如何使用 ASYNC 关键字运行多个并发查询表的子作业。

此示例使用下表中的数据：

CREATE OR REPLACE TABLE orders_q1_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q1_2024 VALUES (1, 500.00);
INSERT INTO orders_q1_2024 VALUES (2, 225.00);
INSERT INTO orders_q1_2024 VALUES (3, 725.00);
INSERT INTO orders_q1_2024 VALUES (4, 150.00);
INSERT INTO orders_q1_2024 VALUES (5, 900.00);

CREATE OR REPLACE TABLE orders_q2_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q2_2024 VALUES (1, 100.00);
INSERT INTO orders_q2_2024 VALUES (2, 645.00);
INSERT INTO orders_q2_2024 VALUES (3, 275.00);
INSERT INTO orders_q2_2024 VALUES (4, 800.00);
INSERT INTO orders_q2_2024 VALUES (5, 250.00);

Copy

以下存储程序执行以下操作：

在两个表中查询所有行的 order_amount 值，并将结果返回给不同的 RESULTSETs（每个表一个）。
使用 ASYNC 关键字指定查询作为并发子作业来运行。
为每个 RESULTSET 执行 AWAIT 语句，以便程序等待查询完成之后再继续。RESULTSET 的查询结果无法访问，直到为 RESULTSET 运行 AWAIT 为止。
使用游标计算每个表的 order_amount 行的总和。
添加表的总计并返回值。

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  accumulator1 INTEGER DEFAULT 0;
  accumulator2 INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
  res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
BEGIN
  AWAIT res1;
  LET cur1 CURSOR FOR res1;
  OPEN cur1;
  AWAIT res2;
  LET cur2 CURSOR FOR res2;
  OPEN cur2;
  FOR row_variable IN cur1 DO
      accumulator1 := accumulator1 + row_variable.order_amount;
  END FOR;
  FOR row_variable IN cur2 DO
      accumulator2 := accumulator2 + row_variable.order_amount;
  END FOR;
  RETURN accumulator1 + accumulator2;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator1 INTEGER DEFAULT 0;
    accumulator2 INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
    res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
  BEGIN
    AWAIT res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;
    AWAIT res2;
    LET cur2 CURSOR FOR res2;
    OPEN cur2;
    FOR row_variable IN cur1 DO
        accumulator1 := accumulator1 + row_variable.order_amount;
    END FOR;
    FOR row_variable IN cur2 DO
        accumulator2 := accumulator2 + row_variable.order_amount;
    END FOR;
    RETURN accumulator1 + accumulator2;
  END;
$$;

Copy

调用存储过程：

CALL test_sp_async_child_jobs_query();

Copy

+--------------------------------+
| TEST_SP_ASYNC_CHILD_JOBS_QUERY |
|--------------------------------|
|                           4570 |
+--------------------------------+

示例：运行子作业，并发地将行插入表中¶

以下代码显示如何使用 ASYNC 关键字运行多个子作业，并发地将行插入到表中。

以下存储程序执行以下操作：

如果 orders_q3_2024 表不存在，则创建该表。
创建两个 RESULTSETs，即 insert_1 和 insert_2，保存插入表中的结果。存储程序实参指定插入表中的值。
指定使用 ASYNC 关键字将插入作为并发子作业来运行。
为每个 RESULTSET 执行 AWAIT 语句，以便程序等待插入完成之后再继续。RESULTSET 的结果无法访问，直到为 RESULTSET 运行 AWAIT 为止。
创建一个新的 RESULTSET res，保存 orders_q3_2024 表的查询结果。
返回查询的结果。

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
$$
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
$$;

Copy

调用存储过程：

CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);

Copy

+----------+--------------+
| ORDER_ID | ORDER_AMOUNT |
|----------+--------------|
|        1 |       325.00 |
|        2 |       241.00 |
+----------+--------------+

使用 RESULTSET 的其他示例¶

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

使用基于 RESULTSET 的 FOR 循环

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

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

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

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