declare
--定义游标
cursor tcount is
select
to_number(to_char(d_create,'yyyy')) as fyear,
vc_code as fsetid,
l_fundid as fsetcode,
vc_name as fsetname,
nvl(to_number(to_char(d_dqrq,'yyyy')),to_number(to_char(systimestamp,'yyyy'))) as fyear1,SRC
from md_tfundinfo;
--定义读取游标的变量
row_tcount tcount%rowtype;
--开始时间
s number;
--结束时间
e number;
begin
--打开游标
open tcount;
--开始循环
loop
--将游标所在行赋值给变量
fetch tcount into row_tcount;
--游标读到结尾时推出(一定要写在最前面)
exit when tcount%notfound;
--赋值
s := row_tcount.fyear;
e := row_tcount.fyear1;
--循环处理该行数据
loop
--先验证表中是否存在要插入的数据,避免重复插入
select count(*) into t from md_lsetlist where fyear=s and fsetid=row_tcount.fsetid and fsetcode=row_tcount.fsetcode
and fsetname=row_tcount.fsetname and src=row_tcount.src;
if t=0 then
insert into md_lsetlist(fyear,fsetid,fsetcode,fsetname,src)
values(s,row_tcount.fsetid,row_tcount.fsetcode,row_tcount.fsetname,row_tcount.src);
commit;
end if;
s := s+1;
exit when s>e;
end loop;
--退出循环
end loop;
--关闭游标
close tcount;
end;
Oracle 游标的使用总结
最新推荐文章于 2024-09-20 10:07:14 发布