oracle中cursor的使用經典資料

dawn009發表於2014-03-03
Oracle
----&gt>轉載於:http://blog.sina.com.cn/s/blog_713bf4fe0100x27v.html
關鍵字
?概念
?型別
?異常處理

一  概念
遊標是SQL的一個記憶體工作區，由系統或使用者以變數的形式定義。遊標的作用就是用於臨時儲存從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁碟的表中調到計算機記憶體中進行處理，最後將處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁碟資料交換會降低效率。
二  型別
  Cursor型別包含三種: 隱式Cursor，顯式Cursor和Ref Cursor（動態Cursor）。
1． 隱式Cursor:
1).對於Select …INTO…語句，一次只能從資料庫中獲取到一條資料，對於這種型別的DML Sql語句，就是隱式Cursor。例如：Select /Update / Insert/Delete操作。
2)作用：可以透過隱式Cusor的屬性來了解操作的狀態和結果，從而達到流程的控制。Cursor的屬性包含：
SQL%ROWCOUNT 整型 代表DML語句成功執行的資料行數
SQL%FOUND  布林型  值為TRUE代表插入、刪除、更新或單行查詢操作成功
SQL%NOTFOUND 布林型 與SQL%FOUND屬性返回值相反
SQL%ISOPEN 布林型 DML執行過程中為真，結束後為假
3) 隱式Cursor是系統自動開啟和關閉Cursor.
下面是一個Sample：
Sql程式碼 ： 
  1. Set Serveroutput on;   
  2.   
  3. begin  
  4.     update t_contract_master set liability_state = 1 where policy_code = '123456789';   
  5.        
  6.     if SQL%Found then  
  7.        dbms_output.put_line('the Policy is updated successfully.');   
  8.        commit;   
  9.     else  
  10.       dbms_output.put_line('the policy is updated failed.');   
  11.     end if;   
  12.   
  13. end;   
  14.   
  15. /  
在PL/SQL中run:
Sql程式碼  
  1. SQL>    
  2.     
  3. the policy is updated failed.   
  4.     
  5. PL/SQL procedure successfully completed  
2． 顯式Cursor：
（1） 對於從資料庫中提取多行資料，就需要使用顯式Cursor。顯式Cursor的屬性包含：
遊標的屬性   返回值型別   意    義 
%ROWCOUNT   整型  獲得FETCH語句返回的資料行數 
%FOUND  布林型 最近的FETCH語句返回一行資料則為真，否則為假 
%NOTFOUND   布林型 與%FOUND屬性返回值相反 
%ISOPEN 布林型 遊標已經開啟時值為真，否則為假 

（2） 對於顯式遊標的運用分為四個步驟：
? 定義遊標---Cursor  [Cursor Name]  IS;
? 開啟遊標---Open  [Cursor Name];
? 運算元據---Fetch  [Cursor name]
? 關閉遊標---Close [Cursor Name],這個Step絕對不可以遺漏。
（3）以下是三種常見顯式Cursor用法。
1）
Sql程式碼  
  1. Set serveroutput on;   
  2.   
  3. declare    
  4.     ---define Cursor   
  5.     Cursor cur_policy is  
  6.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
  7.      from t_contract_master cm   
  8.      where cm.liability_state = 2   
  9.      and cm.policy_type = 1   
  10.      and cm.policy_cate in ('2','3','4')   
  11.      and rownum 
  12.      order by cm.policy_code desc;   
  13.     curPolicyInfo cur_policy%rowtype;---定義遊標變數   
  14. Begin  
  15.    open cur_policy; ---open cursor   
  16.    Loop    
  17.      --deal with extraction data from DB   
  18.      Fetch cur_policy into curPolicyInfo;   
  19.      Exit when cur_policy%notfound;   
  20.             
  21.      Dbms_Output.put_line(curPolicyInfo.policy_code);   
  22.    end loop;   
  23.    Exception    
  24.      when others then  
  25.          close cur_policy;   
  26.          Dbms_Output.put_line(Sqlerrm);   
  27.             
  28.    if cur_policy%isopen then     
  29.     --close cursor    
  30.       close cur_policy;   
  31.    end if;   
  32. end;   
  33.   
  34. /  
2）
Sql程式碼  
  1. Set serveroutput on;   
  2.   
  3. declare    
  4.     Cursor cur_policy is  
  5.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
  6.      from t_contract_master cm   
  7.      where cm.liability_state = 2   
  8.      and cm.policy_type = 1   
  9.      and cm.policy_cate in ('2','3','4')   
  10.      and rownum 
  11.      order by cm.policy_code desc;   
  12.      v_policyCode t_contract_master.policy_code%type;   
  13.      v_applicantId t_contract_master.applicant_id%type;   
  14.      v_periodPrem t_contract_master.period_prem%type;   
  15.      v_bankCode t_contract_master.bank_code%type;   
  16.      v_bankAccount t_contract_master.bank_account%type;   
  17. Begin  
  18.    open cur_policy;   
  19.    Loop    
  20.      Fetch cur_policy into v_policyCode,   
  21.                            v_applicantId,   
  22.                            v_periodPrem,   
  23.                            v_bankCode,   
  24.                            v_bankAccount;   
  25.      Exit when cur_policy%notfound;   
  26.             
  27.      Dbms_Output.put_line(v_policyCode);   
  28.    end loop;   
  29.    Exception    
  30.      when others then  
  31.          close cur_policy;   
  32.          Dbms_Output.put_line(Sqlerrm);   
  33.             
  34.    if cur_policy%isopen then      
  35.       close cur_policy;   
  36.    end if;   
  37. end;   
  38. /  
3）
Sql程式碼  
  1. Set serveroutput on;   
  2.   
  3. declare    
  4.     Cursor cur_policy is  
  5.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
  6.      from t_contract_master cm   
  7.      where cm.liability_state = 2   
  8.      and cm.policy_type = 1   
  9.      and cm.policy_cate in ('2','3','4')   
  10.      and rownum 
  11.      order by cm.policy_code desc;   
  12. Begin  
  13.    For rec_Policy in cur_policy loop   
  14.        Dbms_Output.put_line(rec_policy.policy_code);   
  15.    end loop;   
  16.    Exception    
  17.      when others then  
  18.          Dbms_Output.put_line(Sqlerrm);   
  19.             
  20. end;   
  21.   
  22. /  
run pl/sql,執行結果如下：
Sql程式碼  
  1. SQL>    
  2.     
  3. 8780203932   
  4. 8780203227   
  5. 8780203218   
  6. 8771289268   
  7.     
  8. PL/SQL procedure successfully completed  
3． Ref Cursor（動態遊標）：
1） 與隱式Cursor,顯式Cursor的區別：Ref Cursor是可以透過在執行期間傳遞引數來獲取資料結果集。而另外兩種Cursor，是靜態的，在編譯期間就決定資料結果集。
2） Ref cursor的使用:
? Type [Cursor type name] is ref cursor
? Define 動態的Sql語句
? Open cursor
? 運算元據---Fetch  [Cursor name]
? Close Cursor
下面是一個Sample：
Sql程式碼  
  1. Set serveroutput on;   
  2.   
  3. Declare  
  4.     ---define cursor type name   
  5.     type cur_type is ref cursor;   
  6.     cur_policy cur_type;   
  7.     sqlStr varchar2(500);   
  8.     rec_policy t_contract_master%rowtype;   
  9. begin  
  10.    ---define 動態Sql   
  11.    sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm   
  12.      where cm.liability_state = 2    
  13.      and cm.policy_type = 1    
  14.      and cm.policy_cate in (2,3,4)    
  15.      and rownum 
  16.      order by cm.policy_code desc ';   
  17. ---Open Cursor   
  18.   open cur_policy for sqlStr;   
  19.   loop   
  20.        fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;   
  21.        exit when cur_policy%notfound;   
  22.           
  23.        Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);   
  24.      
  25.   end loop;   
  26. close cur_policy;       
  27.   
  28. end;   
  29. /  
4.常見Exception
Sql程式碼  
  1. 1.  錯 誤 名 稱 錯誤程式碼    錯 誤 含 義      
  2. 2.  CURSOR_ALREADY_OPEN ORA_06511   試圖開啟已經開啟的遊標      
  3. 3.  INVALID_CURSOR  ORA_01001   試圖使用沒有開啟的遊標      
  4. 4.  DUP_VAL_ON_INDEX    ORA_00001   儲存重複值到惟一索引約束的列中      
  5. 5.  ZERO_DIVIDE ORA_01476   發生除數為零的除法錯誤      
  6. 6.  INVALID_NUMBER  ORA_01722   試圖對無效字元進行數值轉換      
  7. 7.  ROWTYPE_MISMATCH    ORA_06504   主變數和遊標的型別不相容      
  8. 8.  VALUE_ERROR ORA_06502   轉換、截斷或算術運算發生錯誤      
  9. 9.  TOO_MANY_ROWS   ORA_01422   SELECT…INTO…語句返回多於一行的資料      
  10. 10. NO_DATA_FOUND   ORA_01403   SELECT…INTO…語句沒有資料返回      
  11. 11. TIMEOUT_ON_RESOURCE ORA_00051   等待資源時發生超時錯誤      
  12. 12. TRANSACTION_BACKED_OUT  ORA_00060   由於死鎖，提交失敗      
  13. 13. STORAGE_ERROR   ORA_06500   發生記憶體錯誤      
  14. 14. PROGRAM_ERROR   ORA_06501   發生PL/SQL內部錯誤      
  15. 15. NOT_LOGGED_ON   ORA_01012   試圖操作未連線的資料庫      
  16. 16. LOGIN_DENIED    ORA_01017   在連線時提供了無效使用者名稱或口令。

四.oracle cursor和遊標變數的區別:

oracle遊標我們經常用到，下面就為您詳細介紹oracle遊標和遊標變數的區別，如果您對oracle遊標方面感興趣的話，不妨一看。

oracle遊標是資料庫中一個命名的工作區,當遊標被宣告後,他就與一個固定的SQL想關聯,在編譯時刻是已知的,是靜態的.它永遠指向一個相同的查詢工作區.
遊標變數可以在執行時刻與不同的SQL語句關聯,在執行時可以取不同的SQL語句.它可以引用不同的工作區.

oracle遊標和遊標變數是不能相互代替的.

如何定義遊標型別

TYPE ref_type_name IS REF CURSOR [RETURN return_type];

宣告遊標變數

cursor_name ref_type_name;
 
ref_type_name 是後面宣告遊標變數時要用到的我們的遊標型別(自定義遊標型別,即CURSOR是系統預設的,ref_type_name是我們定義的 );return_type代表資料庫表中的一行,或一個記錄型別
TYPE ref_type_name IS REF CURSOR RETURN employee%TYPE
RETURN 是可選的,如果有是強型別,可以減少錯誤,如果沒有return是弱引用,有較好的靈活性.
 
不能在包頭裡面宣告遊標變數,注意,但可以定義遊標型別,要注意這二者的區別.
可以宣告遊標變數作為函式或過程的形式引數.

%TYPE一個列型別

%ROWTYPE行型別

控制oracle遊標變數

OPEN-FOR(開啟遊標變數,與多行查詢連線起來) FETCH(從結果集中取行資料),close(關閉遊標變數)
BULK COLLECT子句將遊標變數中的行一次性提取到一個集合中.



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1098480/，如需轉載，請註明出處，否則將追究法律責任。

相關文章