第十章 使用游标

例一：

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(

           

          )

        EXIT  WHEN  emp_cur%NOTFOUND;

END LOOP;

CLOSE  emp_cur;

END;

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

DECLARE

     v_deptno   NUMBER;

     CURSOR  emp_cursor

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 ;

定义游标时，为游标指定参数，可以使用RETURN子句定义游标返回值的类型，返回值的类型一定要和返回结果集的数据类型一致，因此返回值一般为记录类型或%ROWTYPE指定的表中的类型。

DECLARE

         ----声明游标并指定游标返回值类型

    CURSOR  emp_cursor ( p_deptno IN NUMBER)  RETURN   emp%ROWTYPE

    IS

      SELECT *

          FROM emp

     WHERE  deptno = p_deptno;

BEGIN

        OPEN  emp_cursor(20);

END;

10.1.5使用游标属性

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;

     CLOSE   emp_cursor;

END;

2.%FOUND属性

当游标被打开后，在调用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
    THEN
        DBMS_OUTPUT.put_line('%FOUND属性为NULL');
    END IF;
    LOOP                                            --循环提取游标数据
        FETCH emp_cursor
         INTO emp_row;
         EXIT WHEN NOT emp_cursor%FOUND;
    END LOOP;
    CLOSE emp_cursor;
END;

4.%ROWCOUNT属性

当游标被打开时，%ROWCOUNT值为0，每取得一条数据，%ROWCOUNT的值就加1.

10.1.6提取游标数据

FETCH语句，可以一次一行地提取游标数据，也可以使用BULK  COLLECT子句一次性接受所有的游标数据到一个数组或者一个PL/SQL表中。

FECTH  cursor_name INTO  variable_name(s) | PL/SQL_record;

DECLARE
    deptno dept.deptno%TYPE;
    dname  dept.dname%TYPE;
    loc       dept.dname%TYPE;
    dept_row dept%ROWTYPE;
    CURSOR dept_cur IS SELECT * FROM dept;
BEGIN
    OPEN dept_cur;
    LOOP
      IF dept_cur%ROWCOUNT <= 4 THEN
          FETCH    dept_cur  INTO dept_row;
          IF dept_cur%FOUND THEN
              DBMS_OUTPUT.PUT_LINE(dept_row.deptno||' '||dept_now.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;

10.1.7批量提取游标数据

由于FETCH语句一次只从结果集中提取一行，并且提取只能是向前的，因此如果要重新提取已经提取过的数据，只有重新打开游标。

使用BULK COLLECT 批处理子句可以一次性将游标中的结果集保存到集合中，这样就可以在集合中进行前进和后退处理。下面的示例演示了如何使用BULK COLLECT语句一次性获取游标数据。

DECLARE

     TYPE  depttab_type IS TABLE OF dept%ROWTYPE;
     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 语句会一次性将所有的数据都提取到集合中，如果数据量特别大，并且在使用VARRAY这样的具有固定元素个数的集合时，可能需要限制每次提取的行数，可以使用FETCH COLLECT INTO LIMIT语句提取部分数据：

DECLARE
    TYPE dept_type IS VARRAY(4) OF dept%ROWTYPE;
    depttab dept_type
    CURSOR dept_cursor
    IS
        SELECT *
          FROM dept;
    v_rows   INT := 4;
    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(deptabl(i).dname||'');
        END LOOP;
        DBMS_OUTPUT.NEW_LINE;    --输出新行
        v_count := dept_cursor%ROWCOUNT;--为v_count赋新的值
    END LOOP
    CLOSE dept_cursor;
END;

在代码中，通过使用BULK COLLECT INTO depttab LIMIT v_rows 一次性提取游标中的4挑记录到变长数组中，然后开始循环变长数组。由于变长数组的元素个数相对固定，因此使用v_count来减少游标个数进行提取。在代码中使用DBMS_PUT和DBMS_NEW_LINE进行断行处理。