oracle中的遊標

標籤：

Oracle中的遊標有兩種：顯式遊標、隱式遊標。顯示遊標是用cursor...is命令定義的遊標，它可以對查詢語句(select)返回的多條記錄進行處理，而隱式遊標是在執行插入 (insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(select)語句時由PL /SQL 自動定義的。

顯式遊標

當聲明了顯式遊標後，可以通過以下三條命令控制顯式遊標的操作：開啟遊標、推進遊標、關閉遊標。

聲明顯式遊標

Ø  無參遊標

cursor c_auths is select * from auths

Ø  有參遊標

cursor c_auths(p_code auths.author_code%type) is select * from auths where author_code=p_code

Ø  綁定變數參數的遊標

          v_code auths.author_code%type;   

         cursor c_auths is  select * from auths where author_code=v_code;  

開啟顯式遊標

開啟一個已開啟的遊標也是合法的。當第二次開啟遊標時，PL /SQL 先自動關閉遊標，然後再開啟。一次開啟多個遊標也是PL /SQL 所允許的。 開啟遊標前為綁定變數賦值。

    v_code:=‘A00001‘ ; 

   open c_auths; 

    open c_auths(‘A00001‘ );   -- 開啟遊標時將參數傳入

推進顯式遊標

當開啟顯式遊標後，就可以使用FETCH語句來推進遊標，返回查詢結果集中的一行。每執行完一條FETCH語句後，顯式遊標會自動指向查詢結果集的下一行。

關閉顯式遊標

當整個結果集都檢索完以後，應當關閉遊標。關閉遊標用來通知PL /SQL 遊標操作已經結束，並且釋放遊標所佔用的資源(結果集所使用的資源空間)。

隱式遊標

在PL/SQL中為所有的SQL資料操縱語句（包括返回一行的select）隱式聲明遊標 稱為隱式遊標。主要原因是使用者不能直接命名和控制此類遊標。當使用者在PL/SQL 中使用資料操縱語句（DML）和select into時，oracle預先定義一個名稱為SQL的隱式遊標，通過檢查隱式遊標的屬性擷取與最近執行的SQL語句相關資訊。

　　在PL/SQL中向標準的select語句增加單獨的into子句，就可以將從表或視圖中查詢記錄賦予變數或行變數。需要注意的是select ..into 語句結果必須有且只能有一行。 如果查詢沒有返回行，PL/SQL將拋出no_data_found異常。如果查詢返回多行，則拋出 too_many_rows 異常。如果拋出異常，則停止執行，控制權轉移到異常處理部分（沒有異常處理，則程式中斷）。在引發異常時，將不使用屬性%found,%notfound,%rowcount來查明DML語句是否 已影響了行數。

begin  

  update auths set entry_date_time=sysdate where author_code=‘A00017‘ ;  

  -- 如果update語句中修改的行不存在(SQL %notfound傳回值為true ),則向auths表中插入一行。  

  if  sql %nofound then  

    insert into auths values(‘A000017‘ , ‘qiuys‘ , 1 , ‘30-apr-40‘ , 88.5 ,sysdate);  

  end if ;  

end;  

--如果update語句中修改的行不存在(sql %rowcount=0)

declare  

  v_birthdate date;  

begin

  select birthdate into v_birthdate from auths where name=‘qiuys‘ ;  

  -- 如果查詢到一條記錄，則刪除該記錄。  

  if  sql %found then  

    delete from auths where name=‘qiuys‘ ;  

  end if ;  

exception  

  when no_data_found then  

    dbms_output.put_line(‘該記錄不存在‘ );  

  when too_many_rows then  

    dbms_output_line(‘存在同名的作家‘ );  

end;  

遊標屬性

Ø  %found 只有DML語句影響一行或多行時，%found屬性才返回true

Ø  %notfound正好跟%found屬性相反。如果DML語句沒有影響任何行數 ，則%notfound屬性返回true.

Ø  %rowcount返回DML語句影響的行數。如果DML語句沒有影響任何行數 ，則%rowcount屬性將返回0。

Ø  %isopen 判斷SQL遊標是否已經開啟。在執行SQL語句之後，oracle自動關閉SQL 遊標，所以隱式遊標的%isopen屬性始終為false.

遊標迴圈

FETCH迴圈 

delcare  

  -- 聲明一個變數，這個變數用來接收遊標返回的結果集。  

  v_salary auths.salary%type;  

  v_code auths.author_code%type;  

  /*聲明遊標，該遊標的查詢結果集是作家代碼為"A00001"到"A00006"的工資值。*/   

  cursor c_salary is select salary,author_code from auths where author_code<=‘A00006‘ ;  

begin  

  -- 開啟遊標，並初始化結果集  

  open c_salary;  

  loop  

    -- 推進遊標，將遊標的查詢結果集中的一行存到變數v_salary中。  

    fetch c_salary into v_salary,v_code;  

    -- 當結果集中沒有行時退出迴圈。  

    exit when c_salary%notfound;  

    -- 如果查詢到的作家工資小於或等於200 ，則增加該作家的工資值。  

    if  v_salary<= 200  then  

         update auths set salary=salary+50  where author_code=v_code;  

    end if ;  

  end loop;  

  -- 關閉遊標，釋放遊標佔用資源。  

  close c_salary;  

  -- 提交所做的修改。  

  commit;  

end;  

FOR迴圈

delcare  

  cursor c_salary is  

    select salary form auths where author_code<=‘A00006‘ ;  

begin  

  -- 開始遊標FOR迴圈，隱含地開啟c_salary遊標。  

  for  v_salary in c_salary loop  

    -- 一個隱含的fetch語句在這裡被執行。  

　if  v_salary.salary<= 200  then    

update auths set salary=salary+50  where salary=v_salary.salary;  

    end if ;  

    --在迴圈繼續前，一個隱含的c_auths%notfound被檢測。  

　end loop;  

  -- 現在迴圈已經結束，c_auths遊標的一個隱含的close操作被執行。  

  commit;  

end;  

使用current of cursor子句作為條件

declare

cursor cur_emp is

elect empno , ename , job from emp where empno = 7369  for update of ename ;

begin

    for return_cur in cur_emp

    loop   

       update emp set ename = ‘LHG‘ where current of cur_emp ;

    end loop ;

end ;

其中的for update of ename 意思是給表中的行加鎖，經過測試，後面的ename可以寫成該表中的任何欄位，因為oracle中鎖的最低層級是行鎖，不存在給欄位加鎖。

下面是行鎖的作用：

1.行鎖開始於一個CURSOR的OPEN,結束於一個提交COMMIT或ROLLBACK，而並不是結束於一個CURSOR的結束(CLOSE)。

2.當在一個CURSOR中對某個表的行加鎖，那麼如果在這個SESSION中用另一個CURSOR操作該行記錄時候會等待第一個cursor的完成提交，直到第一個cursor提交完成之後，第二個cursor中對改行的操作才會開始執行。

3.當第一個cursor中的由於操作錯誤而不能提交時候，第二個cursor將會一直等待下去，這樣就形成了死結。預防這種情況發生的方法是在for update of ename 之後指定NOWAIT選項，這樣的話，當第二個cursor不會一直等待下去，而是出現 ORA-00054 [resource busy and acquire with NOWAIT specified] 的訊息。

4.既然for update of後面的欄位是隨便那個欄位都可以，那麼可不可以不寫呢？如果你沒必要指定NOWAIT選項，那麼後面的欄位就可以不寫；如果你必須指定NOWAIT選項，則必須指定至少一個欄位。

5.還有一種方法是用ROWID 替代WHERE CURRENT OF YOUR_CURSOR_NAME 語句。

如下代碼：

declare
      cursor cur_emp is select a . deptno ,a . dname , a . rowid , b . rowid rowid_1  

from dept a , emp b where empno = 7369 and a . deptno = b . deptno for update nowait ;

     v_deptno dept . deptno % type ;
     v_dname dept . dname % type ;
     v_rowid rowid ;
     v_rowid_1 rowid ;
begin
     open cur_emp ;
     loop
     fetch cur_emp into v_deptno , v_dname , v_rowid , v_rowid_1 ;
     exit when cur_emp % notfound ;
        update dept set dname = ‘abc‘ where rowid = v_rowid ;
        update emp set ename = ‘frank‘  where rowid = v_rowid_1 ;
     end loop ;
     close cur_emp ;
     commit ;
exception
     when others then
        rollback ;
        raise ;
end ;

由此，推薦的for update的習慣是：

Ø  NOWAIT 定然跟FOR UPDATE 之後。

Ø  直接用ROWID 替代WHERE CURRENT OF YOUR_CURSOR_NAME 語句，尤其在相對繁習的程式裡頭。

Ø  COMMIT 必需存在程式結尾。以防死結成形。

Ø  EXCEPTION 裡的ROLLBACK 是最基本的需要。

遊標變數

到目前為止前面所有顯式遊標的例子都是靜態資料指標－即遊標與一個SQL 陳述式關聯，並且該SQL 陳述式在編譯時間已經確定。而遊標變數是一個參考型別(REF)的變數。  

遊標變數的聲明

  -- 使用%rowtype定義一個遊標變數類型。  

  type t_authsref is ref cursor return auths%rowtype;  

  v_authcv t_authsref;  

 PL /SQL2.8以上版本中，可以使用一個沒有指定結果集類型的遊標變數來指定多個不同類型的查詢。

type t_authsref is ref cursor;

v_ authscv  t_authsref;--聲明一個該類型的變數。

開啟遊標變數  

為了將一個遊標變更與一個具體的select語句聯絡起來，open的文法中增加了一個select語句。

 open v_authscv for  select * from auths;    

關閉遊標操作

關閉遊標操作用來釋放查詢所佔用的資源。但沒有釋放遊標變數佔用的儲存空間。當變數超出範圍時，它所佔用的空間才被釋放掉。 下面的塊中定義了一個沒有指定結果集的遊標變數，這樣我們就可以使用這個遊標變數指向不同的查詢，並能夠返回不同的記錄類型：

set serveroutput on size  100000  --設定儲存緩衝區大小。  

declare  

  /*定義遊標變更類型t_curref，該遊標變數類型沒有指定結果集類型，所以該遊標變數類型的變數可以返回不同的PL /SQL 記錄類型。*/   

  type t_curref is ref cursor;  

  -- 聲明一個遊標變數類型的變數  

  c_cursorref t_curref;  

  -- 定義PL /SQL 記錄類型 t_authorrec，該類型的變數用來接收遊標變數的傳回值。  

  type t_authorrec is record(  

    authorcode auths.author_code%type,  

    name auths.name%type);  

  -- 定義PL /SQL 記錄類型 t_articlerec,該類型的變數也用來接收遊標變數的傳回值。  

  type t_articlerec is record(  

    authorcode article.author_code%type,  

    title artitle.title%type);  

  -- 聲明兩個記錄類型變數。  

  v_author t_authorrec;  

  v_article t_articlerec;  

begin  

  -- 開啟遊標變數c_cursorref，返回t_authorrec類型的記錄。  

  open c_cursorref for    

    select author_code,name from auths where author_code in(‘A00001‘ , ‘A00002‘ , ‘A00003‘ , ‘A00004‘ , ‘A00005‘ );  

  -- 推進遊標變數  

  fetch c_cursorref into v_author;  

  -- 遊標變數的推進迴圈。  

  while  c_cursorref%found loop  

    -- 將作家代碼和相應的作家名字輸出到螢幕上。  

    dbms_output.put(v_author.authorcode||‘:‘ ||v_author.name|| ‘ ‘ );  

    fetch c_cursorref into v_author;  

  end loop;  

  dbms_output.new_line;-- 向螢幕上輸出一個斷行符號行。  

  --關閉遊標變數，僅僅將遊標變數指定的資源釋放掉，遊標變數本身的儲存空間沒有釋放掉。  

  close c_cursorref;  

  -- 再次開啟遊標變數，返回t_articlerec類型的記錄。  

  open c_cursorref for    

    select author_code,title from article  

    where author_code in(‘A00001‘ , ‘A00002‘ , ‘A00003‘ , ‘A00004‘ , ‘A00005‘ );  

  fetch c_cursorref into v_article;  

  while  c_cursorref%found loop  

    ...  

  end loop;  

  close c_cursorref;  

end;  

注意，在上例中，第一次關閉遊標變數是可省略的，因為在第二次開啟遊標變數時，就將第一次的查詢丟失掉了。而且遊標變數也有遊標屬性，通常在推進遊標變數時使用這些遊標屬性，例如上例使用了%found屬性。

 

oracle中的遊標（轉）