pl sql 顯示游標、隱示游標 與 bulk collect 的使用說明

以下主要為說明 PL/SQL 中游標的使用以及其優缺點

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

A. 顯式游標

B. select into隱式游標

C. for .. in 隱式游標

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

--By RollingPig

三種方法在使用上的優劣比較

	顯式游標	select into隱式游標B	for .. in 隱式游標
優點	可以用於Bulk Collect的批量處理句式以提高性能 可以用於動態SQL的游標處理	代碼量最少 可以自動Detect 返回資料超過一行或少於一行的錯誤 可以使用Bulk Collect 批量處理，但是無法使用Limit 關鍵字	代碼量遠少於顯式游標 代碼可讀性優於顯式游標 代碼的出錯可能性也小於顯式游標
缺點	麻煩，需要定義，打開，Fetch,Close一堆代碼，增加代碼複雜度，從而增加出錯的可能性	如果不使用Bulk Collect 批量處理，僅僅只能用於返回資料正好一行的情況，無法使用於返回資料超過一行或少於一行的環境 使用Bulk Collect 批量處理時，無法使用limit 關鍵字，無法處理返回行數太多的情況(不好處理，容易造成PGA過大)	無法用於動態SQL的游標處理 在返回行數超過100行的情況下，性能明顯不如使用Bulk Collect的顯式游標 在性能對比方面，除非是使用了Bulk Collect,否則，三種方式沒有明顯性能差距

三種游標形式的簡單例子

A. 顯式游標

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

代碼:

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

 代碼:

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

代碼:

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隱式游標

代碼:

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隱式游標

代碼:

Declare

  TYPE l_tname_array IS TABLE OF VARCHAR2(100);

  p_tname_array l_tname_array;

  l_table_name varchar2(100);

  l_sql        varchar2(200);

begin

  p_tname_array := l_tname_array();

  p_tname_array.extend(1);

  l_table_name := 'TAB';

  l_sql        := 'select tname from ' || l_table_name ||

                  ' where rownum = 1 ';

  execute immediate l_sql

    into p_tname_array(1);

  for i in 1 .. p_tname_array.count loop

    dbms_output.put_line(p_tname_array(i));

  end loop;

end;

動態SQL 的 select into隱式游標 + Bulk Collect

代碼:

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.

代碼:

Begin

  for c in (select tname from tab) loop

    dbms_output.put_line(c.tname);

  end loop;

end;

使用時機與選擇

1. 在返回資料為一行的情況下，儘量使用select into 的隱式游標返回0行或者 100, 應選用顯示游標+Bulk collect ，以獲得較高的Fetch 性能，同時不至於使用太大的PGA記憶體
2. 如果使用動態SQL, 則根據select list (column list) 是否固定，如果固定，仍然可以考慮使用select into 的隱式游標+動態SQL的方式。當然，仍然需要考慮返回行數的問題。
3. 如果select list (column list) 不固定，則只好使用顯式游標，或者動態語句返回行數太多，必須用limit,那麼也只好用顯式游標了。