DECLARE - Amazon Redshift

DECLARE

定义新游标。使用游标从大型查询的结果集中一次性检索几个行。

在提取游标的第一行时,会在领导节点上、内存中或磁盘上具体化整个结果集(如果需要)。由于将游标用于大型结果集可能会降低性能,因此建议使用备用方法(如果可能)。有关更多信息,请参阅 使用游标时的性能注意事项

您必须在事务块中声明游标。对于每个会话,一次只能打开一个游标。

有关更多信息,请参阅FETCHCLOSE

语法

DECLARE cursor_name CURSOR FOR query

参数

cursor_name

新游标的名称。

query

填充游标的 SELECT 语句。

DECLARE CURSOR 使用说明

如果您的客户端应用程序使用 ODBC 连接,并且您的查询创建的结果集太大,无法存储到内存中,则可使用光标将结果集流式传输到客户端应用程序。在使用游标时,会在领导节点上具体化整个结果集,随后您的客户端可按递增方式提取结果。

注意

若要在 ODBC 中为 Microsoft Windows 启用游标,请在您用于 Amazon Redshift 的 ODBC DSN 中启用使用声明/取回选项。建议使用 ODBC DSN 选项对话框中的 Cache Size 字段将多节点集群中的 ODBC 缓存大小设置为 4,000 或更大值,以最大程度地减少往返操作。在单节点集群上,将缓存大小设置为 1000。

由于使用游标可能会对性能产生负面影响,我们建议您尽可能地使用替代方法。有关更多信息,请参阅 使用游标时的性能注意事项

支持 Amazon Redshift 游标,但存在以下限制:

  • 对于每个会话,一次只能打开一个游标。

  • 游标必须在事务内部使用 (BEGIN … END)。

  • 所有游标的累计最大结果集大小受到集群节点类型的限制。如果您需要更大的结果集,可以调整为 XL 和 8XL 节点配置。

    有关更多信息,请参阅 游标约束

游标约束

在提取游标的第一行时,会在领导节点上具体化整个结果集。如果结果集无法存储到内存中,则会根据需要写入磁盘。为了保护领导节点的完整性,Amazon Redshift 会根据集群节点类型对所有游标结果集的大小强制实施约束。

下表显示每个集群节点类型的最大结果集总大小。最大结果集大小以 MB 为单位。

节点类型 每个集群的最大结果集大小 (MB)

RA3 16XL 多个节点

14400000

DC2 Large 单节点

8000

DC2 Large 多节点

192000

DC2 8XL 多节点

3200000

RA3 4XL 多个节点

3200000

RA3 XLPLUS 多节点

1000000

RA3 XLPLUS 单节点

64000

Amazon Redshift Serverless

150000

要查看某个集群的活动游标配置,请以超级用户身份查询 STV_CURSOR_CONFIGURATION 系统表。要查看活动光标的状态,请查询 STV_ACTIVE_CURSORS 系统表。用户只能看到自己游标所对应的行,而超级用户可以查看所有游标。

使用游标时的性能注意事项

由于在开始将结果返回给客户端之前,游标会在领导节点上具体化整个结果集,因此对超大型结果集使用游标时,会对性能产生负面影响。我们强烈建议不要对超大型结果集使用游标。在一些情况下,例如您的应用程序使用 ODBC 连接时,游标可能是唯一可行的解决方案。我们建议尽可能地利用下面这些备选方案:

  • 使用 UNLOAD 导出大型表。在使用 UNLOAD 时,计算节点将并行工作,从而将数据直接发送到 Amazon Simple Storage Service 上的数据文件。有关更多信息,请参阅 在 Amazon Redshift 中卸载数据

  • 在您的客户端应用程序中设置 JDBC 提取大小参数。如果您使用 JDBC 连接,并且遇到客户端内存不足错误,则可以通过设置 JDBC 提取大小参数,让您的客户端按较小的批次检索结果集。有关更多信息,请参阅 设置 JDBC 提取大小参数

DECLARE CURSOR 示例

以下示例声明一个名为 LOLLAPALOOZA 的游标,以便为 Lollapalooza 事件选择销售信息,然后使用该游标从结果集中提取行:

-- Begin a transaction begin; -- Declare a cursor declare lollapalooza cursor for select eventname, starttime, pricepaid/qtysold as costperticket, qtysold from sales, event where sales.eventid = event.eventid and eventname='Lollapalooza'; -- Fetch the first 5 rows in the cursor lollapalooza: fetch forward 5 from lollapalooza; eventname | starttime | costperticket | qtysold --------------+---------------------+---------------+--------- Lollapalooza | 2008-05-01 19:00:00 | 92.00000000 | 3 Lollapalooza | 2008-11-15 15:00:00 | 222.00000000 | 2 Lollapalooza | 2008-04-17 15:00:00 | 239.00000000 | 3 Lollapalooza | 2008-04-17 15:00:00 | 239.00000000 | 4 Lollapalooza | 2008-04-17 15:00:00 | 239.00000000 | 1 (5 rows) -- Fetch the next row: fetch next from lollapalooza; eventname | starttime | costperticket | qtysold --------------+---------------------+---------------+--------- Lollapalooza | 2008-10-06 14:00:00 | 114.00000000 | 2 -- Close the cursor and end the transaction: close lollapalooza; commit;

以下示例使用表中的所有结果遍历 refcursor:

CREATE TABLE tbl_1 (a int, b int); INSERT INTO tbl_1 values (1, 2),(3, 4); CREATE OR REPLACE PROCEDURE sp_cursor_loop() AS $$ DECLARE target record; curs1 cursor for select * from tbl_1; BEGIN OPEN curs1; LOOP fetch curs1 into target; exit when not found; RAISE INFO 'a %', target.a; END LOOP; CLOSE curs1; END; $$ LANGUAGE plpgsql; CALL sp_cursor_loop(); SELECT message from svl_stored_proc_messages where querytxt like 'CALL sp_cursor_loop()%'; message ---------- a 1 a 3