PL/SQL里的游標可以分為顯式和隱式兩種，而隱式有分為select into隱式游標和for .. in 隱式游標兩種。所以，我們可以認為，有3種游標用法:

A. 顯式游標

B. select into隱式游標

C. for .. in 隱式游標

如何正確的選擇使用哪種游標，將對你的程序的性能，可讀性，代碼量等產生重大影響……

--By RollingPig,

本文簡單的列舉了PL/SQL中用到的幾種不同類型的游標寫法，并簡單對比了不同游標寫法的優缺點，同時給出了一個選擇的基本原則。

本文并不包括太多的實際運行/性能測試，有興趣的話，大家可以根據示例自己測試。

1.三種游標形式的簡單例子

A. 顯式游標

普通顯式游標，指的是通過定義獲得游標，并通過open,fetch,close的等方法來操作游標

代碼:

declarecursor 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的 顯式游標

代碼:

declarecursor c is select tname from tab ;l_tname_array dbms_sql.varchar2_table;beginopen 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

代碼:

declarecursor c is select tname from tab ;l_tname_array dbms_sql.varchar2_table;beginopen c ;loopfetch 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隱式游標

代碼:

declarel_tname varchar2(100);beginselect tname into l_tname from tab where rownum = 1 ;dbms_output.put_line(l_tname);end;/....

動態SQL 的 select into隱式游標

代碼:

declarel_tname varchar2(100);l_table_name varchar2(100);l_sql varchar2(200);beginl_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

代碼:

declarel_tname_array dbms_sql.varchar2_table;l_table_name varchar2(100);l_sql varchar2(200);beginl_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.

代碼:

beginfor c in (select tname from tab) loopdbms_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,那么也只好用顯式游標了。