源码-PL/SQL从入门到精通-第十章-使用游标-Part 1

心得：在PL/SQL Developer中调试代码时，不能准确提示行号，此时，可将代码段复制到sqlplus界面，能准确提示出错的行数，帮助发现问题所在。

第一次接触Oracle时，感觉游标是个很高大上的概念。最近才发现游标和C语言中的指针类似，不是什么高深的概念。正所谓，“难者不会，会者不难”

基本概念：

When Oracle Database executes a SQL statement, it stores the result set and
processing information in an unnamed private SQL area. A pointer to this unnamed
area, called a cursor, lets you retrieve the result set one row at a time. Cursor
attributes return information about the state of the cursor.
Every time you run either a SQL DML statement or a PL/SQL SELECT INTO
statement, PL/SQL opens an implicit cursor. You can get information about this
cursor from its attributes, but you cannot control it. After the statement runs, the
database closes the cursor; however, its attribute values remain available until another
DML or SELECT INTO statement runs.
PL/SQL also lets you declare cursors. A declared cursor has a name and is
associated with a query (SQL SELECT statement)—usually one that returns multiple
rows. After declaring a cursor, you must process it, either implicitly or explicitly. To
process the cursor implicitly, use a cursor FOR LOOP. The syntax is:
FOR record_name IN cursor_name LOOP
statement
[ statement ]...
END LOOP;
To process the cursor explicitly, open it (with the OPEN statement), fetch rows from
the result set either one at a time or in bulk (with the FETCH statement), and close the
cursor (with the CLOSE statement). After closing the cursor, you can neither fetch
records from the result set nor see the cursor attribute values.
The syntax for the value of an implicit cursor attribute is SQL%attribute (for example,
SQL%FOUND). SQL%attribute always refers to the most recently run DML or
SELECT INTO statement.
The syntax for the value of a declared cursor attribute is cursor_name%attribute (for
example, c1%FOUND).
Below table lists the cursor attributes and the values that they can return. (Implicit
cursors have additional attributes that are beyond the scope of this book.)

游标有以下属性：

代码如下：

--第10章开始
--代码10.1在PL/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;


SELECT * FROM emp;

--代码10.2 在PL/SQL中使用隐式游标
BEGIN
   UPDATE emp
      SET comm = comm * 1.12
    WHERE empno = 7687;              --更新员工编号为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;


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


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

--代码10.5 声明游标参数（这参数是干什么的？）
DECLARE
   CURSOR emp_cursor (p_deptno IN NUMBER)            --定义游标并指定游标参数
   IS
      SELECT *
        FROM emp
       WHERE deptno = p_deptno;
BEGIN
   OPEN emp_cursor (20);
END;

--代码10.6 指定游标的返回类型

   --声明游标并指定游标返回值类型
   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.7 %ISOPEN游标属性使用示例
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;


--代码10.8 %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游标属性示例
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语句提取游标数据
        DBMS_OUTPUT.PUT_LINE('当前已提取的行数为：'||emp_cursor%ROWCOUNT||' 行！');                             
      --每循环一次判断%FOUND属性值，如果该值为False，表示提取完成，将退出循环。
      EXIT WHEN emp_cursor%NOTFOUND;
   END LOOP;
END;

--代码10.10 %Rowcount游标属性使用示例
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;

--代码10.11 使用Fetch语句提取游标数据
SELECT * FROM dept;

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;

--代码10.12 使用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子句批次插入
   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 colect 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;