openGauss学习笔记-38 openGauss 高级数据管理-游标

Table of Contents
 openGauss学习笔记-38 openGauss 高级数据管理-游标
 38.1 语法格式
 38.2 参数说明
 38.3 示例
openGauss学习笔记-38 openGauss 高级数据管理-游标
为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

38.1 语法格式
定义游标

CURSOR cursor_name
    [ BINARY ]  [ NO SCROLL ]  [ { WITH | WITHOUT } HOLD ]
    FOR query ;
1.
2.
3.
通过已经创建的游标检索数据

FETCH [ direction { FROM | IN } ] cursor_name;
1.
其中direction子句为可选参数。

NEXT
   | PRIOR
   | FIRST
   | LAST
   | ABSOLUTE count
   | RELATIVE count
   | count
   | ALL
   | FORWARD
   | FORWARD count
   | FORWARD ALL
   | BACKWARD
   | BACKWARD count
   | BACKWARD ALL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
在不检索数据的情况下重新定位一个游标

MOVE的作用类似于FETCH命令，但只是重定位游标而不返回行。

MOVE [ direction [ FROM | IN ] ] cursor_name;
1.
其中direction子句为可选参数。

NEXT
   | PRIOR
   | FIRST
   | LAST
   | ABSOLUTE count
   | RELATIVE count
   | count
   | ALL
   | FORWARD
   | FORWARD count
   | FORWARD ALL
   | BACKWARD
   | BACKWARD count
   | BACKWARD ALL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
关闭游标，释放和一个游标关联的所有资源

CLOSE { cursor_name | ALL } ;
1.
38.2 参数说明
cursor_name

将要创建、关闭的游标名。

BINARY

指明游标以二进制而不是文本格式返回数据。

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

关闭所有已打开的游标。

38.3 示例
假设存在表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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
用一个游标读取一个表。

--开始一个事务。
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
用一个游标读取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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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