cursor 與refcursor及sys_refcursor的區(qū)別 收藏
今天簡(jiǎn)單的總結(jié)一下PL/SQL中cursor(光標(biāo)/游標(biāo))的用法。
相信不少做開(kāi)發(fā)或維護(hù)的DBA在找工作的時(shí)候，遇到過(guò)類似的面視問(wèn)題：請(qǐng)簡(jiǎn)單的描述一下光標(biāo)的類型，說(shuō)一下普通光標(biāo)和REF光標(biāo)之間的區(qū)別，以及什么時(shí)候該正確應(yīng)用哪一個(gè)?
這個(gè)題目，我著實(shí)難住了不少人，其實(shí)他們?cè)诰唧w開(kāi)發(fā)的時(shí)候，也還是比較能夠把握正確的光標(biāo)的使用的，但就是說(shuō)不出來(lái)，當(dāng)然了，這與大家自身的溝流交通能力是有關(guān)系的。有的人不善于說(shuō)，但做的卻很好。扯的扯就走遠(yuǎn)了，最后嘮叨一下：做技術(shù)這條路，能干不能說(shuō)，或者說(shuō)會(huì)干不會(huì)包裝，路是走不"遠(yuǎn)"的。
一、顯式cursor
顯式是相對(duì)與隱式cursor而言的，就是有一個(gè)明確的聲明的cursor。顯式游標(biāo)的聲明類似如下（詳細(xì)的語(yǔ)法參加plsql ref doc ）：
cursor cursor_name (parameter list) is select ...
游標(biāo)從declare、open、fetch、close是一個(gè)完整的生命旅程。當(dāng)然了一個(gè)這樣的游標(biāo)是可以被多次open進(jìn)行使用的，顯式cursor是靜態(tài)cursor，她的作用域是全局的，但也必須明白，靜態(tài)cursor也只有pl/sql代碼才可以使用她。下面看一個(gè)簡(jiǎn)單的靜態(tài)顯式cursor的示例：
declare
cursor get_gsmno_cur (p_nettype in varchar2) is
select gsmno
from gsm_resource
where nettype=p_nettype and status='0';
v_gsmno gsm_resource.gsmno%type;
begin
open get_gsmno_cur('139');
loop
fetch get_gsmno_cur into v_gsmno;
exit when get_gsmno_cur%notfound;
dbms_output.put_line(v_gsmno);
end loop;
close emp_cur;
open get_gsmno_cur('138');
loop
fetch get_gsmno_cur into v_gsmno;
exit when get_gsmno_cur%notfound;
dbms_output.put_line(v_gsmno);
end loop;
close get_gsmno_cur;
end;
/
上面這段匿名塊用來(lái)實(shí)現(xiàn)選號(hào)的功能，我們顯式的定義了一個(gè)get_gsmno_cur，然后根據(jù)不同的號(hào)段輸出當(dāng)前系統(tǒng)中該號(hào)短對(duì)應(yīng)的可用手機(jī)號(hào)碼。當(dāng)然了，實(shí)際應(yīng)用中沒(méi)人這么用的，我只是用來(lái)說(shuō)應(yīng)一個(gè)顯式cursor的用法。
二、隱式cursor
隱式cursor當(dāng)然是相對(duì)于顯式而言的，就是沒(méi)有明確的cursor的declare。在Oracle的PL/SQL中，所有的DML操作都被Oracle內(nèi)部解析為一個(gè)cursor名為SQL的隱式游標(biāo)，只是對(duì)我們透明罷了。
另外，我們前面提到的一些循環(huán)操作中的指針for 循環(huán)，都是隱式cursor?！?
隱式cursor示例一：
CREATE TABLE zrp (str VARCHAR2(10));
insert into zrp values ('ABCDEFG');
insert into zrp values ('ABCXEFG');
insert into zrp values ('ABCYEFG');
insert into zrp values ('ABCDEFG');
insert into zrp values ('ABCZEFG');
COMMIT;
SQL> begin
2 update zrp SET str = 'updateD' where str like '%D%';
3 ifSQL%ROWCOUNT= 0 then
4 insert into zrp values ('1111111');
5 end if;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> select * from zrp;
STR
----------
updateD
ABCXEFG
ABCYEFG
updateD
ABCZEFG
SQL>
SQL> begin
2 update zrp SET str = 'updateD' where str like '%S%';
3 ifSQL%ROWCOUNT= 0 THEN
4 insert into zrp values ('0000000');
5 end if;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> select * from zrp;
STR
----------
updateD
ABCXEFG
ABCYEFG
updateD
ABCZEFG
0000000
6 rows selected
SQL>
隱式cursor示例二：
begin
for rec in (select gsmno,status from gsm_resource) loop
dbms_output.put_line(rec.gsmno||'--'||rec.status);
end loop;
end;
/
三、REFcursor
Ref cursor屬于動(dòng)態(tài)cursor(直到運(yùn)行時(shí)才知道這條查詢)。
從技術(shù)上講，在最基本的層次靜態(tài)cursor和ref cursor是相同的。一個(gè)典型的PL/SQL光標(biāo)按定義是靜態(tài)的。Ref光標(biāo)正好相反，可以動(dòng)態(tài)地打開(kāi)，或者利用一組SQL靜態(tài)語(yǔ)句來(lái)打開(kāi)，選擇哪種方法由邏輯確定（一個(gè)IF/THEN/ELSE代碼塊將打開(kāi)一個(gè)或其它的查詢）。例如，下面的代碼塊顯示一個(gè)典型的靜態(tài)SQL光標(biāo)，光標(biāo)C。此外，還顯示了如何通過(guò)使用動(dòng)態(tài)SQL或靜態(tài)SQL來(lái)用ref光標(biāo)（在本例中為L(zhǎng)_CURSOR）來(lái)打開(kāi)一個(gè)查詢：
Declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if (to_char(sysdate,'dd') = 30) then
-- ref cursor with dynamic sql
open l_cursor for 'select * from emp';
elsif (to_char(sysdate,'dd') = 29) then
-- ref cursor with static sql
open l_cursor for select * from dept;
else
-- with ref cursor with static sql
open l_cursor for select * from dual;
end if;
-- the "normal" static cursor
open c;
end;
/
在這段代碼塊中，可以看到了最顯而易見(jiàn)的區(qū)別：無(wú)論運(yùn)行多少次該代碼塊，光標(biāo)C總是select * from dual。相反，ref光標(biāo)可以是任何結(jié)果集，因?yàn)?select * from emp"字符串可以用實(shí)際上包含任何查詢的變量來(lái)代替。
在上面的代碼中，聲明了一個(gè)弱類型的REF cursor，下面再看一個(gè)強(qiáng)類型（受限）的REF cursor，這種類型的REF cursor在實(shí)際的應(yīng)用系統(tǒng)中用的也是比較多的。
create table gsm_resource
(
gsmno varchar2(11),
status varchar2(1),
price number(8,2),
store_id varchar2(32)
);
insert into gsm_resource values('13905310001','0',200.00,'SD.JN.01');
insert into gsm_resource values('13905312002','0',800.00,'SD.JN.02');
insert into gsm_resource values('13905315005','1',500.00,'SD.JN.01');
insert into gsm_resource values('13905316006','0',900.00,'SD.JN.03');
commit;
SQL> declare
2 type gsm_rec is record(
3 gsmno varchar2(11),
4 status varchar2(1),
5 price number(8,2));
6
7 type app_ref_cur_type is ref cursor return gsm_rec;
8 my_cur app_ref_cur_type;
9 my_rec gsm_rec;
10
11 begin
12 open my_cur for select gsmno,status,price
13 from gsm_resource
14 where store_id='SD.JN.01';
15 fetch my_cur into my_rec;
16 while my_cur%found loop
17 dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);
18 fetch my_cur into my_rec;
19 end loop;
20 close my_cur;
21 end;
22 /
13905310001#0#200
13905315005#1#500
PL/SQL procedure successfully completed
SQL>
普通cursor與REF cursor還有一些大家應(yīng)該都熟悉的區(qū)別，我再浪費(fèi)點(diǎn)唾沫。
1）PL/SQL靜態(tài)光標(biāo)不能返回到客戶端，只有PL/SQL才能利用它。ref光標(biāo)能夠被返回到客戶端，這就是從Oracle的存儲(chǔ)過(guò)程返回結(jié)果集的方式。
2）PL/SQL靜態(tài)光標(biāo)可以是全局的，而ref光標(biāo)則不是。 也就是說(shuō)，不能在包說(shuō)明或包體中的過(guò)程或函數(shù)之外定義ref光標(biāo)。 只能在定義ref光標(biāo)的過(guò)程中處理它，或返回到客戶端應(yīng)用程序。
3）ref光標(biāo)可以從子例程傳遞到子例程，而光標(biāo)則不能。 為了共享靜態(tài)光標(biāo)，必須在包說(shuō)明或包體中把它定義為全局光標(biāo)。 因?yàn)槭褂萌肿兞客ǔ２皇且环N很好的編碼習(xí)慣，因此可以用ref光標(biāo)來(lái)共享PL/SQL中的光標(biāo)，無(wú)需混合使用全局變量。
最后，使用靜態(tài)光標(biāo)--通過(guò)靜態(tài)SQL（但不用ref光標(biāo)）--比使用ref光標(biāo)效率高，而ref光標(biāo)的使用僅限于以下幾種情況：
把結(jié)果集返回給客戶端；
在多個(gè)子例程之間共享光標(biāo)（實(shí)際上與上面提到的一點(diǎn)非常類似）；
沒(méi)有其他有效的方法來(lái)達(dá)到你的目標(biāo)時(shí)，則使用ref光標(biāo)，正如必須用動(dòng)態(tài)SQL時(shí)那樣；
簡(jiǎn)言之，首先考慮使用靜態(tài)SQL，只有絕對(duì)必須使用ref光標(biāo)時(shí)才使用ref光標(biāo)，也有人建議盡量使用隱式游標(biāo)，避免編寫附加的游標(biāo)控制代碼（聲明，打開(kāi)，獲取，關(guān)閉），也不需要聲明變量來(lái)保存從游標(biāo)中獲取的數(shù)據(jù)。這個(gè)就因人因具體的case大家去酌定吧。
四、游標(biāo)屬性
%FOUND： bool - TRUE if >1 row returned
%NOTFOUND：bool - TRUE if 0 rows returned
%ISOPEN： bool - TRUE if cursor still open
%ROWCOUNT：int - number of rows affected by last SQL statement
注：NO_DATA_FOUND和%NOTFOUND的用法是有區(qū)別的，小結(jié)如下：
1）SELECT . . . INTO 語(yǔ)句觸發(fā) NO_DATA_FOUND；
2）當(dāng)一個(gè)顯式光標(biāo)的 where 子句未找到時(shí)觸發(fā) %NOTFOUND；
3）當(dāng)UPDATE或DELETE 語(yǔ)句的where 子句未找到時(shí)觸發(fā) SQL%NOTFOUND；
4）在光標(biāo)的提取(Fetch)循環(huán)中要用 %NOTFOUND 或%FOUND 來(lái)確定循環(huán)的退出條件，不要用NO_DATA_FOUND。
五、sys_refcursor
sys_refcursor是oracle9i以后系統(tǒng)定義的一個(gè)refcursor,主要用在過(guò)程中返回結(jié)果集。
例：
SQL> conn
scott/tiger@vongatesconnected.
SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,
2 out_curEmp out SYS_REFCURSOR) as
3
4 begin
5 open out_curEmp for
6 SELECT * FROM emp WHERE deptno = in_deptNo ;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE_APPLICATION_ERROR(-20101,
10 'Error in getEmpByDept' || SQLCODE );
12 end getEmpByDept;
13 /
已建立程序.
SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);
PL/SQL 程序順利完成.
SQL> print rset;
