PL/SQL游标(原创)

游标的相关概念及特性
定义
映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。
游标的分类
显示游标:即用户自定义游标，专门用于处理select语句返回的多行数据
隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句
游标使用的一般过程：
显示游标：声明, 打开, 读取, 关闭
隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的
显示游标的过程描述
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.显示游标的个属性
cursor_name%ISOPEN               游标是否打开  
cursor_name%FOUND                最近的FETCH是否提取到数据
cursor_name%NOTFOUND        最近的FETCH是否没有提取到数据
cursor_name%ROWCOUNT       返回到目前为止，已经从游标缓冲区中提取到数据的行数

显示游标应用示例

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

 declare
  v_job emp.job%type;       --定义用于存放游标提取的数据的变量
  v_name emp.ename%type;
  v_sal emp.sal%type;
  cursor emp_cur is select ename,sal from emp where job = v_job;
begin
  v_job := '&inputjob';
  dbms_output.put_line('NAME     SAL');
  open emp_cur;
  loop
    fetch emp_cur into v_name,v_sal;

     exit when emp_cur%notfound;
    dbms_output.put_line(v_name || '     ' || v_sal);
    end loop;
  close emp_cur;
end;

/

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
MILLER     1300

PL/SQL procedure successfully completed.

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

SQL> declare
  2    v_dept emp.deptno%type;
  3    type emp_table_type is table of varchar2(10);    --定义PL/SQL表类型
  4    emp_table emp_table_type;    --定义PL/SQL表变量存放游标数据
  5    cursor emp_cur is select ename from emp where deptno = v_dept;
  6  begin
  7     v_dept := &intputno;
  8     open emp_cur ;
  9     fetch emp_cur bulk collect into emp_table;    --使用bulk collect into提取所有数据
 10     for i in 1..emp_table.count
 11       loop
 12         dbms_output.put_line(emp_table(i));
 13       end loop;
 14     close emp_cur;
 15  end;
 16  /  
Enter value for intputno: 10
old   7:    v_dept := &intputno;
new   7:    v_dept := 10;
CLARK
KING
MILLER
PL/SQL procedure successfully completed.

例：游标属性使用示例(使用%isopen和%rowcount属性)
SQL> declare
  2    v_dept emp.deptno%type;
  3    type emp_table_type is table of varchar2(10);
  4    emp_table emp_table_type;
  5    cursor emp_cur is select ename from emp where deptno = v_dept;
  6  begin
  7    v_dept := &intputno;
  8    if not emp_cur%isopen then          --判断游标是否打开，如未打开，则打开游标
  9      open emp_cur;
 10    end if;
 11    fetch emp_cur bulk collect into emp_table;
 12    dbms_output.put_line('The amount of record counts from cursor is ' || emp_cur%rowcount);       --使用cursor_name%rowcount 统计游标的记录数
 13    close emp_cur;
 14  end;
 15  /
Enter value for intputno: 10
old   7:   v_dept := &intputno;
new   7:   v_dept := 10;
The amount of record counts from cursor is 3

PL/SQL procedure successfully completed.

例:基于游标定义记录变量(该方式大大简化了所需要定义的变量个数)
SQL> 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('Employee Nmae: ' || emp_record.ename || '.The salary: ' || emp_record.sal);
 10    end loop;
 11    close emp_cur;
 12  end;
 13  /
Employee Nmae: SMITH.The salary: 800
Employee Nmae: ALLEN.The salary: 1600
...................................................................
PL/SQL procedure successfully completed.
使用游标更新记录      
通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据。如果要通过游标更新和删除数据，在定义游标时必须要带有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来作不同的修改。
SQL>   declare
  2     v_job tb_emp.job%type;
  3     cursor emp_cur is select job from tb_emp for update;
  4   begin
  5     open emp_cur;
  6     loop
  7       fetch emp_cur into v_job;
  8       exit when emp_cur%notfound ;
  9    --   exit when emp_cur%notfound ;
 10       case v_job
 11          when 'CLERK' THEN
 12            update tb_emp set sal = 1.1 * sal where current of emp_cur;
 13          when 'SALESMAN' then
 14            update tb_emp set sal = 1.2 * sal where current of emp_cur;
 15          else
 16            update tb_emp set sal = 1.3 * sal where current of emp_cur;
 17          end case;
 18      end loop;
 19      close emp_cur;
 20*  end;

/
PL/SQL procedure successfully completed.

例：利用游标删除数据

SQL> 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    while emp_cur%found loop
  8      fetch emp_cur into v_job,v_sal;
  9        if v_sal > 3000 then
 10          delete tb_emp where current of emp_cur;
 11        end if;
 12    end loop;
 13    close emp_cur;
 14  end;
 15  /
PL/SQL procedure successfully completed.

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

SQL> declare
  2    cursor emp_cur is
  3    select e.ename,e.sal,d.dname,e.deptno
  4    from tb_emp e ,dept d
  5    where 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 = 1.1 * sal where current of emp_cur;
 14        end if;
 15      dbms_output.put_line('Ename: ' || emp_record.ename || ' Sal: ' || emp_record.sal || ',Deptname: ' || emp_record.dname);
 16      end loop;
 17      close emp_cur;
 18  end;
 19  /
Ename: SMITH Sal: 800,Deptname: RESEARCH
Ename: ALLEN Sal: 1600,Deptname: SALES

..............................................

例:NOWAIT子句的使用

declare
  v_old_sal emp.sal%type;
  cursor emp_cur is select sal from tb_emp for update nowait;
begin
  open emp_cur;
  loop
    fetch emp_cur into v_old_sal;

    exit when emp_cur%notfound;
    if v_old_sal < 2000 then
      update tb_emp set sal = sal + 200 where current of emp_cur;
    end if;
  end loop;
  close emp_cur;
end;

 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循环中直接引用子查询，隐式定义游标
    FOR record_name IN subquery LOOP
        statement;

例：定义游标并使用for循环逐个显示记录

SQL> declare
  2    v_job emp.job%type;
  3    cursor emp_cur is select ename,sal from emp where job = v_job;
  4  begin
  5    v_job := '&inputjob';
  6    dbms_output.put_line('No.     Name.     Sal');
  7    for emp_record in emp_cur loop
  8       dbms_output.put_line(emp_cur%rowcount || '     ' || emp_record.ename || '     ' || emp_record.sal);
  9    end loop;
 10    end;
 11    /
Enter value for inputjob: CLERK
old   5:   v_job := '&inputjob';
new   5:   v_job := 'CLERK';
No.     Name.     Sal
1     SMITH     800
2     ADAMS     1100
3     JAMES     950
4     MILLER     1300

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

SQL> declare
  2    v_job emp.job%type;
  3  begin
  4    v_job := '&intput';
  5    dbms_output.put_line('Name     Sal');
  6    for emp_record in (select ename,sal from emp where job = v_job) loop
  7      dbms_output.put_line(emp_record.ename || '     ' || emp_record.sal);
  8    end loop;
  9    end;
 10   /
Enter value for intput: CLERK
old   4:   v_job := '&intput';
new   4:   v_job := 'CLERK';
Name     Sal
SMITH     800
ADAMS     1100
JAMES     950
MILLER     1300
PL/SQL procedure successfully completed.

参数游标
参数游标是指带有参数的游标，与存储过程和函数相似，可以将参数传递给游标并在查询中使用。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。这对于处理在某种条件下打开游标的情况非常有用。定义参数游标：

cursor cursor_name[(parameter[,parameter],...)] is select_statement;
定义参数的语法如下：
parameter_name [in] data_type[{:=|default} value]

注：datatype 只指定数据类型即可，不能指定参数的长度、精度、刻度
打开参数游标：
OPEN cursor_name [(vlaues)]
参数个数、类型必须与定义时的形参相匹配。对于定义的参数游标，一定要在游标子查询的where子句中指定定义的参数，否则将使得参数游标失去意义

SQL> declare
  2    cursor c_dept is select * from dept;
  3    cursor c_emp(p_dept varchar2) is select ename,sal from emp where deptno = p_dept;
  4    r_dept dept%rowtype;
  5    v_ename emp.ename%type;
  6    v_sal emp.sal%type;
  7    v_tot_sal emp.sal%type;
begin
  open c_dept;
    fetch c_dept into r_dept;
  loop
    fetch c_dept into r_dept;

    exit when c_dept%notfound;

    open c_emp(r_dept.deptno);
    dbms_output.put_line('Department:' || r_dept.dname);
      exit when c_emp%notfound;
    v_tot_sal := 0;
    open c_emp(r_dept.deptno);
    loop
     
 18        fetch c_emp into v_ename,v_sal;

             exit when c_emp%notfound;

19        dbms_output.put_line('Name:' || v_ename || ',Sal:' || v_sal);
      v_tot_sal := v_tot_sal + v_sal;
      end loop;
 22        close c_emp;
 23      dbms_output.put_line('Total sal for dept:' || v_tot_sal);
 24      end loop;
 25      close c_dept;
 26  end;
 27  /
Department:ACCOUNTING
Name:CLARK,Sal:2450
Name:KING,Sal:5000
Name:MILLER,Sal:1300
Name:MILLER,Sal:1300
Total sal for dept:10050
Department:RESEARCH

......................................
隐式游标的定义及其属性
定义
隐式游标则由则由系统自动定义，非显示定义游标的DML语句即被赋予隐式游标属性。其过程由oracle控制，完全自动化。隐式游标的名称是SQL，不能对 SQL游标显式地执行OPEN,FETCH,CLOSE语句。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的 流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个处理操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
属性
类似于显示游标，隐式游标同样具有四种属性，只不过隐式游标以SQL%开头，而显示游标以Cursor_name%开头。通过SQL%总是只能访问前一个DML操作或单行SELECT操作的游标属性，用于判断DML执行的状态和结果，进而控制程序的流程
SQL%ISOPEN：游标是否打开。当执行select into ,insert update,delete时，Oracle会隐含地打开游标，且在该语句执行完毕或隐含地关闭游标，因为是隐式游标，故SQL%ISOPEN总是false 
SQL%FOUND：判断SQL语句是否成功执行。当有作用行时则成功执行为true，否则为false，未执行sql时为NULL。
SQL%NOTFOUND：判断SQL语句是否成功执行。当有作用行时否其值为false，否则其值为true，未执行sql时为NULL。
SQL%ROWCOUNT：在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL。执行了DML或者SELECT语句后，SQL%ROWCOUNT的值将为该语句作用的行数。

Notice: SQL%ROWCOUNT与cursor_name%rowcount是有所不同的，sql%rowcount返回的是上一次sql所处理的行数，cursor_name%rowcount返回的是,当前cursor所处理的行数

对于SELECT INTO语句处理的结果包括三种种情况

a.查询结果返回单行，SELECT INTO被成功执行
b.查询结果没有返回行，PL/SQL将抛出no_data_found异常
c.查询结果返回多行，PL/SQL将抛出too_many_rows 异常
对于上述两种异常发生时，类似于普通异常处理，程序控制权转移到异常处理部分(如没有异常处理则程序中断)。对于异常被激后发，SQL游标的四个属性在此将不可使用，如下面的例子。

SQL> declare
  2    v_dept emp.deptno%type := &no;
  3  begin
  4    if sql%rowcount >= 0 then    --判断执行前的SQL%ROWCOUNT属性
  5      dbms_output.put_line('SQL%ROWCOUNT value is ' || SQL%ROWCOUNT || 'before updated');
  6    else
  7      dbms_output.put_line('SQL%ROWCOUNT value is NULL before updated');
  8    end if;
  9    update emp set sal = sal + 300 where deptno = v_dept;
 10    if sql%found then     --判断SQL%FOUND的属性
 11      dbms_output.put_line('SQL executes successfully');
 12      dbms_output.put_line('SQL%FOUND is True');
 13    end if;
 14    if sql%notfound then   --判断SQL%NOTFOUND的属性
 15      dbms_output.put_line('SQL%NOUTFOUND is True');
 16    else
  end if;dbms_output.put_line('SQL%NOTFOUND is false');
    dbms_output.put_line('SQL%NOTFOUND is false');
  end if;
  if sql%isopen then     --判断SQL%ISOPEN属性
    dbms_output.put_line('SQL%ISOPEN is True');
  else
    dbms_output.put_line('SQL%ISOPEN is False');
  end if;
  dbms_output.put_line('The row updated is :' || SQL%ROWCOUNT);    --判断执行后SQL%ROWCOUNT的属性
 25  end;
 26  /

--下面是成功更新后的结果
Enter value for no: 10
old   2:   v_dept emp.deptno%type := &no;
new   2:   v_dept emp.deptno%type := 10;
SQL%ROWCOUNT value is NULL before updated
SQL executes successfully
SQL%FOUND is True
SQL%NOTFOUND is false
SQL%ISOPEN is False
The row updated is :3
PL/SQL procedure successfully completed.

--下面是更新失败后的结果
SQL> /
Enter value for no: 80
old   2:   v_dept emp.deptno%type := &no;
new   2:   v_dept emp.deptno%type := 80;
SQL%ROWCOUNT value is NULL before updated
SQL%NOUTFOUND is True
SQL%ISOPEN is False
The row updated is :0
PL/SQL procedure successfully completed.

SQL%BULK_ROWCOUNT：Composite attribute that is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement. Restriction on SQL%BULK_ROWCOUNT ，You cannot assign the value of SQL%BULK_ROWCOUNT(index) to another collection.

Note:uses SQL%BULK_ROWCOUNT to show how many rows each DELETE statement in the FORALL  statement deleted and SQL%ROWCOUNT to show the total number of rows deleted.
SQL%BULK_EXCEPTIONS
Composite attribute that is like an associative array of information about the DML statements that failed during the most recently run FORALL statement. SQL%BULK_EXCEPTIONS.COUNT is the number of DML statements that failed. If SQL%BULK_EXCEPTIONS.COUNT is not zero, then for each index value i from 1 through SQL%BULK_EXCEPTIONS.COUNT:SQL%BULK_EXCEPTIONS(i).ERROR_INDEX is the number of the DML statement that failed.SQL%BULK_EXCEPTIONS(i).ERROR_CODE is the Oracle Database error code for the failure.
For example, if a FORALL SAVE EXCEPTIONS statement runs 100 DML statements, and the tenth and sixty-fourth ones fail with error codes ORA-12899 and ORA-19278, respectively, then:
SQL%BULK_EXCEPTIONS.COUNT = 2
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278

关于这两个游标的使用例子请参见笔者的这篇文章

http://czmmiao.iteye.com/blog/1825825

 

参考至:http://blog.csdn.net/robinson_0612/article/details/6078622
               http://blog.csdn.net/robinson_0612/article/details/6092066

               http://hi.baidu.com/graceyan/item/a750fe000c2a30ea349902aa

               http://ftc007.blog.163.com/blog/static/1965716502012315104852764/

               http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i49099

               http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/sql_cursor.htm#i36237

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]