Oracle 游标的使用总结

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;