oracle PL/SQL（procedure language/SQL）程序设计之游标cursors

浏览数：32 / 时间：2015年06月12日

游标 Cursors--Conception

每一条被Oracle服务器执行的SQL语句都有一个独立的游标与之相关联:
隐式游标 Implicit cursors: 用于所有的DML和PL/SQL的SELECT语句。
显示游标 Explicit cursors: 被程序显示声明和命名。
所定义的SQL语句必须只包含select语句，并且不能用insert、update或delete关键字。
当select语句可能返回零或多于一行时，必须用显式游标。
当Select语句预计只返回一行时，隐式游标将做得更好。

SQL游标属性(隐式游标)

使用SQL游标属性，能够测试SQL语句的执行结果。
在PL/SQL中用“SQL”引用最近的隐式游标。在程序中不能用OPEN、FETCH和CLOSE语句控制隐式游标。显示游标在后续课程中讲解。

从表rooms中删除指定的room_id行，并打印删除的行数。

VARIABLE rows_deleted VARCHAR2(100)
DECLARE
v_empno NUMBER := 7788;
BEGIN
DELETE FROM emp
WHERE empno = v_room_id;
:rows_deleted := SQL%ROWCOUNT||‘ rows deleted.‘;
END;

PRINT rows_deleted

显示游标的功能
能够一行一行的处理多行查询结果。
能够记录和跟踪当前正在处理的行。
在PL/SQL块中允许程序手工控制游标。

（1）创建游标语法：

CURSOR cursor_name IS
select_statement;

在游标声明中不能包含INTO子句。
如果处理的行要求特定的顺序，在查询语句中可以使用 ORDER BY子句。
examples;

DECLARE
CURSOR c1 IS
    SELECT empno, ename
        FROM   emp;
CURSOR c2 IS
    SELECT *
    FROM   dept
    WHERE deptno = 10;
BEGIN
...
（2）打开游标

OPEN cursor_name;

打开游标
执行查询。
活动集（查询结果的行的集合）被确定。
活动集的指针指向第一行。
如果查询没有返回行，不会抛出异常。
提取一行后，应该使用游标属性测试结果。
（3）从游标中提取数据

FETCH cursor_name INTO [variable1, variable2, ...]
| record_name];

提取当前行的值存储到PL/SQL变量中。
变量的类型必须与查询的选择列表的类型相兼容。
变量的数量必须与查询的选择列表的数量相同。
测试游标是否包含行。如果没有提取出值，则说明在处理的活动集中没有剩下需要处理的行并且不会有错误记录。

例子
DECLARE（声明数据变量）
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_empRecord emp%ROWTYPE;
CURSOR cur_AllEmp IS（创建游标）
SELECT * FROM emp;
BEGIN
OPEN cur_AllEmp;打开游标
FETCH cur_AllEmp INTO v_empRecord;（从游标中提取数据）
FETCH cur_AllEmp INTO v_ename, v_sal;（error）
END;

例子从scott.emp表中查询所有记录，利用游标获取前两行记录并输出ename、job、sal中的值
DECLARE
CURSOR c1 IS SELECT * FROM emp; 创建游标）
emp_rec emp%ROWTYPE; --定义一个和表结构完全一致的记录变量
BEGIN
   OPEN c1;
   FETCH c1 INTO emp_rec;
   dbms_output.put_line(‘姓名是:‘||emp_rec.ename|| ‘工作是:‘||emp_rec.job|| ‘工资是:‘||emp_rec.sal);
   FETCH c1 INTO emp_rec;
   dbms_output.put_line(‘姓名是:‘||emp_rec.ename||‘工作是:‘||emp_rec.job|| ‘工资是:‘||emp_rec.sal);
   CLOSE c1;
END;

关闭游标 CLOSE cursor_name;

当所有的活动集都被检索以后，游标就应该关闭。
如果需要，可以在重新打开游标。
一旦关闭了游标，再从该游标提取数据就将是非法的。这样做会产生一个Oracle错误。

游标提取控制

使用LOOP循环实现显示游标多行数据处理。
反复使用Fetch来提取每一行数据。
使用%NOTFOUND属性来判断提取(Fetch)行是否不成功。
使用%FOUND显示游标属性来判断每一次提取(Fetch)操作是否成功。
%ISOPEN属性

只有当游标打开是才能进行提取(Fetch)行的操作。
在进行提取(Fetch)行操作之前，使用%ISOPEN属性测试游标是否打开。
Example
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
LOOP
FETCH c1...

使用%ROWCOUNT游标属性，返回到目前位置由游标返回的行的数目。
使用%NOTFOUND游标属性，作为LOOP循环的退出条件。
例子使用游标实现逐行输出scott.emp表中部门编号为10的员工姓名和工资。
DECLARE
   CURSOR emp_cursor IS 创建游标
   SELECT ename,sal FROM emp WHERE deptno=10;
   emp_record emp%ROWTYPE;
BEGIN
   OPEN emp_cursor ;打开游标
   LOOP 建立loop循环
      FETCH emp_cursor INTO emp_record.ename,emp_record.sal; 、、提取数据
      EXIT WHEN emp_cursor%NOTFOUND; //退出循环
      dbms_output.put_line(‘ename: ‘||emp_record.ename||‘ sal:‘||emp_record.sal);
   END LOOP;//
   dbms_output.put_line(‘row count:‘||emp_cursor%rowcount);
   CLOSE emp_cursor;//关闭游标
END;

游标的FOR循环

FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
游标的FOR循环能够便捷的处理显示游标。
FOR循环中的循环控制变量不需要事先定义。
在游标的FOR循环之前，系统能够自动打开游标；在FOR循环结束后，系统能够自动关闭游标，不需要人为操作。
在游标的FOR循环过程中，系统能够自动执行FETCH语句；每一次循环，系统就自动执行一次FETCH语句，将游标指向的当前行记录存入循环控制变量中
例子使用FOR循环实现逐行输出emp表中部门编号为10的员工姓名和工资。

DECLARE
   CURSOR emp_cursor IS
   SELECT ename,sal FROM scott.emp WHERE deptno=10;
BEGIN
   FOR emp_record IN emp_cursor LOOP
      dbms_output.put_line(‘ename: ‘||emp_record.ename||‘ sal:‘||emp_record.sal);
   END LOOP;
/* 该命令无效，因为FOR循环结束后游标自动关闭
dbms_output.put_line(‘row count:‘||emp_cursor%rowcount); */
END;
游标提取循环

例子使用游标查询emp表中的所有记录，并在程序块中输出工资最高的前五行记录。
DECLARE
   CURSOR cur IS SELECT * FROM scott.emp ORDER BY sal DESC;
BEGIN
   FOR rec IN cur LOOP
      IF cur%ROWCOUNT<=5 THEN [cur%ROWCOUNT 从1 开始]
                dbms_output.put_line(‘ename:‘||rec.ename||’sal:‘||rec.sal);
      ELSE
        EXIT;
      END IF;
   END LOOP;
END;

使用子查询的游标FOR循环(不需要定义游标)

BEGIN
FOR emp_record IN ( SELECT empno, ename
                          FROM   emp) LOOP （就是用DML语句代替了游标而已，其他到没有啥子）
         -- implicit open and implicit fetch occur
    IF emp_record.empno = 7839 THEN
      ...
END LOOP; -- implicit close occurs
END;

带参数的游标

CURSOR cursor_name(parameter_name datatype) IS select_statment;
使用open命令打开带参数的游标时，需要给游标传递实参值。
   实例定义参数游标，查询指定部门的员工姓名
DECLARE
--定义游标参数no，参数类型为number类型
CURSOR emp_cursor( no NUMBER) IS
       SELECT ename FROM emp WHERE deptno=no;
emp_rec emp_cursor%ROWTYPE;
BEGIN
--打开参数游标时，指明一个替代变量作为游标参数的值
OPEN emp_cursor(&no);
LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line(‘ename:‘||emp_rec.ename);
END LOOP;
CLOSE emp_cursor;
END;