游标属性介绍.txt

 

上次提到了sql游标的四个属性：sql%found、sql%notfound、sql%rowcount、sql%isopen。
这些属性是针对游标中的一种——————隐式游标  来讲的。
隐式游标不需要显式地打开游标、抽取数据和关闭游标，因此也就不存在“游标名称”，其属性的表示方式都是sql%xxxx。
隐式游标会在执行dml语句、使用select  xxxx 【bulk collect】into ……语句时自动声明和管理。

更一般地说，游标可以分为显式游标、隐式游标和动态游标。

%found 反映游标最后一次数据提取操作的状态。如果有返回行，结果为true；没有返回行，结果为false；
游标没打开就试图访问该属性，报错invalid_cursor异常。

%notfound 与%found相反，可以用于退出（如：exit when  cursor_name%notfound）

%rowcount 返回游标中取出的记录数量；游标没打开就试图访问该属性，报错invalid_cursor异常。

%isopen  如果是一个打开的游标，返回true；否则返回false

%bulk_rowcount 是用在forall语句中的，返回每次dml操作的行数，在语义上相当于关联数组。

%bulk_exceptions 用在forall语句中，返回每次dml语句执行时可能发生的异常。语义上相当于关联数组。

1.显式游标

对于显式游标，需要进行声明、打开、提取、关闭这几个步骤。
在游标打开后，对数据所作的修改将不会影响到游标中的数据。如：

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONESa     MANAGER    7839 1997-3-24     3795.00               20
 7782 CLARK      MANAGER    7839 1982-6-4      3470.00               10
 7788 SCOTT      ANALYST    7566 1998-4-11     3820.00               30
 7839 KING       PRESIDENT       1992-11-9     5820.00               30
 7876 ADAMS      CLERK      7788 2003-5-14     1920.00               20
 7934 MILLER     CLERK      7782 1983-1-18     2320.00               10

6 rows selected

SQL>
SQL> declare
  2    cursor cur_a is select * from emp;
  3    type emp_type is table of emp%rowtype;
  4    emp_lst emp_type;
  5  begin
  6    open cur_a;
  7    update emp set ename=ename||'a';--在打开游标后，对原表数据进行更新
  8    fetch cur_a bulk collect into emp_lst;--然后提取数据
  9    for i in emp_lst.first..emp_lst.last loop
 10      dbms_output.put_line(emp_lst(i).ename);
 11    end loop;
 12    close cur_a;
 13    commit;
 14  end;
 15  /

JONESa
CLARK
SCOTT
KING
ADAMS
MILLER

PL/SQL procedure successfully completed

根据以上结果，游标所提取的数据是open游标时的数据。
SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONESaa    MANAGER    7839 1997-3-24     3795.00               20
 7782 CLARKa     MANAGER    7839 1982-6-4      3470.00               10
 7788 SCOTTa     ANALYST    7566 1998-4-11     3820.00               30
 7839 KINGa      PRESIDENT       1992-11-9     5820.00               30
 7876 ADAMSa     CLERK      7788 2003-5-14     1920.00               20
 7934 MILLERa    CLERK      7782 1983-1-18     2320.00               10

6 rows selected

在提取时，提取超过最后一行后，不会报出异常，也不会将变量值设置为null。因此，永远不要用监测into中变量值的方法来确定游标的fetch操作是否成功；
而应该使用%found或%notfound判断。

关闭游标需要注意：
1）应该在程序结束时关闭已经打开的游标，否则可能出现内存泄露。
pl/sql会在一个函数调用、过程调用或匿名块调用结束后检查打开的游标并隐式关闭，但其开销不容忽视，且ref cursor不会被隐式关闭。

2）包级别声明的游标，在块或程序中打开时，需要显示关闭这个游标或等到会话结束自动关闭，否则将一直打开

3）对已经关闭的游标再进行关闭操作，会报错invalid_cursor异常

4）打开游标过多，超过open_cursors初始化参数，会报错ora-01000

 

关于参数游标：
对于不同条件形成不同游标，可以使用参数游标。并且参数可以出现在select列表中。
参数游标的使用实例如下：
SQL> declare
  2    cursor cur_a(deptno_lst emp.deptno%type)
  3    is
  4    select * from emp where deptno=deptno_lst;
  5 
  6    type emp_type is table of emp%rowtype;
  7    emp_lst emp_type;
  8  begin
  9    open cur_a('10');
 10    fetch cur_a bulk collect into emp_lst;
 11    for i in emp_lst.first..emp_lst.last loop
 12      dbms_output.put_line(emp_lst(i).ename);
 13    end loop;
 14    close cur_a;
 15  end;
 16  /

CLARKaa
MILLERaa

PL/SQL procedure successfully completed

注意：声明部分和open时需要指定参数名，fetch时不需要再重复参数名了。
如果声明部分声明了游标中参数的默认值，fetch时不指定参数值，就使用默认值。

 

2.select for update

声明的游标可以使用for update选项，避免打开游标后其他会话对数据进行更改；
更细粒度地，可以使用for update of column_name，防止其他回话更新特定列；
使用nowait选项，在其他会话已经锁定数据时不发生等待，或使用wait xx表示等待的最大秒数，
若不指定该选项，则等待直到初始化参数distributed_lock_timeout的值。

在带有for update的游标打开后，关闭前不要多次进行提交、回滚操作，否则报错ora-01002.

使用where current of子句，可以指定对最后取出来的数据行指定update、delete操作。避免了重复编码来定位记录的同一条件。

 

3.游标变量和ref cursor

游标变量是一个指向或引用底层游标的变量。
可以用于打开任何一个查询，甚至在一个程序中执行多个不同查询。
游标变量可以在不同pl/sql程序间传递查询的结果集。

使用游标变量的好处：
1）把一个游标变量在不同时刻关联不同的查询语句
2）把游标变量作为参数传递给一个过程或者函数，相当于通过传递记录集的引用来共享游标的结果集
3）使用pl/sql的静态游标的完整功能集
4）把一个游标的内容赋值给另一个游标变量。

一般用法：
declare
  type var_cur_type is ref cursor;
  var_cur var_cur_type;  --这两个操作也可以用"var_cur sys_refcursor;"代替
begin
  open var_cur for ……
  ……
  close var_cur;
end;

上面是弱类型的游标变量，没有return子句，
如果type ……语句中有return 子句，就是强类型。

4.关于游标的%bulk_rowcount、%bulk_exceptions属性，以下举一个例子慢慢体会，不详细说明了。
可以参考：
http://space.itpub.net/24908207/viewspace-683730
http://wenku.baidu.com/view/a0ba5fea81c758f5f61f676a.html
http://hwhuang.iteye.com/blog/537087

SQL> declare
  2    cursor cur_a
  3    is
  4    select empno,ename,deptno from emp;
  5 
  6    type empno_typ is table of emp.empno%type;
  7    type ename_typ is table of emp.ename%type;
  8    type deptno_typ is table of emp.deptno%type;
  9    empno_lst empno_typ;
 10    ename_lst ename_typ;
 11    deptno_lst deptno_typ;
 12 
 13  begin
 14 
 15    open cur_a;
 16    loop
 17    fetch cur_a bulk collect into empno_lst,ename_lst,deptno_lst limit 2;
 18    exit when cur_a%notfound;
 19    forall i in empno_lst.first..empno_lst.last save exceptions --必须有save exceptions，才能调用bulk_exceptions
 20      insert into emp1(empno,ename,deptno)
 21      values(empno_lst(i),ename_lst(i),deptno_lst(i));
 22    end loop;
 23 
 24    dbms_output.put_line('cur_a%rowcount:'||cur_a%rowcount);
 25    dbms_output.put_line('cur_a%bulk_rowcount(1):'||cur_a%bulk_rowcount(1));
 26   
 27    dbms_output.put_line('sql%bulk_exceptions.count:'||sql%bulk_exceptions.count);--除count之外还有error_code、error_index属性
 28    close cur_a;
 29  end;
 30  /

cur_a%rowcount:6
cur_a%bulk_rowcount(1):6
sql%bulk_exceptions.count:0

PL/SQL procedure successfully completed

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-768743/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-768743/