你的位置:首页 > 数据库

[数据库][独孤九剑]Oracle知识点梳理(七)数据库常用对象之Cursor


本系列链接导航:

[独孤九剑]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 then19     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的操作。