感謝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,那么也只好用顯式游標了。