oracle 游标

1>>>>三种常见显式Cursor用法。 
通过使用显示游标，不仅可以一行一行的处理select语句结果，而且也可以更新或删除当前游标的数据，注意，如果要通过游标更新或删除数据，在定义游标时一定要带有for update子句，语法如下： 
cursor cursor_name(parameter_name datatype) is select_statement for updae [of column_reference][nowait];如上所示：for update子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作，当select语句要引用到多张表是，使用of子句可以确定哪些表要加锁，如果没有of子句，则会在select语句所引用的全部表上加锁，nowait用于指定不等待锁，为了更新或删除当前游标行数据，必须在update 或delete语句中引用where current of 子句，语法如下： 
update table_name set column=.. where current of cursor_name; 
delete from table_name  where current of cursor_name; 


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;


/


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;
/


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;


/


2>>>>隐式游标
如：Select /Update / Insert/Delete操作。
select ... into ...




3>>>>Ref Cursor（动态游标）： 
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.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;
/