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_curEND;

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

批量提取游标数据

由于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;                          --关闭游标