DECLARE
定义新游标。使用游标从大型查询的结果集中一次性检索几个行。
在提取游标的第一行时,会在领导节点上、内存中或磁盘上具体化整个结果集(如果需要)。由于将游标用于大型结果集可能会降低性能,因此建议使用备用方法(如果可能)。有关更多信息,请参阅 使用游标时的性能注意事项。
您必须在事务块中声明游标。对于每个会话,一次只能打开一个游标。
语法
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