以下主要為說明 PL/SQL 中游標的使用以及其優缺點
在PL/SQL裡的游標可以分為顯式和隱式兩種，而隱式有分為select into隱式游標和for .. in 隱式游標兩種。所以，我們可以認為，有3種游標用法:
A. 顯式游標
B. select into隱式游標
C. for .. in 隱式游標
如何正確的選擇使用哪種游標，將對你的程式的性能，可讀性，代碼量等產生重大影響……
--By RollingPig
三種方法在使用上的優劣比較
顯式游標
|
select into隱式游標B
|
for .. in 隱式游標
| |
優點
|
|
|
|
缺點
|
麻煩，需要定義，打開，Fetch,Close一堆代碼，增加代碼複雜度，從而增加出錯的可能性
|
|
|
三種游標形式的簡單例子
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;
使用時機與選擇
- 在返回資料為一行的情況下，儘量使用select into 的隱式游標返回0行或者 100, 應選用顯示游標+Bulk collect ，以獲得較高的Fetch 性能，同時不至於使用太大的PGA記憶體
- 如果使用動態SQL, 則根據select list (column list) 是否固定，如果固定，仍然可以考慮使用select into 的隱式游標+動態SQL的方式。當然，仍然需要考慮返回行數的問題。
- 如果select list (column list) 不固定，則只好使用顯式游標，或者動態語句返回行數太多，必須用limit,那麼也只好用顯式游標了。
