数据仓库服务 GAUSSDB(DWS)-CURSOR:示例

时间：2023-11-10 17:34:09

示例

创建名称为cursor1的游标：

1	CURSOR cursor1 FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

创建名称为cursor2的游标：

1	CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;

WITH HOLD游标的使用示例：

开启事务：
1

START TRANSACTION;

创建一个with hold游标：

1	DECLARE cursor3 CURSOR WITH HOLD FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

抓取头2行到游标cursor3里：

FETCH FORWARD 2 FROM cursor3;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name   | ca_street_type  | ca_suite_number |     ca_city     |    ca_county    | ca_state |   ca_zip   |  ca_country   | ca_gmt_offset |   ca_location_type   
---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+-----------------+----------+------------+---------------+---------------+----------------------
             1 | AAAAAAAABAAAAAAA | 18               | Jackson            | Parkway         | Suite 280       | Fairfield       | Maricopa County | AZ       | 86192      | United States |         -7.00 | condo               
             2 | AAAAAAAACAAAAAAA | 362              | Washington 6th     | RD              | Suite 80        | Fairview        | Taos County     | NM       | 85709      | United States |         -7.00 | condo               
(2 rows)

结束事务：
1

END;

抓取下一行到游标cursor3里：

FETCH FORWARD 1 FROM cursor3;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name   | ca_street_type  | ca_suite_number |     ca_city     |    ca_county    | ca_state |   ca_zip   |  ca_country   | ca_gmt_offset |   ca_location_type   
---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+-----------------+----------+------------+---------------+---------------+----------------------
             3 | AAAAAAAADAAAAAAA | 585              | Dogwood Washington | Circle          | Suite Q         | Pleasant Valley | York County     | PA       | 12477      | United States |         -5.00 | single family       
(1 row)