本文并不包括太多的实际运行/性能测试,有兴趣的话,大家可以根据示例自己测试。
PL/SQL里的游标可以分为显式和隐式两种,而隐式有分为select into隐式游标和for .. in 隐式游标两种。所以,我们可以认为,有3种游标用法:
A. 显式游标
B. select into隐式游标
C. for .. in 隐式游标
A. 显式游标
普通显式游标,指的是通过定义获得游标,并通过open,fetch,close的等方法来操作
游标
Bulk Collect的 显式游标PHP code: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的 显式游标 + limitPHP code: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 code: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隐式游标PHP code: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隐式游标
PHP code: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; / .. ..
动态SQL 的 select into隐式游标 + Bulk Collect
PHP code: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.PHP code: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行或者<几十行的情况下,使用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,那么也只好用显式游标了。