DDL语句 - CURSOR - 《ZNBase 数据库技术文档》

CURSOR
- 游标概述
DECLARE CURSOR
OPEN/CLOSE CURSOR
FETCH/MOVE CURSOR
SHOW CURSORS
UPDATE/DELETE … WHERE CURRENT OF CURSOR

CURSOR

游标概述

和一次执行整个查询不同，可以建立一个游标来封装该查询，并且接着一次读取该查询结果的一些行。这样做的原因之一是在结果中包含大量行时避免内存不足。

游标一般和存储过程结合使用。

游标定义后，要先打开才能进行使用。

使用游标需要先关闭优化，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

游标名称。

opt_cursor_param_assign_clause

为游标参数赋值。

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

游标行为参数：可以为“空”或者以下之一

取出下一行。如果省略direction，这将是默认值。

PRIOR

取出当前位置之前的一行。

FIRST

取出该查询的第一行（和ABSOLUTE 1相同）。

LAST

取出该查询的最后一行（和ABSOLUTE -1相同）。

ABSOLUTE count

取出该查询的第count个行，如果count为负则是从尾部开始取出第abs(count)个行。如果 count超出范围，将定位在第一行之前或者最后一行之后。特别地，ABSOLUTE 0 会定位在第一行之前。

RELATIVE count

取出第count个后继行，如果 count为负则是取出前面的第abs(count)个行。 RELATIVE 0重新取出当前行（如果有）。

count

取出接下来的count行（和 FORWARD count相同）。

取出所有剩余的行（和FORWARD ALL相同）。

FORWARD

取出下一行（和NEXT相同）。

FORWARD count

取出接下来的count行。 FORWARD 0重新取出当前行。

FORWARD ALL

取出所有剩下的行。

BACKWARD

取出当前行前面的一行（和PRIOR相同）。

BACKWARD count

取出前面的count行（反向扫描）。 BACKWARD 0会重新取出当前行。

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 | zhangsan | 1993-07-13 | true

(2 rows)