程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> [獨孤九劍]Oracle知識點梳理(七)數據庫常用對象之Cursor,oraclecursor

[獨孤九劍]Oracle知識點梳理(七)數據庫常用對象之Cursor,oraclecursor

編輯:Oracle教程

[獨孤九劍]Oracle知識點梳理(七)數據庫常用對象之Cursor,oraclecursor


本系列鏈接導航:

[獨孤九劍]Oracle知識點梳理(一)表空間、用戶

[獨孤九劍]Oracle知識點梳理(二)數據庫的連接

[獨孤九劍]Oracle知識點梳理(三)導入、導出

[獨孤九劍]Oracle知識點梳理(四)SQL語句之DML和DDL

[獨孤九劍]Oracle知識點梳理(五)數據庫常用對象之Table、View

[獨孤九劍]Oracle知識點梳理(六)數據庫常用對象之Procedure、function、Sequence

[獨孤九劍]Oracle知識點梳理(七)數據庫常用對象之Cursor

[獨孤九劍]Oracle知識點梳理(八)常見Exception 

[獨孤九劍]Oracle知識點梳理(九)數據庫常用對象之package

[獨孤九劍]Oracle知識點梳理(十)%type與%rowtype及常用函數

5.6、cursor操作

  游標的使用場景大致有:

    a) 顯示游標:function或procedure中,用於獲取某些值進行遍歷操作

    b) 動態游標:procedure中,用於返回查詢結果

5.6.1、顯示游標:在代碼段、function、procdeure中創建cursor,用於取值

  • 隱式游標
 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 [Cursor Name] IS;
  • 打開游標---Open [Cursor Name];
  • 操作數據---Fetch [Cursor name]
  • 關閉游標---Close [Cursor Name],這個Step絕對不可以遺漏。

  以下是三種常見顯式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 / 

5.6.2、動態游標:在procdeure中使用cursor,用於返回查詢結果

  與隱式Cursor,顯式Cursor的區別:

  • Ref Cursor是可以通過在運行期間傳遞參數來獲取數據結果集。
  • 而另外兩種Cursor(隱式游標和顯示游標),是靜態的,在編譯期間就決定數據結果集。

5.6.2.1、定義動態游標

 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的操作。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved