使用游标

您可以使用游标一次循环访问一行查询结果。

简介

要从查询结果中检索数据，请使用游标。您可以在 循环 中使用游标循环访问结果中的行。

要使用游标，请执行以下操作：

  1. 在 DECLARE 部分中， 声明游标。声明包括对游标的查询。

  2. 首次使用游标之前，请执行 OPEN 命令以 打开游标。这将执行查询并将结果加载到游标中。

  3. 执行 FETCH 命令以 :ref:` 提取一行或多行 <label-snowscript_cursors_fetch>` 并处理这些行。

  4. 结果完成或不再需要游标时，执行 CLOSE 命令以 关闭游标。

备注

使用 Snowflake Scripting 时，您还可以使用 RESULTSET 检索查询结果。有关游标和 RESULTSET 之间区别的信息，请参阅 了解游标和 RESULTSET 之间的差异。

为示例设置数据

本节中的示例使用以下数据：

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);
Copy

声明游标

您可以为 SELECT 语句或 RESULTSET 声明游标。

您可以在块的 DECLARE 部分或块的 BEGIN ...END 部分中声明游标：

  • 在 DECLARE 部分，使用 光标声明语法 中所述的语法。

    例如，要为查询声明游标，请执行以下操作：

    DECLARE
  ...
  c1 CURSOR FOR SELECT price FROM invoices;
    Copy

    要为 RESULTSET 声明游标，请执行以下操作：

    DECLARE
  ...
  res RESULTSET DEFAULT (SELECT price FROM invoices);
  c1 CURSOR FOR res;
    Copy

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

    BEGIN
  ...
  LET c1 CURSOR FOR SELECT price FROM invoices;
    Copy

在 SELECT 语句中，可以指定在打开游标时可绑定到变量的绑定参数（? 字符）。要将变量绑定到参数，请在 OPEN 命令的 USING 子句中指定变量。例如：

DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
END;
Copy

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

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
END;
$$
;
Copy
+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

打开游标

尽管声明游标的语句定义了与该游标关联的查询，但在通过执行 OPEN 命令打开游标之前，不会执行查询。例如：

OPEN c1;
Copy

备注

  • 在 FOR 循环中使用游标时，无需显式打开游标。

  • 如果为 RESULTSET 对象声明游标，则在将该对象与查询关联时执行查询。在这种情况下，打开游标不会导致再次执行查询。

如果查询包含任何绑定参数（? 字符），请添加 USING 子句以指定要绑定到这些参数的变量列表。例如：

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);
Copy

打开游标将执行查询，将指定的行检索到游标中，并设置指向第一行的内部指针。您可以使用 FETCH 命令通过游标 提取（读取）各行。

与任何 SQL 查询一样，如果查询定义在最外层不包含 ORDER BY，则结果集没有已定义的顺序。创建游标的结果集时，行的顺序将保持不变，直到游标关闭。如果再次声明或打开游标，则行的顺序可能会有所不同。同样，如果在再次打开游标前关闭游标并更新基础表，则结果集也会改变。

使用游标提取数据

使用 FETCH 命令从结果集中检索当前行，并推进内部当前行指针以指向结果集中的下一行。

在 INTO 子句中，指定应用于保存行中值的变量。

例如：

FETCH c1 INTO var_for_column_value;
Copy

如果变量的数量与游标声明的 SELECT 子句中的表达式的数量不匹配，Snowflake 会尝试按位置将变量数与列数进行匹配：

  • 如果变量数多于列数，则 Snowflake 会将其余变量保留为未设置。

  • 如果列数多于变量数，则 Snowflake 将忽略其余列。

您执行的每个后续 FETCH 命令都会获取下一行，直到提取最后一行。如果尝试在最后一行之后 FETCH 一行，则会得到 NULL 值。

执行查询时，RESULTSET 或游标不一定缓存结果集的所有行。FETCH 操作可能会遇到延迟。

使用游标检索 GEOGRAPHY 值

如果结果包含 GEOGRAPHY 类型的列，则该列中的值的类型是 OBJECT，而不是 GEOGRAPHY。这意味着，您不能直接将此值传递给接受 GEOGRAPHY 对象作为输入的 地理空间函数 ：

DECLARE
  geohash_value VARCHAR;
BEGIN
  LET res RESULTSET := (SELECT TO_GEOGRAPHY('POINT(1 1)') AS GEOGRAPHY_VALUE);
  LET cur CURSOR FOR res;
  FOR row_variable IN cur DO
    geohash_value := ST_GEOHASH(row_variable.geography_value);
  END FOR;
  RETURN geohash_value;
END;
Copy
001044 (42P13): Uncaught exception of type 'EXPRESSION_ERROR' on line 7 at position 21 : SQL compilation error: ...
Invalid argument types for function 'ST_GEOHASH': (OBJECT)
Copy

要解决此问题，请将列值转换为 GEOGRAPHY 类型：

geohash_value := ST_GEOHASH(TO_GEOGRAPHY(row_variable.geography_value));
Copy

为游标返回表

如果需要从游标返回数据表，可以将游标传递给 RESULTSET_FROM_CURSOR(cursor)，然后再将其传递给 TABLE(...)。

以下块从游标返回数据表：

DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;
Copy

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

EXECUTE IMMEDIATE $$
DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;
$$
;
Copy
+----+-------+
| ID | PRICE |
|----+-------|
|  1 | 11.11 |
|  2 | 22.22 |
+----+-------+

即使您已经使用游标 提取行，但 RESULTSET_FROM_CURSOR 仍会返回包含所有行（而不仅仅是从内部行指针开始的行）的 RESULTSET。

如上所示，该示例提取第一行，并将内部行指针设置为第二行。RESULTSET_FROM_CURSOR 返回包含两行（而不仅仅是第二行）的 RESULTSET。

关闭游标

完成结果集后，通过执行 CLOSE 命令关闭游标。例如：

CLOSE c1;
Copy

备注

在 FOR 循环中使用游标时，无需显式关闭游标。

不能在已关闭的游标上执行 FETCH 命令。

此外，关闭游标后，当前行指针将变为无效。如果再次打开游标，指针将指向新结果集中的第一行。

使用游标的示例

此示例使用您在 为示例设置数据 中设置的数据。

以下是一个存储过程，它使用游标读取两行，并对这两行中的价格求和：

DECLARE
  row_price FLOAT;
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  row_price := 0.0;
  total_price := 0.0;
  OPEN c1;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  CLOSE c1;
  RETURN total_price;
END;
Copy

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

EXECUTE IMMEDIATE $$
DECLARE
    row_price FLOAT;
    total_price FLOAT;
    c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
    row_price := 0.0;
    total_price := 0.0;
    OPEN c1;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    CLOSE c1;
    RETURN total_price;
END;
$$
;
Copy
+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

FOR 循环 文档中包含一个使用循环的示例。

游标问题疑难解答

下一节介绍有关游标的常见问题，并指出每种情况下可能的原因和解决方案。

症状：游标每隔一行检索一次，而不是每一行检索一次

  • 可能的原因： 您可能已经在 FOR <record> IN <cursor> 循环内执行了 FETCH。FOR 循环穿过游标时会自动提取下一行数据。如果您在循环中再次执行提取操作，会间隔一行获得一次数据。

  • 可能的解决方案： 删除 FOR 循环内任何不需要的 FETCH 命令。

症状：FETCH 命令检索出意外的 NULL 值

  • 可能的原因： 您可能已经在 FOR <record> IN <cursor> 循环内执行了 FETCH。FOR 循环穿过游标时会自动提取下一行数据。如果您在循环中再次执行提取操作，会间隔一行获得一次数据。如果行数为奇数，则最后一次提取操作将尝试提取最后一行之外的行，并且值将为 NULL。

  • 可能的解决方案： 删除 FOR 循环内任何不需要的 FETCH 命令。

语言: 中文