DECLARE¶
说明¶
定义一个游标
参数¶
cursor_name
游标名称。
TRANSACTIONAL
事务级游标。若指定为事务级，则该游标在事务结束时会自动关闭和释放。与指定 WITHOUT HOLD 等效。
WITHOUT HOLD
事务级游标。若指定为事务级，则该游标在事务结束时会自动关闭和释放。与指定 TRANSACTIONAL 等效。
WITH HOLD
非事务级游标。若指定为非事务级，则该游标在事务结束时不会自动关闭和释放，可以跨事务使用。与不指定 TRANSACTIONAL 等效。
注解
- 若不指定 TRANSACTIONAL、WITHOUT HOLD、WITH HOLD，则游标为非事务级。
- TRANSACTIONAL 与 WITHOUT HOLD、WITH HOLD 不能同时指定。
BINARY
表示二进制游标，游标将以二进制而不是文本格式获取数据。
SCROLL
表明FETCH 操作可以正向、反向获取数据。
INSENSITIVE
表明从游标检索出来的数据不应该被其他客户端连接或游标的更新动作影响。
INSENSITIVE SCROLL
INSENSITIVE与SCROLL的结合。
注解
- 若不指定 SCROLL，则游标不可滚动，不可以反向获取数据。
- 若不指定 INSENSITIVE，则游标为敏感游标，游标检索出来的数据会被其他客户端连接或游标的更新动作影响。
plan_name
曾经申明过的查询计划的名字（PREPARE）。系统允许以查询计划名代替 SELECT 语句来申明一个游标。
select
一个SELECT查询语句，它提供由游标控制的行。请参考 SELECT 语法。
READ ONLY
表明游标将用于只读模式。
UPDATE
表明游标将被用于更新表。如果后面不跟列名，则表示所有列都可更新。
Column
可以被更新的列。现在系统仅在语法上提供了支持，对于列级更新上的限制还未实现。因此 FOR UPDATE 与 FOR UPDATE OF ... 的效果是一样的。
注解
DECLARE 是系统扩展，不仅可以用于嵌入式 SQL（E-SQL）中，也可以用于交互式 SQL 中。
DECLARE 允许用户创建游标，用于在一个大的查询里面检索少数几行数据（使用 FETCH ）。游标可以既可以返回文本也可以返回二进制格式。
当不在一个事务中时， TRANSACTIONAL 关键字将导致定义游标出错。
当使用查询计划名来申明一个游标时，如果 DEALLOCATE 这个查询计划，这个相关的游标将同时被 DEALLOCATE 。如果此时游标仍然处于打开状态，将出错，显示无法 DEALLOCATE 这个查询计划。另外，每个查询计划只能与一个游标相关联。如果该查询计划已经关联了一个游标，再次使用它申明一个游标时将出错。
如果游标所依赖的表结构发生变化，那么在做 OPEN 、FETCH 的时候会导致出错。
当没有申明 FOR READ ONLY 和 FOR UPDATE 时，默认将是可更新的。
所有在事务中打开的游标当事务提交/回滚时将自动关闭。
INSENSITIVE 和UPDATE 不能同时出现在declare语句中
示例¶
示例1: 定义游标(不可反向移动)
-- 清理环境
DEALLOCATE cur1;
DROP TABLE tab1 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab1 (a INT);
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
SELECT * FROM tab1 ORDER BY a;
A(int) |
------------
1 |
------------
2 |
------------
3 |
总数目：3
-- 定义游标(不可反向移动)
DECLARE cur1 CURSOR FOR SELECT * FROM tab1 ORDER BY a;/
-- 打开游标
OPEN cur1;
-- 获取数据
FETCH ALL FROM cur1;
A(int) |
------------
1 |
------------
2 |
------------
3 |
总数目：3
-- 反向获取数据，失败
FETCH BACKWARD ALL FROM cur1;
ERROR, 本游标不能后向执行
-- 关闭游标
CLOSE cur1;
-- 删除游标
DEALLOCATE cur1;
-- 删除表
DROP TABLE tab1;
示例2: 定义事务级游标
-- 清理环境
DEALLOCATE cur2;
DROP TABLE tab2 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab2 (a INT);
INSERT INTO tab2 VALUES (1);
INSERT INTO tab2 VALUES (2);
INSERT INTO tab2 VALUES (3);
SELECT * FROM tab2 ORDER BY a;
A(int) |
------------
1 |
------------
2 |
------------
3 |
总数目：3
-- 开启一个事务
BEGIN;
-- 定义事务级游标
DECLARE cur2 TRANSACTIONAL CURSOR FOR SELECT * FROM tab2 ORDER BY a;/
-- 打开游标
OPEN cur2;
-- 结束事务
END;
-- 事务结束之后，游标将自动关闭并释放
-- 事务外游标已不存在， CLOSE 失败
CLOSE cur2;
ERROR, 游标CUR2不存在
-- 删除表
DROP TABLE tab2;
示例3: 使用查询计划定义游标
-- 清理环境
DEALLOCATE cur3;
DEALLOCATE PREPARE pp3;
DROP TABLE tab3 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab3 (a INT);
INSERT INTO tab3 VALUES (1);
INSERT INTO tab3 VALUES (2);
INSERT INTO tab3 VALUES (3);
INSERT INTO tab3 VALUES (4);
INSERT INTO tab3 VALUES (5);
SELECT * FROM tab3 ORDER BY a;
A(int) |
------------
1 |
------------
2 |
------------
3 |
------------
4 |
------------
5 |
总数目：5
-- 创建查询计划
PREPARE pp3 AS SELECT * FROM tab3 WHERE a > ? ORDER BY a;
-- 使用查询计划定义游标
DECLARE cur3 CURSOR FOR pp3;/
-- 打开游标
OPEN cur3 USING 3;
-- 获取数据
FETCH ALL FROM cur3;
A(int) |
------------
4 |
------------
5 |
总数目：2
-- 关闭游标
CLOSE cur3;
-- 删除游标、查询计划和表
DEALLOCATE cur3;
DEALLOCATE PREPARE pp3;
DROP TABLE tab3;
示例4: 定义可更新游标
-- 清理环境
DEALLOCATE cur4;
DROP TABLE tab4 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab4 (a INT);
INSERT INTO tab4 VALUES (1);
INSERT INTO tab4 VALUES (2);
INSERT INTO tab4 VALUES (3);
SELECT * FROM tab4 ORDER BY a;
A(int) |
------------
1 |
------------
2 |
------------
3 |
总数目：3
-- 定义可更新游标
DECLARE cur4 CURSOR FOR SELECT * FROM tab4 ORDER BY a FOR UPDATE;/
-- 打开游标
OPEN cur4;
-- 获取数据
FETCH cur4;
A(int) |
------------
1 |
总数目：1
-- 删除游标数据
DELETE FROM tab4 WHERE CURRENT OF cur4;
-- 一行数据已被删除
SELECT * FROM tab4 ORDER BY a;
A(int) |
------------
2 |
------------
3 |
总数目：2
-- 获取数据
FETCH cur4;
A(int) |
------------
2 |
总数目：1
-- 更新游标数据
UPDATE tab4 SET a = 9 WHERE CURRENT OF cur4;
-- 数据已被更新
SELECT * FROM tab4 ORDER BY a;
A(int) |
------------
3 |
------------
9 |
总数目：2
-- 关闭游标
CLOSE cur4;
-- 删除游标和表
DEALLOCATE cur4;
DROP TABLE tab4;