ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 游标属性介绍.txt

游标属性介绍.txt

原创 Linux操作系统 作者:to_be_dba 时间:2013-08-18 14:03:35 0 删除 编辑

 

上次提到了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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    396421