PL/SQL中用到的几种不同类型的游标写法(转)

java调用存储过程简单例子：

http://www.blogjava.net/TrampEagle/archive/2005/12/13/23605.html

如何选择游标：

http://space.itpub.net/batch.viewlink.php?itemid=176913

 

PL/SQL里的游标可以分为显式和隐式两种，而隐式有分为select into隐式游标和for .. in 隐式游标两种。所以，我们可以认为，有3种游标用法：
A. 显式游标
B. select into隐式游标
C. for .. in 隐式游标

 A. 显式游标
普通显式游标，指的是通过定义获得游标，并通过open,fetch,close的等方法来操作
游标[php]
declare
cursor c is select tname from tab ;
l_tname varchar2(64);
begin
open c ;
loop
        fetch c into l_tname ;
        exit when c%notfound ;
        dbms_output.put_line(l_tname);
end loop;
close c;
end;
/
..
..
[/php]


Bulk Collect的 显式游标

[php]
declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin
open c ;
fetch c bulk collect into l_tname_array ;
for i in 1 .. l_tname_array.count loop
        dbms_output.put_line(l_tname_array(i) );
end loop;
close c;
end;
/


..
..
[/php]

Bulk Collect的 显式游标 + limit

[php]
declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin
open c ;
loop
fetch c bulk collect into  l_tname_array limit 10 ;
exit when c%notfound ;
        for i in 1 .. l_tname_array.count loop
                dbms_output.put_line(l_tname_array(i) );
        end loop;
end loop;
close c;
end;
/


..
..
[/php]

 

 

隐式游标相对于显式游标而言，指的是不需要事先Declare,也无须用open,fetch,close的等方法来操作，而是通过其它的方式来操作游标


B. select into隐式游标

[php]
declare
l_tname varchar2(100);
begin
select tname into l_tname from tab where rownum = 1 ;
dbms_output.put_line(l_tname);
end;
/

..
..

[/php]

动态SQL 的 select into隐式游标
[php]
declare
l_tname varchar2(100);
l_table_name varchar2(100);
l_sql varchar2(200);
begin
l_table_name := 'TAB' ;
l_sql := 'select tname from '||l_table_name ||' where rownum = 1 ' ;
execute immediate l_sql into l_tname;
    dbms_output.put_line(l_tname );
end;
/

..
..

[/php]


动态SQL 的 select into隐式游标 + Bulk Collect
[php]
declare
l_tname_array dbms_sql.varchar2_table;
l_table_name varchar2(100);
l_sql varchar2(200);
begin
l_table_name := 'TAB' ;
l_sql := 'select tname from '||l_table_name ;
execute immediate l_sql bulk collect into l_tname_array;
for i in 1 .. l_tname_array.count loop
        dbms_output.put_line(l_tname_array(i) );
end loop;
end;
/

..
..

[/php]

 

 

C. for .. in 隐式游标
for .. in 隐式游标通过loop的开始于结束来控制cursor的Open与Close.

[php]
begin
for c in (select tname from tab) loop
dbms_output.put_line(c.tname);
end loop;
end;
/
..
..
[/php]

 

 2.三种用法的优劣
A. 显式游标
优点：
·可以用于Bulk Collect的批量处理句式以提高性能
·可以用于动态SQL的游标处理
缺点：
·麻烦，需要定义，打开，Fetch,Close一堆代码，增加代码复杂度，从而增加出错的可能性

B. select into隐式游标B
优点
·代码量最少
·可以自动Detect 返回数据超过一行或少于一行的错误
·可以使用Bulk Collect 批量处理，但是无法使用Limit 关键字
缺点
·如果不使用Bulk Collect 批量处理，仅仅只能用于返回数据正好一行的情况，无法使用于返回数据超过一行或少于一行的环境
·使用Bulk Collect 批量处理时，无法使用limit 关键字，无法处理返回行数太多的情况（不好处理，容易造成PGA过大）

C. for .. in 隐式游标
优点
·代码量远少于显式游标
·代码可读性优于显式游标
·代码的出错可能性也小于显式游标
缺点：
·无法用于动态SQL的游标处理
·在返回行数超过10行的情况下，性能明显不如使用Bulk Collect的显式游标

在性能对比方面，除非是使用了Bulk Collect,否则，三种方式没有明显性能差距。

 

3。具体的选择
·在返回数据为一行的情况下，尽量使用select into 的隐式游标

·返回0行或者<几十行的情况下，使用for .. in 隐式游标

·或者在返回行稍多，但是不关心Fetch性能的情况下，也可考虑用for .. in 隐式游标

·返回10行-100行（一个很随意选择的经验值，可以自己根据情况设定），而且关心Fetch性能的情况下，可以使用select into 的隐式游标+Bulk collect ,在获得性能提升的情况下，代码量也不会增加太多。

·返回行数很多，> 100, 应选用显示游标+Bulk collect ，以获得较高的Fetch 性能，同时不至于使用太大的PGA内存。

·如国使用动态SQL, 则根据select list (column list) 是否固定，如果固定，仍然可以考虑使用select into 的隐式游标+动态SQL的方式。当然，仍然需要考虑返回行数的问题。

·如果select list (column list) 不固定，则只好使用显式游标

·或者动态语句返回行数太多，必须用limit,那么也只好用显式游标了