3. Ref Cursor(动态游标): 1) 与隐式Cursor,显式Cursor的区别:Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor,是静态的,在编译期间就决定数据结果集。 2) Ref cursor的使用: ? Type [Cursor type name] is ref cursor ? Define 动态的Sql语句 ? Open cursor ? 操作数据---Fetch [Cursor name] ? Close Cursor 下面是一个Sample:
- 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;
- /
|