生活随笔
收集整理的這篇文章主要介紹了
Oracle中Cursor介绍
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
一? 概念
二? 類型
Set Serveroutput on;
在PL/SQL中run:
Sql代碼 ?
SQL>??? ??? the?policy?is ?updated?failed.?? ??? PL/SQL?procedure ?successfully?completed 2． 顯式Cursor：
Sql代碼 ?
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 ;?? ????curPolicyInfo?cur_policy%rowtype;?? Begin ?????open ?cur_policy;??? ???Loop??? ??????? ?????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 ?cur_policy;?? ???end ?if;?? end ;???? /??
Sql代碼 ?
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 ;??/??
Sql代碼 ?
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 ;?? Begin ?????For ?rec_Policy?in ?cur_policy?loop?? ???????Dbms_Output.put_line(rec_policy.policy_code);?? ???end ?loop;?? ???Exception??? ?????when ?others?then ?? ?????????Dbms_Output.put_line(Sqlerrm);?? ??????????? end ;???? /??
Sql代碼 ?
SQL>??? ??? 8780203932?? 8780203227?? 8780203218?? 8771289268?? ??? PL/SQL?procedure ?successfully?completed??
Sql代碼 ?
Set ?serveroutput?on ;???? Declare ???????? ????type?cur_type?is ?ref?cursor ;?? ????cur_policy?cur_type;?? ????sqlStr?varchar2(500);?? ????rec_policy?t_contract_master%rowtype;?? begin ??????? ???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 ?cur_policy?for ?sqlStr;?? ??loop?? ???????fetch ?cur_policy?into ?rec_policy.policy_code,?rec_policy.applicant_id,?rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;?? ???????exit?when ?cur_policy%notfound;?? ????????? ???????Dbms_Output.put_line('Policy_code:' ||rec_policy.policy_code);?? ???? ??end ?loop;?? close ?cur_policy;???????? end ;??/??
Sql代碼 ?
1.??錯?誤?名?稱?錯誤代碼????錯?誤?含?義????? 2.??CURSOR_ALREADY_OPEN?ORA_06511???試圖打開已經打開的游標????? 3.??INVALID_CURSOR??ORA_01001???試圖使用沒有打開的游標????? 4.??DUP_VAL_ON_INDEX????ORA_00001???保存重復值到惟一索引約束的列中????? 5.??ZERO_DIVIDE?ORA_01476???發生除數為零的除法錯誤????? 6.??INVALID_NUMBER??ORA_01722???試圖對無效字符進行數值轉換????? 7.??ROWTYPE_MISMATCH????ORA_06504???主變量和游標的類型不兼容????? 8.??VALUE_ERROR?ORA_06502???轉換、截斷或算術運算發生錯誤????? 9.??TOO_MANY_ROWS???ORA_01422???SELECT …INTO …語句返回多于一行的數據????? 10.?NO_DATA_FOUND???ORA_01403???SELECT …INTO …語句沒有數據返回????? 11.?TIMEOUT_ON_RESOURCE?ORA_00051???等待資源時發生超時錯誤????? 12.?TRANSACTION_BACKED_OUT??ORA_00060???由于死鎖，提交失敗????? 13.?STORAGE_ERROR???ORA_06500???發生內存錯誤????? 14.?PROGRAM_ERROR???ORA_06501???發生PL/SQL內部錯誤????? 15.?NOT_LOGGED_ON???ORA_01012???試圖操作未連接的數據庫????? 16.?LOGIN_DENIED????ORA_01017???在連接時提供了無效用戶名或口令?
總結
以上是生活随笔 為你收集整理的Oracle中Cursor介绍 的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網站內容還不錯，歡迎將生活随笔 推薦給好友。
