游标
为了处理SQL语句,存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标,存储过程可以控制上下文区域的变化。
语法格式
定义游标
CURSOR cursor_name [ BINARY ] [ NO SCROLL ] [ { WITH | WITHOUT } HOLD ] FOR query ;
通过已经创建的游标检索数据
FETCH [ direction { FROM | IN } ] cursor_name;
其中direction子句为可选参数。
NEXT | PRIOR | FIRST | LAST | ABSOLUTE count | RELATIVE count | count | ALL | FORWARD | FORWARD count | FORWARD ALL | BACKWARD | BACKWARD count | BACKWARD ALL
在不检索数据的情况下重新定位一个游标
MOVE的作用类似于FETCH命令,但只是重定位游标而不返回行。
MOVE [ direction [ FROM | IN ] ] cursor_name;
其中direction子句为可选参数。
NEXT | PRIOR | FIRST | LAST | ABSOLUTE count | RELATIVE count | count | ALL | FORWARD | FORWARD count | FORWARD ALL | BACKWARD | BACKWARD count | BACKWARD ALL
关闭游标,释放和一个游标关联的所有资源
CLOSE { cursor_name | ALL } ;
参数说明
cursor_name
将要创建、关闭的游标名。
BINARY
指明游标以二进制而不是文本格式返回数据。
NO SCROLL
声明游标检索数据行的方式。
- NO SCROLL:声明该游标不能用于以倒序的方式检索数据行。
- 未声明:根据执行计划的不同,自动判断该游标是否可以用于以倒序的方式检索数据行。
说明:
在cmdsql中,只支持添加 NO SCROLL关键字或者不添加滚动选项,不支持添加SCROLL关键字。
在存储过程中,支持添加NO SCROLL、SCROLL关键字或者不添加滚动选项。
显示游标中,只有申明为NO SCROLL的游标可以并行执行,建议将不需要用到倒序检索数据行方式的游标设置为NO SCROLL。
WITH HOLD | WITHOUT HOLD
声明当创建游标的事务结束后,游标是否能继续使用。
- WITH HOLD:声明该游标在创建它的事务结束后仍可继续使用。
- WITHOUT HOLD:声明该游标在创建它的事务之外不能再继续使用,此游标将在事务结束时被自动关闭。
- 如果不指定WITH HOLD或WITHOUT HOLD,默认行为是WITHOUT HOLD。
- 跨节点事务不支持WITH HOLD(例如在多DBnode部署openGauss中所创建的含有DDL的事务属于跨节点事务)。
query
使用SELECT或VALUES子句指定游标返回的行。
取值范围:SELECT或VALUES子句。
direction_clause
定义抓取数据的方向。
取值范围:
NEXT(缺省值)
从当前关联位置开始,抓取下一行。
PRIOR
从当前关联位置开始,抓取上一行。
FIRST
抓取查询的第一行(和ABSOLUTE 1相同)。
LAST
抓取查询的最后一行(和ABSOLUTE -1相同)。
ABSOLUTE count
抓取查询中第count行。
ABSOLUTE抓取不会比用相对位移移动到需要的数据行更快,因为下层的实现必须遍历所有中间的行。
count取值范围:有符号的整数
- count为正数,就从查询结果的第一行开始,抓取第count行。
- count为负数,就从查询结果末尾抓取第abs(count)行。
- count为0时,定位在第一行之前。
RELATIVE count
从当前关联位置开始,抓取随后或前面的第count行。
取值范围:有符号的整数
- count为正数就抓取当前关联位置之后的第count行。
- count为负数就抓取当前关联位置之前的第abs(count)行。
- 如果当前行没有数据的话,RELATIVE 0返回空。
count
抓取随后的count行(和FORWARD count一样)。
ALL
从当前关联位置开始,抓取所有剩余的行(和FORWARD ALL一样)。
FORWARD
抓取下一行(和NEXT一样)。
FORWARD count
从当前关联位置开始,抓取随后或前面的count行。
FORWARD ALL
从当前关联位置开始,抓取所有剩余行。
BACKWARD
从当前关联位置开始,抓取前面一行(和PRIOR一样) 。
BACKWARD count
从当前关联位置开始,抓取前面的count行(向后扫描)。
取值范围:有符号的整数
- count为正数就抓取当前关联位置之前的count行。
- count为负数就抓取当前关联位置之后的abs(count)行。
- 如果有数据的话,BACKWARD 0重新抓取当前行。
BACKWARD ALL
从当前关联位置开始,抓取所有前面的行(向后扫描) 。
{ FROM | IN } cursor_name
使用关键字FROM或IN指定游标名称。
取值范围:已创建的游标的名称。
ALL
关闭所有已打开的游标。
示例
假设存在表customer_t1 ,数据内容如下:
openGauss=# SELECT * FROM customer_t1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
3769 | | Grace | |
3769 | hello | | |
6885 | maps | Joes | | 2200
4321 | tpcds | Lily | | 3000
9527 | world | James | | 5000
(5 rows)
用一个游标读取一个表。
--开始一个事务。 openGauss=# START TRANSACTION; START TRANSACTION --建立一个名为cursor1的游标。 openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1; DECLARE CURSOR --抓取前3行到游标cursor1里。 openGauss=# FETCH FORWARD 3 FROM cursor1; c_customer_sk | c_customer_id | c_first_name | c_last_name | amount ---------------+---------------+--------------+-------------+-------- 3769 | | Grace | | 3769 | hello | | | 6885 | maps | Joes | | 2200 (3 rows) --关闭游标并提交事务。 openGauss=# CLOSE cursor1; CLOSE CURSOR --结束一个事务。 openGauss=# END; COMMIT
用一个游标读取VALUES子句中的内容。
--开始一个事务。 openGauss=# START TRANSACTION; START TRANSACTION --建立一个名为cursor2的游标。 openGauss=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1; DECLARE CURSOR --抓取前2行到游标cursor2里。 openGauss=# FETCH FORWARD 2 FROM cursor2; column1 | column2 ---------+--------- 0 | 3 1 | 2 (2 rows) --关闭游标并提交事务。 openGauss=# CLOSE cursor2; CLOSE CURSOR --结束一个事务。 openGauss=# END; COMMIT
WITH HOLD游标的使用。
--开启事务。 openGauss=# START TRANSACTION; --创建一个with hold游标。 openGauss=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer_t1; --抓取头2行到游标cursor1里。 openGauss=# FETCH FORWARD 2 FROM cursor1; c_customer_sk | c_customer_id | c_first_name | c_last_name | amount ---------------+---------------+--------------+-------------+-------- 3769 | | Grace | | 3769 | hello | | | (2 rows) --结束事务。 openGauss=# END; COMMIT --抓取下一行到游标cursor1里。 openGauss=# FETCH FORWARD 1 FROM cursor1; c_customer_sk | c_customer_id | c_first_name | c_last_name | amount ---------------+---------------+--------------+-------------+-------- 6885 | maps | Joes | | 2200 (1 row) --关闭游标。 openGauss=# CLOSE cursor1; CLOSE CURSOR
MOVE语句的使用。
--开始一个事务。 openGauss=# START TRANSACTION; START TRANSACTION --定义一个名为cursor1的游标。 openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1; DECLARE CURSOR --忽略游标cursor1的前3行。 openGauss=# MOVE FORWARD 1 FROM cursor1; MOVE 1 --抓取游标cursor1的前2行。 openGauss=# FETCH 2 FROM cursor1; c_customer_sk | c_customer_id | c_first_name | c_last_name | amount ---------------+---------------+--------------+-------------+-------- 3769 | hello | | | 6885 | maps | Joes | | 2200 (2 rows) --关闭游标。 openGauss=# CLOSE cursor1; CLOSE CURSOR --结束一个事务。 openGauss=# END; COMMIT