[獨孤九劍]Oracle知識點梳理(一)表空間、用戶
[獨孤九劍]Oracle知識點梳理(二)數據庫的連接
[獨孤九劍]Oracle知識點梳理(三)導入、導出
[獨孤九劍]Oracle知識點梳理(四)SQL語句之DML和DDL
[獨孤九劍]Oracle知識點梳理(五)數據庫常用對象之Table、View
[獨孤九劍]Oracle知識點梳理(六)數據庫常用對象之Procedure、function、Sequence
[獨孤九劍]Oracle知識點梳理(七)數據庫常用對象之Cursor
[獨孤九劍]Oracle知識點梳理(八)常見Exception
[獨孤九劍]Oracle知識點梳理(九)數據庫常用對象之package
[獨孤九劍]Oracle知識點梳理(十)%type與%rowtype及常用函數
游標的使用場景大致有:
a) 顯示游標:function或procedure中,用於獲取某些值進行遍歷操作
b) 動態游標:procedure中,用於返回查詢結果
1 declare 2 cursor myCursor is 3 select name from person; 4 vNames varchar2(128); 5 begin 6 --遍歷游標 7 for c in myCursor loop --隱式打開、關閉游標 8 vNames:=vNames||','||c; -- 可以包含復雜邏輯 9 end loop; 10 end;
1 declare 2 cursor myCursor is --定義游標 3 select * from person; 4 my_c myCursor%rowtype --定義游標變量 5 begin 6 open myCursor; 7 loop 8 fetch myCursor into my_c; 9 exit when c%notfound; 10 /*code here*/ --編寫復雜邏輯 11 end loop; 12 13 Exception 14 when others then 15 close myCursor; 16 17 18 if myCursor%isopen then 19 close myCursor; 20 end;
上面寫的兩個列子比較簡單,下面是網上摘抄顯示游標例子,很詳細:
對於顯式游標的運用分為四個步驟:
以下是三種常見顯式Cursor用法。
1)Set serveroutput on;
1 declare 2 ---define Cursor 3 Cursor cur_policy is 4 select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account 5 from t_contract_master cm 6 where cm.liability_state = 2 7 and cm.policy_type = 1 8 and cm.policy_cate in ('2','3','4') 9 and rownum < 5 10 order by cm.policy_code desc; 11 curPolicyInfo cur_policy%rowtype;---定義游標變量 12 Begin 13 open cur_policy; ---open cursor 14 Loop 15 --deal with extraction data from DB 16 Fetch cur_policy into curPolicyInfo; 17 Exit when cur_policy%notfound; 18 19 Dbms_Output.put_line(curPolicyInfo.policy_code); 20 end loop; 21 Exception 22 when others then 23 close cur_policy; 24 Dbms_Output.put_line(Sqlerrm); 25 26 if cur_policy%isopen then 27 --close cursor 28 close cur_policy; 29 end if; 30 end; 31 32 /
2) Set serveroutput on;
1 declare 2 Cursor cur_policy is 3 select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account 4 from t_contract_master cm 5 where cm.liability_state = 2 6 and cm.policy_type = 1 7 and cm.policy_cate in ('2','3','4') 8 and rownum < 5 9 order by cm.policy_code desc; 10 v_policyCode t_contract_master.policy_code%type; 11 v_applicantId t_contract_master.applicant_id%type; 12 v_periodPrem t_contract_master.period_prem%type; 13 v_bankCode t_contract_master.bank_code%type; 14 v_bankAccount t_contract_master.bank_account%type; 15 Begin 16 open cur_policy; 17 Loop 18 Fetch cur_policy into v_policyCode, 19 v_applicantId, 20 v_periodPrem, 21 v_bankCode, 22 v_bankAccount; 23 Exit when cur_policy%notfound; 24 25 Dbms_Output.put_line(v_policyCode); 26 end loop; 27 Exception 28 when others then 29 close cur_policy; 30 Dbms_Output.put_line(Sqlerrm); 31 32 if cur_policy%isopen then 33 close cur_policy; 34 end if; 35 end; 36 /
3)Set serveroutput on;
1 declare 2 Cursor cur_policy is 3 select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account 4 from t_contract_master cm 5 where cm.liability_state = 2 6 and cm.policy_type = 1 7 and cm.policy_cate in ('2','3','4') 8 and rownum < 5 9 order by cm.policy_code desc; 10 Begin 11 For rec_Policy in cur_policy loop 12 Dbms_Output.put_line(rec_policy.policy_code); 13 end loop; 14 Exception 15 when others then 16 Dbms_Output.put_line(Sqlerrm); 17 18 end; 19 20 /
與隱式Cursor,顯式Cursor的區別:
1 Declare 2 ---define cursor type name 3 type cur_type is ref cursor; 4 cur_policy cur_type; 5 sqlStr varchar2(500); 6 rec_policy t_contract_master%rowtype; 7 begin 8 ---define 動態Sql 9 sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,
cm.bank_account from t_contract_master cm 10 where cm.liability_state = 2 11 and cm.policy_type = 1 12 and cm.policy_cate in (2,3,4) 13 and rownum < 5 14 order by cm.policy_code desc '; 15 ---Open Cursor 16 open cur_policy for sqlStr; 17 loop 18 fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,
rec_policy.bank_code,rec_policy.bank_account; 19 exit when cur_policy%notfound; 20 21 Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code); 22 23 end loop; 24 close cur_policy; 25 26 end; 27 /
另外,在定義package時,可以在包頭中定義動態游標類型,在包體中使用,如在procedure中用於定義返回參數類型。詳見package的操作。