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

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

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


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,那么也只好用显式游标了。 对于游标一些优化建议      如果能不用游标，尽量不要使用游标      用完用完之后一定要关闭和释放      尽量不要在大量数据上定义游标      尽量不要使用游标上更新数据      尽量不要使用insensitive, static和keyset这些参数定义游标      如果可以，尽量使用FAST_FORWARD关键字定义游标      如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数  总结      本文从游标的基本概念，到生命周期来谈游标。游标是非常邪恶的一种存在，使用游标经常会比使用面向集合的方法慢2-3倍，当游标定义在大数据量时，这个比例还会增加。如果可能，尽量使用while,子查询，临时表，函数，表变量等来替代游标，记住，游标永远只是你最后无奈之下的选择，而不是首选。