Oracle中Cursor介绍.docx

Oracle中Cursor介绍 原创作者: hwhuang 阅读:1101次 评论:0条 更新时间:2010-05-03 关键字 l概念 l类型 l异常处理 一 概念 游标是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. 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） 对于从数据库中提取多行数据，就需要使用显式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. 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;-定义游标变量 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;-定义游标变量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（动态游标）： 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. Setserveroutputon; 2. 3. Declare4. -definecursortypename 5. typecur_typeisrefcursor; 6. cur_policycur_type; 7. sqlStrvarchar2(500); 8. rec_policyt_contract_master%rowtype; 9. begin10. -define动态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 动态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