Oracle PL/SQL开发基础（第二十四弹：游标基本结构）

游标简介

游标只是一个指向查询语句返回结果的指针，因此在游标定义时，将包含一个查询定义。当游标打开后，数据被接收到一块内存区域存储，直到游标关闭。
游标实际上指向的是一块内存区域，这块内存区域位于进程全局区内部，称为上下文区域（Context Area），在上下文区域中保存了如下3类信息：
- 查询返回的数据行
- 查询所处理的数据的行号
- 指向共享池中的已分析的SQL语句。

在进行游标操作的过程中，也可以将游标看做是指向数据表中数据的指针，但是在后台，游标实际指向的是一块内存区域。

使用如下：

DECLARE
   emprow   emp%ROWTYPE;     --定义保存游标检索结果行的记录变量
   CURSOR emp_cur            --定义游标
   IS
SELECT *
FROM emp
WHERE deptno IS NOT NULL;
BEGIN
OPEN emp_cur;             --打开游标
   LOOP                      --循环检索游标
      FETCH emp_cur          --提取游标内容
       INTO emprow;
--输出检索到的游标行的信息
      DBMS_OUTPUT.put_line (   '员工编号：'
                            || emprow.empno
                            || ' '
                            || '员工名称：'
                            || emprow.ename
                           );
      EXIT WHEN emp_cur%NOTFOUND;  --当游标数据检索完成退出循环
END LOOP;   
   CLOSE emp_cur;           --关闭游标
END;

游标分类

上面使用CURSOR显式定义的游标称为显式游标，在PL/SQL宏，游标可分为两类：
- 显式游标：使用CURSOR显式定义的游标，游标被定义后，需要打开并提取游标。
- 隐式游标：由Oracle为每一个不属于显式游标的SQL DML语句都创建一个隐式游标，由于隐式游标没有名称，因此也叫作SQL游标。隐式游标不能显式地打开。

比如在执行UPDATE时会自动具有一个隐式游标，例如：

BEGIN
UPDATE emp
SET comm = comm * 1.12
WHERE empno = 7369;              --更新员工编号为7369的员工信息 
--使用隐式游标属性判断已更新的行数
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' 行被更新');
--如果没有任何更新
   IF SQL%NOTFOUND
   THEN
--显示未更新的信息
      DBMS_OUTPUT.put_line ('不能更新员工号为7369的员工!');
END IF;
--向数据库提交更改
COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);  --如果出现异常，显示异常信息
END;

隐式游标会自动关闭，因此如果访问隐式游标的%ISOPEN属性，得到的结果总是False。

INSERT、UPDATE、DELETE和SELECT INRO语句在被执行时，都会隐含地创建游标，通过访问游标的四大属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT来访问游标的相关属性。

定义游标类型

显式游标的定义语法如下：

CURSOR cursor_name [parameter_list]
[RETURN return_type]
IS query
[FOR UPDATE [OF (column_list)][NOWAIT]]

如：

DECLARE
   CURSOR emp_cursor      --定义一个查询emp表中部门编号为20的游标 
   IS
SELECT *
FROM emp
WHERE deptno = 20;
BEGIN
NULL;
END;

DECLARE
   v_deptno NUMBER;
   CURSOR emp_cursor      --定义一个查询emp表中部门编号为20的游标 
   IS
SELECT *
FROM emp
WHERE deptno = v_deptno;
BEGIN
 v_deptno:=20;
   OPEN emp_cursor;       --打开游标
   IF emp_cursor%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('游标已经被打开');
END IF;
END;

DECLARE
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
   IS
SELECT *
FROM emp
WHERE deptno = p_deptno;
BEGIN
OPEN emp_cursor (20);
END;

打开游标

如：

DECLARE
--声明游标并指定游标返回值类型
   CURSOR emp_cursor (p_deptno IN NUMBER) RETURN dept%ROWTYPE
   IS
SELECT *
FROM emp
WHERE deptno = p_deptno;  
BEGIN
OPEN emp_cursor (20);   --打开游标
END;

在游标被打开时，将会发生如下动作：
- 检验绑定变量的值。
- 基于查询的语句确定游标的活动集。
- 游标指针指向游标活动集的第一行。

如果在定义游标时使用了FOR UPDATE，打开游标是会锁住游标记录，所有对数据的INSERT、UPDATE、DELETE等操作对游标提取出来的结果集都没有影响，直到游标关闭在打开以后，这些影响才在结果集里反映出来。

使用游标属性

游标属性用于返回游标的执行信息，不论是显式游标还是隐式游标，都包含了%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT属性。使用显式游标属性时，使用显示游标名做前缀，使用隐式游标属性时使用SQL作为前缀。

%ISOPEN属性

%ISOPEN属性判断对应的游标变量是否打开，如果游标变量打开则返回True，否则返回False。
如：

DECLARE
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
   IS
SELECT *
FROM emp
WHERE deptno = p_deptno;
BEGIN
IF NOT emp_cursor%ISOPEN THEN --如果游标还没有被打开
OPEN emp_cursor (20);                          --打开游标
END IF;
   IF emp_cursor%ISOPEN THEN                        --判断游标状态，显示状态信息
     DBMS_OUTPUT.PUT_LINE('游标已经被打开！');
   ELSE
     DBMS_OUTPUT.PUT_LINE('游标还没有被打开！');   
END IF;   
END;

%FOUND属性

如果游标没有打开就使用%FOUND，Oracle将会提示错误信息。当游标被打开后，在调用FETCH之前，%FOUND会产生NULL值，而此后每取得一行数据，其值会为True，如果最后一次取得数据失败，其值会变为False。因此，%FOUND的作用是检查是否从结果集中提取到了数据。
如：

DECLARE
   emp_row   emp%ROWTYPE;                                --定义游标值存储变量
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
IS
SELECT *
        FROM emp
       WHERE deptno = p_deptno;
BEGIN
IF NOT emp_cursor%ISOPEN
THEN                                                --如果游标还没有被打开
OPEN emp_cursor (20);                                        --打开游标
END IF;
IF emp_cursor%FOUND IS NULL                        --在使用FETCH提取游标数据之前，值为NULL
THEN
      DBMS_OUTPUT.put_line ('%FOUND属性为NULL');   --输出提示信息
END IF;
LOOP                                               --循环提取游标数据
      FETCH emp_cursor  
       INTO emp_row;                                  --使用FETCH语句提取游标数据
--每循环一次判断%FOUND属性值，如果该值为False，表示提取完成，将退出循环。
EXIT WHEN NOT emp_cursor%FOUND;
END LOOP;
END;

%NOTFOUND属性

该属性与%FOUND相反。
如：

DECLARE
   emp_row   emp%ROWTYPE;                                --定义游标值存储变量
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
IS
SELECT *
        FROM emp
       WHERE deptno = p_deptno;
BEGIN
OPEN emp_cursor (20);                                        --打开游标
IF emp_cursor%NOTFOUND IS NULL                        --在使用FETCH提取游标数据之前，值为NULL
THEN
      DBMS_OUTPUT.put_line ('%NOTFOUND属性为NULL');   --输出提示信息
END IF;
LOOP                                               --循环提取游标数据
      FETCH emp_cursor  
       INTO emp_row;                                  --使用FETCH语句提取游标数据
--每循环一次判断%FOUND属性值，如果该值为False，表示提取完成，将退出循环。
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
END;

%ROWCOUNT属性

%ROWCOUNT用来返回到目前为止已经从游标中取出的记录行数，当游标被打开时，%ROWCOUNT值为0，每取得一条数据，%ROWCOUNT的值就加1。
如：

DECLARE
   emp_row   emp%ROWTYPE;                                --定义游标值存储变量
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
   IS
SELECT *
FROM emp
WHERE deptno = p_deptno;
BEGIN
OPEN emp_cursor (20);                                        --打开游标
   LOOP                                               --循环提取游标数据
      FETCH emp_cursor  
       INTO emp_row;                                  --使用FETCH语句提取游标数据
--每循环一次判断%FOUND属性值，如果该值为False，表示提取完成，将退出循环。
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('当前已提取的行数为：'||emp_cursor%ROWCOUNT||' 行！');
END LOOP;
   CLOSE emp_cursor;
END;

提取游标记录

如：

DECLARE
  deptno dept.deptno%TYPE;                   --定义保存游标数据的变量
  dname dept.dname%TYPE;
  loc dept.loc%TYPE;
  dept_row dept%ROWTYPE;                     --定义记录变量
  CURSOR dept_cur IS SELECT * FROM dept;     --定义游标
BEGIN
OPEN dept_cur ;                           --打开游标
LOOP      
IF dept_cur%ROWCOUNT<=4 THEN           --判断如果当前提取的游标小于等于4行
        FETCH dept_cur  INTO dept_row;       --提取游标数据到记录类型中
IF dept_cur%FOUND THEN               --如果FETCH到数据，则进行显示
        DBMS_OUTPUT.PUT_LINE(dept_row.deptno||' '||dept_row.dname||' '||dept_row.loc);
END IF;
ELSE
        FETCH dept_cur INTO deptno,dname,loc;--否则提取记录到变量列表中
IF dept_cur%FOUND THEN               --如果提取到数据则进行显示
        DBMS_OUTPUT.PUT_LINE(deptno||' '||dname||' '||loc);
END IF;        
END IF;
EXIT WHEN dept_cur%NOTFOUND;           --判断是否提取完成
END LOOP;
   CLOSE dept_cur
END;

要注意，在将游标的值提取到变量列表时，必须要注意的是变量的类型与查询的字段类型必须一致。

批量提取游标数据

由于FETCH语句一次只从结果集中提取一行，并且提取只能是向前的，因此如果要重新提取已经提取过的数据，只有重新打开游标。
使用BULK COLLECT批处理子句可以一次性将游标中的结果集保存到集合中，这样就可以在集合中进行前进和后退处理。
如：

DECLARE
TYPE depttab_type IS TABLE OF dept%ROWTYPE;    --定义dept行类型的嵌套表类型
   depttab   depttab_type;                        --定义嵌套表变量
   CURSOR deptcur IS SELECT * FROM dept;          --定义游标
BEGIN
OPEN deptcur;
   FETCH deptcur BULK COLLECT INTO depttab;       --使用BULK COLLECT INTO子句批次插入
   CLOSE deptcur;                                 --关闭游标
FOR i IN 1 .. depttab.COUNT                    --循环嵌套表变量中的数据
LOOP
      DBMS_OUTPUT.put_line (   depttab (i).deptno
                            || ' '
                            || depttab (i).dname
                            || ' '
                            || depttab (i).loc
                           );
END LOOP;
   CLOSE deptcur;                                --关闭游标
END;

使用BULK COLLECT INTO使得面向游标的操作变成了面向集合的操作，这在需要前后导航记录数据时非常有用。

BULK COLLECT INTO会一次性将所有的数据都提取到集合中，如果数据量特别大，并且在使用VARRAY这样的具有固定元素个数的集合时，可能需要限制每次提取的行数，可以使用BULK COLLECT INTO LIMIT提取部分数据，如：

DECLARE
TYPE dept_type IS VARRAY (4) OF dept%ROWTYPE;    --定义变长数组类型
   depttab   dept_type;                             --定义变长数组变量
   CURSOR dept_cursor                               --定义打开dept的游标
IS
SELECT *
        FROM dept;
   v_rows    INT       := 4;                        --使用LIMIT限制的行数
   v_count   INT       := 0;                        --保存游标提取过的行数
BEGIN
OPEN dept_cursor;                                --打开游标
LOOP                                             --循环提取游标
--每次提取4行数据到变长数组中
      FETCH dept_cursor BULK COLLECT INTO depttab LIMIT v_rows;
EXIT WHEN dept_cursor%NOTFOUND;               --没有游标数据时退出 
      DBMS_OUTPUT.put('部门名称：');             --输出部门名称
--循环提取变长数组数据，因为变长数组只能存放4个元素，因此不能越界读取
FOR i IN 1 .. (dept_cursor%ROWCOUNT - v_count) 
LOOP
         DBMS_OUTPUT.put (depttab (i).dname || ' '); --输出部门名称
END LOOP;
      DBMS_OUTPUT.new_line;                      --输出新行
      v_count := dept_cursor%ROWCOUNT;              --为v_count赋新的值
END LOOP;
   CLOSE dept_cursor;                               --关闭游标
END;

关闭游标

在将游标结果集中的所有数据都提取完了以后，应该立即关闭游标，以便释放所有与游标相关的资源，如：

CLOSE dept_cursor;                          --关闭游标