写存储过程时,会经常使用游标来处理数据,这里分享一个动态游标的使用实例,供大家参考。若有不对的或更好的方法,请多多的指正。
对于显示游标的使用,有四个步骤:
1 定义游标---Cursor [CursorName] IS;
2 打开游标---Open [CursorName];
3 操作数据---Fetch [Cursorname];
4 关闭游标---Close [Cursor Name],这个Step绝对不可以遗漏。
动态游标也都遵循这个步骤。
一般在游标数据是变化时*也就是获取游标数据的表是变化的),使用动态游标。本例中,游标数据和传入参数有关,是变化的蛮久可以用动态游标来处理。
下面是使用实例;
create or replace procedure recover2026(handingTime varchar2) is---传入参数形如20151225 按天
type ref_cursor is ref cursor;--声明动态游标类型:ref_cursor ;cur ref_cursor;--1,定义游标 ,声明ref_cursor 类型变量 cur;
v_ref_cur_sql varchar2(1000);--声明一个变量,用来存储获取游标内容的语句;
TYPE rec is record(--声明一个记录,用来得到游标的数据;
policy_no VARCHAR2(64),
status NUMBER(1)
);
recs rec;--记录类型变量
v_sql varchar2(2000);
v_sql2 varchar2(2000);v_sql3 varchar2(2000);
v_num int;
v_mod number(2);
begin
--1.从tb_policy2015XXXX,prod_no='2006' 和status 比较32套表中创建时间2015XXXX以前(不包含这一条的数据)的,
--prod_no='2006',status, policy_no 的数据
-- 如果在tb_policyserverXX中存在的话,则删除tp_policy2015XXXX的数据这条数据,
--同时删除tb_policyserverXX 中creattime='2015XXXX' and policy_no 这条数据
v_ref_cur_sql:='select policy_no,status from tb_policy'||handingTime||' where prod_no=''2026'' ' ;--动态游标内容
--OPEN cur ;
open cur for v_ref_cur_sql;--2,打开游标 open cur for string(string的执行结果即为游标内容)
LOOP
FETCH cur INTO recs;--3,操作数据,取出游标数据,将游标数据一条一条的循环复制给变量 recs;
exit when cur%notfound;
v_mod:=mod(regexp_substr(recs.policy_no,'[0-9]+'),32);--模,对应32张表
v_sql:='select count(*) from tb_policyservice'||v_mod||' t where t.policy_no='''||recs.policy_no||''' and t.status='''||recs.status||''' and t.prod_no=''2026'' and to_char(t.createtime,''yyyymmdd'')<'''||handingTime||'''';
execute immediate v_sql into v_num;
if v_num>0 then --有数据则删除tp_policy2015XXXX的数据这条数据
v_sql2:='delete tb_policy'||handingTime||' t where t.policy_no='''||recs.policy_no||''' and t.status='''||recs.status||''' and t.prod_no=''2026'' ';
execute immediate v_sql2;
commit;
v_sql3:='delete tb_policyservice'||v_mod||' t where t.policy_no='''||recs.policy_no||''' and to_char(t.createtime,''yyyymmdd'')='''||handingTime||''' and t.status='''||recs.status||'''';
execute immediate v_sql3;--同时删除tb_policyserverXX 中creattime='2015XXXX' and policy_no 这条数据
commit;
end if;
END LOOP;
CLOSE cur;
end recover2026;
说明:动态游标REF COUSOR属于引用类型,是一种数据类型,而cursor则不是。所以动态游标需要使用TYPE什么,在定义REF CURSOR类型的变量。
有好的建议或疑问,邮箱联系。