DM数据库中的游标

游标概述

游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。类似指针的作用，它能遍历结果集中的所有行，但每次只能指向一行。

通过SELECT …INTO可以将查询结果存放到变量中处理，但只能存放一条记录，返回多行时会报错TOO_MANY_ROWS。但游标可以对多行数据进行逐条处理。

静态游标

只读游标，总是按照打开游标时的原样显示结果集， 在编译时就能确定静态游标使用的查询。静态游标分为隐式游标和显式游标。

隐式游标

隐式游标无需定义，在执行DML语句或SELECT …INTO语句时，DMSQL程序自动声明一个隐式游标并管理它。

此隐式游标的名称为“SQL”，每个游标都有%FOUND、 %NOTFOUND、 %ISOPEN 和%ROWCOUNT 四个属性。

• %FOUND：语句是否修改或查询到了记录，是返回 TRUE，否则返回 FALSE；
• %NOTFOUND：语句是否未能成功修改或查询到记录，是返回 TRUE，否则返回FALSE；
• %ISOPEN： 游标是否打开。 是返回 TRUE，否返回 FALSE。 由于系统在语句执行完成后会自动关闭隐式游标，因此隐式游标的%ISOPEN 属性永远为 FALSE；
• %ROWCOUNT： DML 语句执行影响的行数，或 SELECT…INTO 语句返回的行数。

%NOTFOUND属性示例

BEGIN
	SELECT *FROM DMHR.EMPLOYEE WHERE EMPLOYEE_ID=1001;
	IF SQL%NOTFOUND THEN
	PRINT ‘未找到记录’;
ELSE
	PRINT ‘查看到记录’;
END IF;
END;

执行结果

总共1个语句正依次执行...

[执行语句1]:
BEGIN
	UPDATE DMHR.EMPLOYEE SET EMPLOYEE_NAME = 'MAXUEMING' WHERE EMPLOYEE_ID=1001;
	IF SQL%NOTFOUND THEN
	PRINT '未找到记录';
ELSE
	PRINT '查看到记录';
END IF;
END;
执行成功, 执行耗时0毫秒. 执行号:52814
查看到记录

影响了1条记录

1条语句执行成功

显式游标

显式游标指向一个查询语句的结果集。 当需要处理返回多条记录的查询时，应该显式地定义游标以处理结果集的每一行。

使用显式游标一般包含四个步骤：

• 定义游标。声明游标及关联的查询语句
• 打开游标。执行关联的语句，将结果集装入游标工作区，将游标定位到结果集的第一行之前
• 拨动游标。移动游标到合适的位置。
• 关闭游标。关闭，释放资源。

定义显式游标

语法

CURSOR <游标名> [FAST | NO FAST] <cursor 选项>;
或 <
游标名> CURSOR [FAST | NO FAST] <cursor 选项>;

缺省创建的游标为NO FAST，普通游标。快速游标提前返回结果集，速度快，但有以下约束：

• FAST属性只在显式游标中支持。
• 使用快速游标的 DMSQL 程序语句块中不能修改快速游标所涉及的表。
• 不支持游标更新和删除；
• 不支持 NEXT 以外的 FETCH 方向；
• 不支持快速游标作为函数返回值；
• MPP 环境下不支持对快速游标进行 FETCH 操作。

定义显式游标

DECLARE
CURSOR C1 IS SELECT *FROM DMHR.EMPLOYEE WHERE EMPLOYEE_ID=1001;

打开显式游标

OPEN <游标名>;

打开上面创建的CURSOR游标，此处必须是已定义的游标，执行游标关联的查询语句，获取结果集并将游标定位到结果集的第一行之前。

OPEN C1;

注意：打开一个已打开的游标时，此游标会被重新初始化。

拨动游标
拨动游标前必须先打开此游标。
语法：

FETCH [<fetch 选项> [FROM]] <游标名> [ [BULK COLLECT] INTO <主变量名>{,<主变量名>} ] [LIMIT <rows>];
<fetch 选项>::= NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n

FETCH选项指定将游标移动到结果集的某个位置：

• NEXT： 游标下移一行
• PRIOR： 游标前移一行
• FIRST： 游标移动到第一行
• LAST： 游标移动到最后一行
• ABSOLUTE n： 游标移动到第 n 行
• RELATIVE n： 游标移动到当前指示行后的第 n 行

FETCH每次获取一条记录，除非指定了“BULK COLLECT”。
不指定FETCH选项时，第一次执行FETCH语句时，游标下移，指向结果集的第一行，之后每次执行，游标均下移一行。

INTO 子句中的变量个数、 类型必须与游标关联的查询语句中各 SELECT 项的个数、类型一一对应。

DECLARE
	E_NAME VARCHAR;
	E_PHONE VARCHAR;
	C1 CURSOR;
BEGIN
	OPEN C1 FOR SELECT E.EMPLOYEE_NAME,E.PHONE_NUM FROM DMHR.EMPLOYEE E WHERE E.EMPLOYEE_ID < 1010;
	
	LOOP
		FETCH C1 INTO E_NAME,E_PHONE;
		EXIT WHEN C1%NOTFOUND;
		PRINT E_NAME || E_PHONE;
	END LOOP;
	
	CLOSE C1;
END;

执行结果

执行成功, 执行耗时0毫秒. 执行号:52818
MAXUEMING15312348552
程擎武13912366391
郑吉群18512355646
陈仙13012347208
金纬13612374154

影响了0条记录

FETCH…BULK COLLECTINTO的用法
使用FETCH…BULK COLLECTINTO可将结果集批量赋值给集合变量，同时可使用LIMIT ROWS限制行数。

DECLARE
TYPE V_rd IS RECORD(V_NAME VARCHAR(50),V_PHONE VARCHAR(50)); --定义记录类型
TYPE V_type IS TABLE OF V_rd INDEX BY INT; --定义索引表类型
v_info V_type;
c1 CURSOR IS SELECT E.EMPLOYEE_NAME,D.DEPARTMENT_NAME FROM DMHR.EMPLOYEE E,DMHR.DEPARTMENT D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID AND E.EMPLOYEE_ID < 1010;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO v_info;
CLOSE c1;
FOR I IN 1..v_info.COUNT LOOP
PRINT v_info(I).V_NAME ||’,’||v_info(I).V_PHONE;
END LOOP;
END;

执行结果

执行成功, 执行耗时1毫秒. 执行号:52825
MAXUEMING,总经理办
金纬,技术支持部
陈仙,市场部
郑吉群,开发部
程擎武,行政部

影响了0条记录

1条语句执行成功

注意：
BULK COLLECT 可以和 SELECT INTO、 FETCH INTO、 RETURNING INTO 一起使
用， BULK COLLECT 之后 INTO 的变量必须是集合类型。

关闭游标
游标使用完后应及时关闭，释放资源。关闭后不能再从游标中获取数据

CLOSE <游标名>;

相关属性
显式游标也有%FOUND、 %NOTFOUND、 %ISOPEN 和%ROWCOUNT 四个属性，但意义和隐式游标不同

• %FOUND：第一次拨动游标之前，其值为 NULL。如果最近一次拨动游标时取到了数据，其值为 TRUE，否则为 FALSE。
• %NOTFOUND：在第一次拨动游标之前，其值为 NULL。如果最近一次拨动游标时取到了数据，其值为 FALSE，否则为TRUE。
• %ISOPEN： 游标打开时为 TRUE，否则为 FALSE。
• %ROWCOUNT： 如果游标未打开，产生一个异常。如游标已打开，在第一次拨动游标之前其值为 0，否则为最近一次拨动后已经取到的元组数。

使用ROWCOUNT属性示例
取出DMHR.EMPLOYEE表的前五行数据。

DECLARE
CURSOR c1 FOR SELECT E.EMPLOYEE_ID,EMPLOYEE_NAME FROM DMHR.EMPLOYEE E;
E_ID INT;
E_NAME VARCHAR;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO E_ID,E_NAME;
EXIT WHEN c1%NOTFOUND; 
PRINT E_ID || ' ' || E_NAME;
EXIT WHEN c1%ROWCOUNT=5; 
END LOOP;
CLOSE c1;
END;

执行结果

执行成功, 执行耗时0毫秒. 执行号:52827
1001 MAXUEMING
1002 程擎武
1003 郑吉群
1004 陈仙
1005 金纬

影响了0条记录

1条语句执行成功

动态游标

静态游标在声明时就指定关联的查询语句，而动态游标先只声明一个变量，在执行部分打开游标时才指定查询语句。与静态游标的不同主要的定义和打开部分。

定义动态游标
语法：

CURSOR <游标名>；

打开动态游标

OPEN <游标名><for 表达式>;

示例：使用动态游标输出

DECLARE
E_ID INT;
E_NAME VARCHAR;
c1 CURSOR;
BEGIN
OPEN C1 FOR SELECT E.EMPLOYEE_ID,EMPLOYEE_NAME FROM DMHR.EMPLOYEE E WHERE E.EMPLOYEE_ID < 1010;
LOOP
FETCH c1 INTO E_ID,E_NAME;
EXIT WHEN c1%NOTFOUND;
PRINT 'ID:'||E_ID || '姓名：' || E_NAME;
END LOOP;
CLOSE c1;
END;

执行结果

执行成功, 执行耗时0毫秒. 执行号:52828
ID:1001姓名：MAXUEMING
ID:1002姓名：程擎武
ID:1003姓名：郑吉群
ID:1004姓名：陈仙
ID:1005姓名：金纬

影响了0条记录

1条语句执行成功

参数/占位符

在动态游标关联的查询语句中还可以代参数，用？指定，在打开游标时，使用USING子句指定参数，个数与语句中的？占位符一致。

DECLARE
E_NAME VARCHAR;
CURSOR C1;
BEGIN
OPEN C1 FOR 'SELECT E.EMPLOYEE_NAME FROM DMHR.EMPLOYEE E WHERE E.EMPLOYEE_ID = ? OR E.EMAIL = ?' USING '1001','chengqingwu@dameng.com';
LOOP
FETCH C1 INTO E_NAME;
EXIT WHEN C1%NOTFOUND;
PRINT E_NAME;
END LOOP;
CLOSE C1;
END;

执行结果

执行成功, 执行耗时1毫秒. 执行号:52834
程擎武
MAXUEMING

影响了0条记录

1条语句执行成功

游标变量

游标变量是指游标对象的一个指针，是一种引用类型。

语法：

<游标变量名> CURSOR[:= <源游标名>];
或
TYPE <类型名> IS REF CURSOR [RETURN <DMSQL 数据类型>];
<游标变量名><类型名>;

引用游标的状态，属性和源游标完全一致。

引用游标的特点：

• 可以为多个查询所用，在前一个查询完成后为其他查询打开处理。
• 可对引用游标赋值。
• 可在表达式中使用引用游标
• 可作为子程序的参数
• 可在不同的子程序中传递结果集

使用游标更新，删除数据
要使用此功能，必须在游标关联的查询语句中使用FOR UPDATE选项，此选项对要修改的行上锁。
使用时必须在UPDATE/DELETE语句结尾使用WHERE CURRENT OF 子句，指定修改游标当前所指的行。

示例：使用游标更新数据

DECLARE
CURSOR C1 is SELECT E.EMPLOYEE_NAME FROM DMHR.EMPLOYEE E WHERE E.EMPLOYEE_ID=1001 FOR UPDATE;
BEGIN
OPEN C1;
IF C1%ISOPEN THEN
FETCH C1;
UPDATE DMHR.EMPLOYEE E SET E.EMPLOYEE_NAME = 'THANOS' WHERE CURRENT
OF C1;
ELSE
PRINT 'CURSOR IS NOT OPENED';
END IF;
CLOSE C1;
END;

执行结果

执行成功, 执行耗时1毫秒. 执行号:52836

1条语句执行成功

查询验证