Oracle中Cursor介紹.docx

Oracle中Cursor介紹 原創(chuàng)作者: hwhuang 閱讀:1101次 評論:0條 更新時間:2010-05-03 關(guān)鍵字 l概念 l類型 l異常處理 一 概念 游標(biāo)是SQL的一個內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標(biāo)的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。在某些情況下，需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計算機內(nèi)存中進行處理，最后將處理結(jié)果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度才會提高，否則頻繁的磁盤數(shù)據(jù)交換會降低效率。 二 類型 Cursor類型包含三種: 隱式Cursor，顯式Cursor和Ref Cursor（動態(tài)Cursor）。 1 隱式Cursor: 1).對于Select INTO語句，一次只能從數(shù)據(jù)庫中獲取到一條數(shù)據(jù)，對于這種類型的DML Sql語句，就是隱式Cursor。例如：Select /Update / Insert/Delete操作。 2)作用：可以通過隱式Cusor的屬性來了解操作的狀態(tài)和結(jié)果，從而達到流程的控制。Cursor的屬性包含： SQL%ROWCOUNT 整型 代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù) SQL%FOUND 布爾型 值為TRUE代表插入、刪除、更新或單行查詢操作成功 SQL%NOTFOUND 布爾型 與SQL%FOUND屬性返回值相反 SQL%ISOPEN 布爾型 DML執(zhí)行過程中為真，結(jié)束后為假 3) 隱式Cursor是系統(tǒng)自動打開和關(guān)閉Cursor. 下面是一個Sample： Sql代碼 1. SetServeroutputon; 2. 3. begin4. updatet_contract_mastersetliability_state=1wherepolicy_code=123456789; 5. 6. ifSQL%Foundthen7. dbms_output.put_line(thePolicyisupdatedsuccessfully.); 8. commit; 9. else10. dbms_output.put_line(thepolicyisupdatedfailed.); 11. endif; 12. 13. end; 14. 15. /Set Serveroutput on;begin update t_contract_master set liability_state = 1 where policy_code = 123456789; if SQL%Found then dbms_output.put_line(the Policy is updated successfully.); commit; else dbms_output.put_line(the policy is updated failed.); end if;end;/在PL/SQL中run: Sql代碼 1. SQL 2. 3. thepolicyisupdatedfailed. 4. 5. PL/SQLproceduresuccessfullycompletedSQL the policy is updated failed. PL/SQL procedure successfully completed2 顯式Cursor： （1） 對于從數(shù)據(jù)庫中提取多行數(shù)據(jù)，就需要使用顯式Cursor。顯式Cursor的屬性包含： 游標(biāo)的屬性 返回值類型 意 義 %ROWCOUNT 整型 獲得FETCH語句返回的數(shù)據(jù)行數(shù) %FOUND 布爾型 最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假 %NOTFOUND 布爾型 與%FOUND屬性返回值相反 %ISOPEN 布爾型 游標(biāo)已經(jīng)打開時值為真，否則為假 （2） 對于顯式游標(biāo)的運用分為四個步驟： 定義游標(biāo)-Cursor Cursor Name IS; 打開游標(biāo)-Open Cursor Name; 操作數(shù)據(jù)-Fetch Cursor name 關(guān)閉游標(biāo)-Close Cursor Name,這個Step絕對不可以遺漏。 （3）以下是三種常見顯式Cursor用法。 1） Sql代碼 1. Setserveroutputon; 2. 3. declare 4. -defineCursor 5. Cursorcur_policyis6. selectcm.policy_code,cm.applicant_id,cm.period_prem,cm.bank_code,cm.bank_account 7. fromt_contract_mastercm 8. wherecm.liability_state=2 9. andcm.policy_type=1 10. andcm.policy_catein(2,3,4) 11. andrownum5 12. orderbycm.policy_codedesc; 13. curPolicyInfocur_policy%rowtype;-定義游標(biāo)變量 14. Begin15. opencur_policy;-opencursor 16. Loop 17. -dealwithextractiondatafromDB 18. Fetchcur_policyintocurPolicyInfo; 19. Exitwhencur_policy%notfound; 20. 21. Dbms_Output.put_line(curPolicyInfo.policy_code); 22. endloop; 23. Exception 24. whenothersthen25. closecur_policy; 26. Dbms_Output.put_line(Sqlerrm); 27. 28. ifcur_policy%isopenthen 29. -closecursor 30. closecur_policy; 31. endif; 32. end; 33. 34. /Set serveroutput on;declare -define Cursor Cursor cur_policy is select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm where cm.liability_state = 2 and cm.policy_type = 1 and cm.policy_cate in (2,3,4) and rownum 5 order by cm.policy_code desc; curPolicyInfo cur_policy%rowtype;-定義游標(biāo)變量Begin open cur_policy; -open cursor Loop -deal with extraction data from DB Fetch cur_policy into curPolicyInfo; Exit when cur_policy%notfound; Dbms_Output.put_line(curPolicyInfo.policy_code); end loop; Exception when others then close cur_policy; Dbms_Output.put_line(Sqlerrm); if cur_policy%isopen then -close cursor close cur_policy; end if;end;/2） Sql代碼 1. Setserveroutputon; 2. 3. declare 4. Cursorcur_policyis5. selectcm.policy_code,cm.applicant_id,cm.period_prem,cm.bank_code,cm.bank_account 6. fromt_contract_mastercm 7. wherecm.liability_state=2 8. andcm.policy_type=1 9. andcm.policy_catein(2,3,4) 10. andrownum5 11. orderbycm.policy_codedesc; 12. v_policyCodet_contract_master.policy_code%type; 13. v_applicantIdt_contract_master.applicant_id%type; 14. v_periodPremt_contract_master.period_prem%type; 15. v_bankCodet_contract_master.bank_code%type; 16. v_bankAccountt_contract_master.bank_account%type; 17. Begin18. opencur_policy; 19. Loop 20. Fetchcur_policyintov_policyCode, 21. v_applicantId, 22. v_periodPrem, 23. v_bankCode, 24. v_bankAccount; 25. Exitwhencur_policy%notfound; 26. 27. Dbms_Output.put_line(v_policyCode); 28. endloop; 29. Exception 30. whenothersthen31. closecur_policy; 32. Dbms_Output.put_line(Sqlerrm); 33. 34. ifcur_policy%isopenthen 35. closecur_policy; 36. endif; 37. end; 38. /Set serveroutput on;declare Cursor cur_policy is select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm where cm.liability_state = 2 and cm.policy_type = 1 and cm.policy_cate in (2,3,4) and rownum 5 order by cm.policy_code desc; v_policyCode t_contract_master.policy_code%type; v_applicantId t_contract_master.applicant_id%type; v_periodPrem t_contract_master.period_prem%type; v_bankCode t_contract_master.bank_code%type; v_bankAccount t_contract_master.bank_account%type;Begin open cur_policy; Loop Fetch cur_policy into v_policyCode, v_applicantId, v_periodPrem, v_bankCode, v_bankAccount; Exit when cur_policy%notfound; Dbms_Output.put_line(v_policyCode); end loop; Exception when others then close cur_policy; Dbms_Output.put_line(Sqlerrm); if cur_policy%isopen then close cur_policy; end if;end;/3） Sql代碼 1. Setserveroutputon; 2. 3. declare 4. Cursorcur_policyis5. selectcm.policy_code,cm.applicant_id,cm.period_prem,cm.bank_code,cm.bank_account 6. fromt_contract_mastercm 7. wherecm.liability_state=2 8. andcm.policy_type=1 9. andcm.policy_catein(2,3,4) 10. andrownum5 11. orderbycm.policy_codedesc; 12. Begin13. Forrec_Policyincur_policyloop 14. Dbms_Output.put_line(rec_policy.policy_code); 15. endloop; 16. Exception 17. whenothersthen18. Dbms_Output.put_line(Sqlerrm); 19. 20. end; 21. 22. /Set serveroutput on;declare Cursor cur_policy is select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm where cm.liability_state = 2 and cm.policy_type = 1 and cm.policy_cate in (2,3,4) and rownum 2. 3. 8780203932 4. 8780203227 5. 8780203218 6. 8771289268 7. 8. PL/SQLproceduresuccessfullycompletedSQL 8780203932878020322787802032188771289268 PL/SQL procedure successfully completed3 Ref Cursor（動態(tài)游標(biāo)）： 1） 與隱式Cursor,顯式Cursor的區(qū)別：Ref Cursor是可以通過在運行期間傳遞參數(shù)來獲取數(shù)據(jù)結(jié)果集。而另外兩種Cursor，是靜態(tài)的，在編譯期間就決定數(shù)據(jù)結(jié)果集。 2） Ref cursor的使用: Type Cursor type name is ref cursor Define 動態(tài)的Sql語句 Open cursor 操作數(shù)據(jù)-Fetch Cursor name Close Cursor 下面是一個Sample： Sql代碼 1. Setserveroutputon; 2. 3. Declare4. -definecursortypename 5. typecur_typeisrefcursor; 6. cur_policycur_type; 7. sqlStrvarchar2(500); 8. rec_policyt_contract_master%rowtype; 9. begin10. -define動態(tài)Sql 11. sqlStr:=selectcm.policy_code,cm.applicant_id,cm.period_prem,cm.bank_code,cm.bank_accountfromt_contract_mastercm 12. wherecm.liability_state=2 13. andcm.policy_type=1 14. andcm.policy_catein(2,3,4) 15. andrownum5 16. orderbycm.policy_codedesc; 17. -OpenCursor 18. opencur_policyforsqlStr; 19. loop 20. fetchcur_policyintorec_policy.policy_code,rec_policy.applicant_id,rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account; 21. exitwhencur_policy%notfound; 22. 23. Dbms_Output.put_line(Policy_code:|rec_policy.policy_code); 24. 25. endloop; 26. closecur_policy; 27. 28. end; 29. /Set serveroutput on;Declare -define cursor type name type cur_type is ref cursor; cur_policy cur_type; sqlStr varchar2(500); rec_policy t_contract_master%rowtype;begin -define 動態(tài)Sql sqlStr := select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm where cm.liability_state = 2 and cm.policy_type = 1 and cm.policy_cate in (2,3,4) and rownum 5 order by cm.policy_code desc ;-Open Cursor open cur_policy for sqlStr; loop fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.ba