我的书签
添加书签
移除书签游标概述
游标一般和存储过程结合使用。
游标定义后，要先打开才能进行使用。
使用游标需要先关闭优化，SET OPTIMIZER = OFF;
DECLARE CURSOR
语法格式
DECLARE CURSOR的语法如下：
CURSOR是定义绑定查询语句的游标，可以直接打开。
REFCURSOR是定义无绑定语句的游标，打开时候需要绑定查询语句。
cursor_param_def_list
定义带参数的游标，可以在打开的时候，传入参数。
for_or_is:
FOR和IS都可以用于绑定游标查询语句（为了兼容ORACLE语法）
参数说明
- cursor_name
- opt_cursor_param_def_clause
定义游标参数。
- select_stmt
游标绑定查询语句。
OPEN/CLOSE CURSOR
打开游标时候，会执行与游标绑定的查询语句，并将结果集存储入游标中。
关闭游标，会删除游标内数据和游标本身。
语法格式
OPEN/CLOSE CURSOR的语法如下：
OPEN打开游标
CLOSE关闭游标
cursor_param_assign_list:
给游标参数赋值支持两种形式，指定变量名赋值和顺序赋值。
参数说明
- cursor_name
游标名称。
为游标参数赋值。
- select_stmt
为refcursor游标绑定查询语句，并打开。
语法示例
--打开一个绑定查询语句游标
>OPEN cur1;
--打开一个带参数游标/用顺序赋值，打开带参游标
>OPEN cur2(a=3, t=’1995-01-01’);
>OPEN cur2(3, ’1995-01-01’);
--打开一个未绑定查询语句的游标
> OPEN curref CURSOR FOR SELECT * FROM test;
--关闭一个游标
> CLOSE curref;
FETCH/MOVE CURSOR
游标初始的指针位置在第一条数据之前，指针最多可以移动到最后一条数据之后。
语法格式
FETCH/MOVE CURSOR的语法如下：
FETCH游标可以将结果显示在客户端，也可以将结果存入变量。
MOVE只移动游标指针指向的位置，不取出结果。
fetch_args:
opt_fetch_into:
FETCH 根据游标参数，取出游标对应的行，并且移动游标指针位置。
参数说明
- fetch_args
游标行为参数：可以为“空”或者以下之一
- NEXT
取出下一行。如果省略direction，这将是默认值。
- PRIOR
取出当前位置之前的一行。
- FIRST
- LAST
取出该查询的最后一行（和ABSOLUTE -1相同）。
- ABSOLUTE count
取出该查询的第count个行，如果count为负则是从尾部开始取出 第abs(count)个行。如果 count超出范围，将定位在第一行 之前或者最后一行之后。特别地，ABSOLUTE 0 会定位在第一行之前。
- RELATIVE count
取出第count个后继行，如果 count为负 则是取出前面的第abs(count)个行。 RELATIVE 0重新取出当前行（如果有）。
- count
取出接下来的count行（和 FORWARD count相同）。
- ALL
取出所有剩余的行（和FORWARD ALL相同）。
取出下一行（和NEXT相同）。
- FORWARD count
取出接下来的count行。 FORWARD 0重新取出当前行。
- FORWARD ALL
取出所有剩下的行。
- BACKWARD
取出当前行前面的一行（和PRIOR相同）。
- BACKWARD count
- BACKWARD ALL
取出所有当前位置之前的行（反向扫描）。
- count
count 是一个可能带有符号的整数常量，它决定要取得的位置或者行数。对于 FORWARD和BACKWARD情况，指定一个负的 count等效于改变 FORWARDhe BACKWARD的意义。opt_cursor_into
- select_stmt
为refcursor游标绑定查询语句，并打开。
- opt_cursor_into
在存储过程中，可以使用into将结果存入变量
语法示例
--从游标cur1中取出3条数据
> FETCH FORWARD 3 IN cur1
id | name | birth | marry
+——+—————+——————+———-+
1 | xiaoming | 1996-09-18 | false
2 | zhangsan | 1993-07-13 | true
3 | wangli | 1985-12-03 | true
(3 rows)
--取出前一条数据
> FETCH PRIOR FROM cur1;
id | name | birth | marry
+——+—————+——————+———-+
2 | zhangsan | 1993-07-13 | true
(1 row)
--把cur1指针位置移动到第一条数据的位置
> MOVE FIRST IN cur1;
--取出当前位置数据
> FETCH RELATIVE 0 IN cur1;
id | name | birth | marry
+——+—————+——————+———-+
1 | xiaoming | 1996-09-18 | false
(1 row) |
SHOW CURSORS
用于查看游标信息。
语法格式
SHOW CURSORS的语法如下：
SHOW CURSORS查看全部游标信息
SHOW CURSORS cursor_name查看指定游标信息
参数说明
- cursor_name
要单独显示游标的名称
--定义游标cur1
> DECLARE cur1 CURSOR FOR SELECT * FROM test;
--定义游标cur2
> DECLARE cur2 CURSOR(a INT) FOR SELECT * FROM test where id>a;
--显示所有游标
> SHOW CURSORS;
cursorName | cursorID | cursorQuery
+——————+—————+————————————————-+
cur1 | 1 | SELECT * FROM test cur2 |
2 | SELECT * FROM test WHERE id > a (1 row)
--单独显示cur1
> SHOW CURSORS cur1;
cursorName | cursorID | cursorQuery
+——————+—————+——————————+
cur1 | 1 | SELECT * FROM test
(1 row) |
UPDATE/DELETE … WHERE CURRENT OF CURSOR
使用游标当前指针所指向位置，更新表的数据，或者删除表的数据。
使用where current of的更新和删除操作，不支持关联的查询语句是多表关联、无表关联或含有group by、distinct的游标，因为这种情况下根据游标的指针无法确认为表中的某一行数据。
语法格式
UPDAT/DELETE … WHERE CURRENT OF CURSOR的语法如下：
UPDATE:
DELETE:
where_current_of:
参数说明
- cursor_name
更新表的游标名
其它参数详见UPDATE和DELETE语法。
语法示例
--查看test表
> SELECT * FROM test; id | name | birth | marry
+——+—————+——————+———-+
1 | xiaoming | 1996-09-18 | false
2 | zhangsan | 1993-07-13 | true
3 | wangli | 1985-12-03 | true
(3 rows)
--定义并且移动游标位置
> DECLARE cur1 CURSOR FOR SELECT * FROM test;
> OPEN cur1;
> MOVE NEXT IN cur1;
--使用游标更新当前表
> UPDATE test SET name=’haha’ WHERE CURRENT OF cur1;
--移动游标
> MOVE LAST IN cur1;
--使用游标删除表数据
> DELETE FROM test WHERE CURRENT OF cur1;
--查看test表
> SELECT * FROM TEST; id | name | birth | marry
+——+—————+——————+———-+
1 | haha | 1996-09-18 | false
(2 rows)
