感谢itpub的rollingpig 分享他的文章

PL/SQL 中如何正确选择游标类型

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

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


PL/SQL里的游标可以分为显式和隐式两种，而隐式有分为select into隐式游标和for .. in 隐式游标两种。所以，我们可以认为，有3种游标用法：
A. 显式游标
B. select into隐式游标
C. for .. in 隐式游标
A. 显式游标
普通显式游标，指的是通过定义获得游标，并通过open,fetch,close的等方法来操作
游标

  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的 显式游标

  PHP 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;

/

Bulk Collect的 显式游标 + limit

  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;

---

  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;

---

  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,那么也只好用显式游标了。