PL/SQL-->游标

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