|
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@vongates
connected. 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;
本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/Phoenix_99/archive/2010/08/20/5825926.aspx |
|
|
