PL/SQL正确选择游标类型

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

A. 显式游标

B. select into隐式游标

C. for .. in 隐式游标

如何正确的选择使用哪种游标，将对你的程序的性能，可读性，代码量等产生重大影响……

--By RollingPig,

本文简单的列举了PL/SQL中用到的几种不同类型的游标写法，并简单对比了不同游标写法的优缺点，同时给出了一个选择的基本原则。

本文并不包括太多的实际运行/性能测试，有兴趣的话，大家可以根据示例自己测试。

1.三种游标形式的简单例子

A. 显式游标

普通显式游标，指的是通过定义获得游标，并通过open,fetch,close的等方法来操作游标

代码:

 

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;



/

 

Bulk Collect的 显式游标

 

代码:

 

 

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;



/

 

Bulk Collect的 显式游标 + limit

代码:

 

 

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;



/

 

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

B. select into隐式游标

代码:

 

 

declare



l_tname varchar2(100);



begin



select tname into l_tname from tab where rownum = 1 ;



dbms_output.put_line(l_tname);



end;



/

 

动态SQL 的 select into隐式游标

代码:

 

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;

for i in 1 .. l_tname_array.count loop

dbms_output.put_line(l_tname_array(i) );

end loop;

end;

/

 

动态SQL 的 select into隐式游标 + Bulk Collect

代码:

 

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;

/

 

C. for .. in 隐式游标

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

代码:

 

begin

for c in (select tname from tab) loop

dbms_output.put_line(c.tname);

end loop;

end;

/

 

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行或者 100, 应选用显示游标+Bulk collect ，以获得较高的Fetch 性能，同时不至于使用太大的PGA内存。

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

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

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