PL/SQL-->游标

一、游标的相关概念及特性

    1.定义

        映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。

    2.游标的分类

        显式游标:即用户自定义游标，专门用于处理select语句返回的多行数据
        隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句

    3.游标使用的一般过程：

        显式游标：声明, 打开, 读取, 关闭
        隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的

    4.显式游标的过程描述

        a.声明游标

            CURSOR cursor_name IS select_statement
            如：CURSOR emp_cur IS SELECT empno,ename,job,sal FROM scott.emp;

        b.打开游标

            OPEN cursor_name     --打开游标则执行对应的select语句，将对应的结果集存放到游标当中
            如：OPEN emp_cur

        c.读取数据

            FETCH cursor_name INTO var_name1,...var_name2 ;   --提取单行数据，需要配合循环语句来使用
            FETCH cursor_name BULK COLLECT INTO collect1,collect2,...[LIMIT rows];    --提取多行数据，collect为集合变量

        d.关闭游标

            CLOSE cursor_name

    5.显式游标的4个属性

        cursor_name%ISOPEN      游标是否打开   
        cursor_name%FOUND       最近的FETCH是否提取到数据
        cursor_name%NOTFOUND       最近的FETCH是否没有提取到数据
        cursor_name%ROWCOUNT       返回到目前为止，已经从游标缓冲区中提取到数据的行数

二、显式游标应用示例

    --例：浏览数据，输入职位，查看每个人工资(使用fetch cursor_name into来提取单行记录)

        scott@ORCL> get /u01/bk/scripts/emp_cur1.sql

          1  DECLARE
          2      v_name emp.ename%TYPE;       --定义变量，用于存放游标提取的数据
          3      v_job emp.job%TYPE;
          4      v_sal emp.sal%TYPE;
          5      CURSOR emp_cur IS select ename,sal FROM emp WHERE job=v_job;
          6  BEGIN
          7      v_job:='&inputjob';
          8      OPEN emp_cur;
          9      DBMS_OUTPUT.PUT_LINE('Name     Sal');
         10      LOOP
         11         FETCH emp_cur INTO v_name,v_sal;
         12         EXIT WHEN emp_cur%NOTFOUND;
         13         DBMS_OUTPUT.PUT_LINE(v_name||'    '||v_sal);
         14      END LOOP;
         15      CLOSE emp_cur;
         16* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur1.sql

        Enter value for inputjob: CLERK
        old   7:     v_job:='&inputjob';
        new   7:     v_job:='CLERK';
        
        Name     Sal
        
        SMITH    800
        ADAMS    1100
        JAMES    950
        MILLER    1300

        PL/SQL procedure successfully completed.

    --例：定义一个游标，输入部门号时，显示该部门所有成员的名字(使用fetch cursor_name bulk collect into提取所有数据)

        scott@ORCL> get /u01/bk/scripts/emp_cur2.sql

          1  DECLARE
          2      v_deptno emp.deptno%type;
          3      type ename_table_type is table of varchar2(10);    --定义PL/SQL表类型
          4      ename_table ename_table_type;                      --定义PL/SQL表变量存放游标数据
          5      cursor emp_cur is
          6      select ename from emp where deptno=v_deptno;
          7  BEGIN
          8      v_deptno:=&inputno;
          9      open emp_cur;
         10      fetch emp_cur bulk collect into ename_table;       --使用bulk collect into提取所有数据
         11      for i in 1..ename_table.count
         12          loop
         13              dbms_output.put_line(ename_table(i));
         14          end loop;
         15      close emp_cur;
         16* END;      

        scott@ORCL> start /u01/bk/scripts/emp_cur2.sql

        Enter value for inputno: 10
        old   8:     v_deptno:=&inputno;
        new   8:     v_deptno:=10;

        CLARK
        KING
        MILLER

    --例：游标属性使用示例(使用%isopen和%rowcount属性)

        scott@ORCL> get /u01/bk/scripts/emp_cur3.sql

          1  DECLARE
          2      v_deptno emp.deptno%type;
          3      type ename_table_type is table of varchar2(10);
          4      ename_table ename_table_type;
          5      cursor emp_cur is
          6     select ename from emp where deptno=v_deptno;
          7  BEGIN
          8      v_deptno:=&inputno;
          9      if not emp_cur%isopen then       --判断游标是否打开，如未打开，则打开游标
         10          open emp_cur;
         11      end if;
         12      fetch emp_cur bulk collect into ename_table;
         13      dbms_output.put_line
         14          ('All record counts from cursor is : '||emp_cur%rowcount);  --使用cursor_name%rowcount 统计游标的记录数
         15      close emp_cur;
         16* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur3.sql

        Enter value for inputno: 20
        old   8:     v_deptno:=&inputno;
        new   8:     v_deptno:=20;
        All record counts from cursor is : 5

    --例:基于游标定义记录变量(该方式大大简化了所需要定义的变量个数)

        scott@ORCL> get /u01/bk/scripts/emp_cur4.sql
        
          1  DECLARE
          2      cursor emp_cur is select ename,sal from emp;
          3      emp_record emp_cur%rowtype;          --定义游标类型记录变量
          4  BEGIN
          5      open emp_cur;
          6      loop
          7          fetch emp_cur into emp_record;
          8          exit when emp_cur%notfound;
          9          dbms_output.put_line
         10             ('Employee Name : '||emp_record.ename ||' ,Sal: '||emp_record.sal);
         11      end loop;
         12      close emp_cur;
         13* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur4.sql

        Employee Name : SMITH ,Sal: 800
        Employee Name : ALLEN ,Sal: 1600
        Employee Name : WARD ,Sal: 1250
                 .......

三、使用游标更新记录       

    通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据
    如果要通过游标更新和删除数据，在定义游标时必须要带有FOR UPDATE子句
        格式：
        CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]
        
        FOR UPDATE ：用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行DML操作
        OF ：游标子查询用到多张表时来确定哪些表要加锁，如未指定，则select语句所引用的全部表将被加锁
        NOWAIT ：指定不等待锁

        使用DML语句操作游标中的当前行时，需要在update或delete语句中引用where current of子句
            UPDATE tbname set col1=.. WHERE CURRENT OF cursor_name;
            DELETE tbname  WHERE CURRENT OF cursor_name;

        --例：使用游标修改所有记录的工资，根据JOB来作不同的修改。

            scott@ORCL> create table tb_emp as select * from emp;
            scott@ORCL> get /u01/bk/scripts/emp_cur6.sql

              1  DECLARE
              2      v_job tb_emp.job%TYPE;
              3      CURSOR emp_cur IS SELECT job FROM tb_emp FOR UPDATE;  --定义时，使用FOR UPDATE
              4  BEGIN
              5      OPEN emp_cur;
              6      LOOP
              7          FETCH emp_cur INTO v_job;
              8          EXIT WHEN emp_cur%NOTFOUND;
              9          CASE  
             10              WHEN v_job='CLERK' THEN
             11                  UPDATE tb_emp SET sal=sal*1.1 WHERE CURRENT OF emp_cur;  --注意，需要使用WHERE CURRENT OF
             12              WHEN v_job='SALESMAN' THEN
             13                  UPDATE tb_emp SET sal=sal*1.08 WHERE CURRENT OF emp_cur;
             14              ELSE
             15                  UPDATE tb_emp SET sal=sal*1.05 WHERE CURRENT OF emp_cur;
             16          END CASE;
             17      END LOOP;
             18      CLOSE emp_cur;
             19* END;

        --例：利用游标删除数据

            scott@ORCL> get /u01/bk/scripts/emp_cur7.sql

              1  DECLARE
              2      v_job tb_emp.job%type;
              3      v_sal tb_emp.sal%type;
              4      cursor emp_cur is select job,sal from tb_emp for update;
              5  BEGIN
              6      open emp_cur;
              7      fetch emp_cur into v_job,v_sal;
              8      while emp_cur%found
              9          loop
             10              if v_sal>3000 then
             11                  delete from tb_emp where current of emp_cur;
             12              end if;
             13              fetch emp_cur into v_job,v_sal;
             14          end loop;
             15      close emp_cur;
             16* END;
             17  /

        --例：使用OF子句对特定的表加共享锁

            scott@ORCL> get /u01/bk/scripts/emp_cur8.sql

              1  DECLARE
              2      cursor emp_cur is
              3          select ename,sal,dname,e.deptno
              4          from tb_emp e join dept d
              5              on e.deptno=d.deptno for update of e.deptno;
              6      emp_record emp_cur%rowtype;
              7  BEGIN
              8      open emp_cur;
              9      loop
             10          fetch emp_cur into emp_record;
             11          exit when emp_cur%notfound;
             12          if emp_record.deptno=20 then
             13              update tb_emp set sal=sal 100 where current of emp_cur;
             14          end if;
             15          dbms_output.put_line('Ename: '||emp_record.ename||
             16                               ',Sal: '||emp_record.sal||
             17                               ',Deptname:'||emp_record.dname);
             18      end loop;
             19      close emp_cur;
             20* END;
             21  /
            Ename: SMITH,Sal: 880,Deptname:RESEARCH
            Ename: ALLEN,Sal: 1728,Deptname:SALES
                       ........

        --例:NOWAIT子句的使用  

            scott@ORCL> get /u01/bk/scripts/emp_cur9.sql
              1  DECLARE
              2      v_ename tb_emp.ename%type;
              3      v_oldsal tb_emp.sal%type;
              4      cursor emp_cur is
              5          select ename,sal from tb_emp for update nowait;  --使用nowait子句指定不等待锁，会给出错误提示
              6  BEGIN
              7      open emp_cur;
              8      loop
              9          fetch emp_cur into v_ename,v_oldsal;
             10          exit when emp_cur%notfound;
             11          if v_oldsal<2000 then
             12              update tb_emp set sal=sal 200 where current of emp_cur;
             13          end if;
             14      end loop;
             15      close emp_cur;
             16* END;

            scott@ORCL> start /u01/bk/scripts/emp_cur9.sql
            DECLARE
            *
            ERROR at line 1:

            ORA-00054: resource busy and acquire with NOWAIT specified

            ORA-06512: at line 5

            ORA-06512: at line 7

       

四、游标FOR循环

    游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标的关闭都是ORACLE系统自动进行的。

    游标FOR循环两种语句格式：

        格式一：
            先在声明部分定义游标，然后在游标FOR循环中引用该游标

            FOR record_name IN cursor_name LOOP
                statement1;
                statement2;
            END LOOP;

    --例：先定义游标然后使用for循环逐个显示记录

        DECLARE
            v_job emp.job%TYPE;
            CURSOR emp_cur IS SELECT ename,sal FROM emp WHERE job=v_job;
        BEGIN
            v_job:='&inputjob';
            DBMS_OUTPUT.PUT_LINE('NO.     Name       Sal');
            FOR emp_record IN emp_cur LOOP
                DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT||'    '||
                emp_record.ename||'    '||emp_record.sal);
            END LOOP;
        END;
        /

        scott@ORCL> start /u01/bk/scripts/emp_cur10.sql
        Enter value for inputjob: SALESMAN
        old   5:                        v_job:='&inputjob';
        new   5:                        v_job:='SALESMAN';
        NO.     Name       Sal
        1    ALLEN    1600
        2    WARD    1250
        3    MARTIN    1250
        4    TURNER    1500

        
        格式二：
            在FOR循环中直接引用子查询，隐式定义游标

            FOR record_name IN subquery LOOP
                statement;
            END LOOP;

    --例：直接在游标for循环中使用子查询来逐个显示记录

        DECLARE
            v_job emp.job%TYPE;
        BEGIN
            v_job:='&inputjob';
            DBMS_OUTPUT.PUT_LINE('Name     Sal');
            FOR emp_record IN (SELECT ename,sal FROM emp WHERE job=v_job) LOOP
                DBMS_OUTPUT.PUT_LINE(emp_record.ename||'    '
                    ||emp_record.sal);
            END LOOP;
        END;
        /
 

五、参数游标

    参数游标是指带有参数的游标。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。

    定义参数游标：
        CURSOR cursor_name (para_name1 datatype [,para_name2 datatype ...])
        IS select_statement;

    注：datatype 只指定数据类型即可，不能指定参数的长度、精度、刻度

    打开参数游标：
        OPEN cursor_name [(vlaues)]
        参数个数、类型必须与定义时的形参相匹配。
        对于定义的参数游标，一定要在游标子查询的where子句中指定定义的参数，否则将使得参数游标失去意义
        
    例：用部门编号deptno作形参，显示每个人的姓名和工资

    scott@ORCL> get /u01/bk/scripts/emp_cur5.sql

      1  DECLARE
      2      v_deptno emp.deptno%type;
      3      cursor emp_cur(v_deptno emp.deptno%type) is    --定义游标时指定了参数v_deptno及类型
      4     select ename,sal from emp where deptno=v_deptno;   --必须在where子句中指定定义的参数
      5      emp_record emp_cur%rowtype;
      6  BEGIN
      7      v_deptno:=&inputno;
      8      open emp_cur(v_deptno);
      9      loop
     10          fetch emp_cur into emp_record;
     11          exit when emp_cur%notfound;
     12          dbms_output.put_line
     13               ('Employe Name is :'||emp_record.ename||' ,Sal:'||emp_record.sal);
     14      end loop;
     15      close emp_cur;
     16* END;
     17  /

    Enter value for inputno: 10
    old   7:     v_deptno:=&inputno;
    new   7:     v_deptno:=10;
    Employe Name is :CLARK ,Sal:2450
    Employe Name is :KING ,Sal:5000
    Employe Name is :MILLER ,Sal:1300

    
六、游标变量

    简言之，其一是一个游标，其次则是一个变量，因此称之为游标变量，可以用来存储不同的游标

    对于游标变量的使用，在打开游标变量时指定其对应的select语句

    1.游标变量的使用步骤

        a.定义REF CURSOR 类型和游标变量
            TYPE ref_type_name IS REF CURSOR [RETURN return_type];   --必须先定义REF CURSOR类型
            cursor_variable ref_type_name;                           --接下来再定义游标变量
            
            ref_type_name:   指定自定义的类型名
            RETURN:          指定REF CURSOR返回结果的数据类型
            cursor_variable: 定义游标变量的名字

            注：若指定RETURN子句，其数据类型必须是记录类型，此外，不能在包内定义游标变量

        b.打开游标
            在打开游标时必须指定其对应的select语句，一旦打开游标变量则对应的select结果集将存放到游标变量中
            OPEN cursor_variable FOR select_statement;

        c.提取数据
            提取数据与普通的显式游标提取数据的方法类似
            FETCH cursor_variable INTO variable1,...variable2 ;   --提取单行数据，需要配合循环语句来使用
            FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];    --提取多行数据，collect为集合变量

        d.关闭游标变量
            CLOSE cursor_vairable;

    2.游标变量使用的例子

        --例.根据部门名称显示该部门的所有雇员(定义REF CURSOR时不指定RETURN子句)

            scott@ORCL> get /u01/bk/scripts/emp_cur12.sql

              1  DECLARE
              2      type emp_cur_type is ref cursor;    --定义游标类型为ref cursor
              3      emp_cur emp_cur_type;               --定义游标变量为emp_cur
              4      emp_record emp%rowtype;             --定义游标变量记录类型为emp_record
              5      v_deptno emp.deptno%type;
              6  BEGIN
              7      v_deptno:=&inputno;
              8      open emp_cur for select * from emp where deptno=v_deptno;
              9      dbms_output.put_line('No    Name');
             10      loop
             11          fetch emp_cur into emp_record;
             12          exit when emp_cur%notfound;
             13          dbms_output.put_line(emp_cur%rowcount||'    '||emp_record.ename);
             14      end loop;
             15      close emp_cur;
             16* END;
             17  /

            Enter value for inputno: 10
            old   7:     v_deptno:=&inputno;
            new   7:     v_deptno:=10;
            No    Name
            1    CLARK
            2    KING
            3    MILLER


        --例:根据部门名称显示该部门的所有雇员名字及薪水(定义REF CURSOR时指定RETURN子句)

            scott@ORCL> get /u01/bk/scripts/emp_cur13.sql

              1  DECLARE
              2      type emp_record_type is record(name varchar2(10),salary number(6,2));  --定义PL/SQL记录变量类型
              3      type emp_cur_type is ref cursor return emp_record_type;  --定义游标类型为ref cursor，且具有返回类型
              4      emp_cur emp_cur_type;                                    --定义游标变量为emp_cur
              5      emp_record emp_record_type;                              --定义类型为emp_record_type记录变量emp_record
              6      v_deptno emp.deptno%type;
              7  BEGIN
              8      v_deptno:=&inputno;
              9      open emp_cur for select ename,sal from emp where deptno=v_deptno;
             10      dbms_output.put_line('Name    Salary');
             11      loop
             12          fetch emp_cur into emp_record;
             13          exit when emp_cur%notfound;
             14          dbms_output.put_line(emp_record.name||'    '||emp_record.salary);
             15      end loop;
             16      close emp_cur;
             17* END;
             18  /

            Enter value for inputno: 10
            old   8:     v_deptno:=&inputno;
            new   8:     v_deptno:=10;
            Name    Salary
            CLARK    2450
            KING    5000
            MILLER    1300

            --如果REF CURSOR指定RETURN子句的数据列与select子句的数据列不一致将收到如下的错误提示信息

            scott@ORCL> start /u01/bk/scripts/emp_cur13.sql

            Enter value for inputno: 10
            old   8:     v_deptno:=&inputno;
            new   8:     v_deptno:=10;
                open emp_cur for select ename,sal,job from emp where deptno=v_deptno;  --多出了一列
                                 *
            ERROR at line 9:
            ORA-06550: line 9, column 22:
            PLS-00382: expression is of wrong type
            ORA-06550: line 9, column 5:
            PL/SQL: SQL Statement ignored          

        --例：游标变量的多次使用

            scott@ORCL> get /u01/bk/scripts/emp_cur14.sql

              1  DECLARE
              2      type cur_type is ref cursor;
              3      scott_cur  cur_type;
              4      v_emp  emp%rowtype;
              5      v_dept dept%rowtype;
              6  BEGIN
              7      open scott_cur for select * from emp where deptno=10;       --使用for select首次打开游标
              8      dbms_output.put_line('No, Name');
              9      loop
             10          fetch scott_cur into v_emp;
             11          exit when scott_cur%notfound;
             12          dbms_output.put_line(scott_cur%rowcount||','||v_emp.ename);
             13      end loop;
             14      open scott_cur for select * from dept where deptno=10;--使用for select 再次打开游标，此次加载了不同数据
             15      dbms_output.put_line('Deptno, Name');
             16      loop
             17          fetch scott_cur into v_dept;
             18          exit when scott_cur%notfound;
             19          dbms_output.put_line(v_dept.deptno||','||v_dept.dname);
             20      end loop;
             21* END;
             22  /

                No, Name
                1,CLARK
                2,KING
                3,MILLER
                Deptno, Name
                10,ACCOUNTING  

转自：http://blog.csdn.net/robinson_0612/article/details/6078622