|
cursor 與refcursor及sys_refcursor的區(qū)別 收藏
今天簡單的總結(jié)一下PL/SQL中cursor(光標/游標)的用法。 相信不少做開發(fā)或維護的DBA在找工作的時候，遇到過類似的面視問題：請簡單的描述一下光標的類型，說一下普通光標和REF光標之間的區(qū)別，以及什么時候該正確應(yīng)用哪一個?
這個題目，我著實難住了不少人，其實他們在具體開發(fā)的時候，也還是比較能夠把握正確的光標的使用的，但就是說不出來，當(dāng)然了，這與大家自身的溝流交通能力是有關(guān)系的。有的人不善于說，但做的卻很好。扯的扯就走遠了，最后嘮叨一下：做技術(shù)這條路，能干不能說，或者說會干不會包裝，路是走不"遠"的。
一、顯式cursor 顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式游標的聲明類似如下（詳細的語法參加plsql ref doc ）：
cursor cursor_name (parameter list) is select ...
游標從declare、open、fetch、close是一個完整的生命旅程。當(dāng)然了一個這樣的游標是可以被多次open進行使用的，顯式cursor是靜態(tài)cursor，她的作用域是全局的，但也必須明白，靜態(tài)cursor也只有pl/sql代碼才可以使用她。下面看一個簡單的靜態(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; / 上面這段匿名塊用來實現(xiàn)選號的功能，我們顯式的定義了一個get_gsmno_cur，然后根據(jù)不同的號段輸出當(dāng)前系統(tǒng)中該號短對應(yīng)的可用手機號碼。當(dāng)然了，實際應(yīng)用中沒人這么用的，我只是用來說應(yīng)一個顯式cursor的用法。
二、隱式cursor
隱式cursor當(dāng)然是相對于顯式而言的，就是沒有明確的cursor的declare。在Oracle的PL/SQL中，所有的DML操作都被Oracle內(nèi)部解析為一個cursor名為SQL的隱式游標，只是對我們透明罷了。
另外，我們前面提到的一些循環(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屬于動態(tài)cursor(直到運行時才知道這條查詢)。
從技術(shù)上講，在最基本的層次靜態(tài)cursor和ref cursor是相同的。一個典型的PL/SQL光標按定義是靜態(tài)的。Ref光標正好相反，可以動態(tài)地打開，或者利用一組SQL靜態(tài)語句來打開，選擇哪種方法由邏輯確定（一個IF/THEN/ELSE代碼塊將打開一個或其它的查詢）。例如，下面的代碼塊顯示一個典型的靜態(tài)SQL光標，光標C。此外，還顯示了如何通過使用動態(tài)SQL或靜態(tài)SQL來用ref光標（在本例中為L_CURSOR）來打開一個查詢：
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; / 在這段代碼塊中，可以看到了最顯而易見的區(qū)別：無論運行多少次該代碼塊，光標C總是select * from dual。相反，ref光標可以是任何結(jié)果集，因為"select * from emp"字符串可以用實際上包含任何查詢的變量來代替。
在上面的代碼中，聲明了一個弱類型的REF cursor，下面再看一個強類型（受限）的REF cursor，這種類型的REF cursor在實際的應(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ū)別，我再浪費點唾沫。
1）PL/SQL靜態(tài)光標不能返回到客戶端，只有PL/SQL才能利用它。ref光標能夠被返回到客戶端，這就是從Oracle的存儲過程返回結(jié)果集的方式。
2）PL/SQL靜態(tài)光標可以是全局的，而ref光標則不是。 也就是說，不能在包說明或包體中的過程或函數(shù)之外定義ref光標。 只能在定義ref光標的過程中處理它，或返回到客戶端應(yīng)用程序。
3）ref光標可以從子例程傳遞到子例程，而光標則不能。 為了共享靜態(tài)光標，必須在包說明或包體中把它定義為全局光標。 因為使用全局變量通常不是一種很好的編碼習(xí)慣，因此可以用ref光標來共享PL/SQL中的光標，無需混合使用全局變量。
最后，使用靜態(tài)光標--通過靜態(tài)SQL（但不用ref光標）--比使用ref光標效率高，而ref光標的使用僅限于以下幾種情況：
把結(jié)果集返回給客戶端；
在多個子例程之間共享光標（實際上與上面提到的一點非常類似）； 沒有其他有效的方法來達到你的目標時，則使用ref光標，正如必須用動態(tài)SQL時那樣； 簡言之，首先考慮使用靜態(tài)SQL，只有絕對必須使用ref光標時才使用ref光標，也有人建議盡量使用隱式游標，避免編寫附加的游標控制代碼（聲明，打開，獲取，關(guān)閉），也不需要聲明變量來保存從游標中獲取的數(shù)據(jù)。這個就因人因具體的case大家去酌定吧。
四、游標屬性
%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 語句觸發(fā) NO_DATA_FOUND； 2）當(dāng)一個顯式光標的 where 子句未找到時觸發(fā) %NOTFOUND； 3）當(dāng)UPDATE或DELETE 語句的where 子句未找到時觸發(fā) SQL%NOTFOUND； 4）在光標的提取(Fetch)循環(huán)中要用 %NOTFOUND 或%FOUND 來確定循環(huán)的退出條件，不要用NO_DATA_FOUND。 五、sys_refcursor
sys_refcursor是oracle9i以后系統(tǒng)定義的一個refcursor,主要用在過程中返回結(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;
本文來自CSDN博客，轉(zhuǎn)載請標明出處：http://blog.csdn.net/Phoenix_99/archive/2010/08/20/5825926.aspx |
|
|
