存储过程例子

create or replace procedure day_street_kh

 is
  yesterday varchar2(30);
  maxTime varchar2(30);
  myCusor SYS_REFCURSOR;

begin
  --参数赋值

  select to_char(sysdate - 1, 'yyyy-MM-dd') into yesterday from dual;
  dbms_output.put_line(yesterday);
 
  select max(t.ymtime) into maxTime from cr_exam_day_stats_bak t ;
   dbms_output.put_line(maxTime);
   
   --插入前删除cr_exam_day_stats 所有数据，
  delete from  cr_exam_day_stats t where  t.typekind='1';
  -- 删除存在的同一天数据
 if(yesterday=maxTime)then
     
    delete from  cr_exam_day_stats_bak t where t.ymtime=yesterday and t.typekind='1';
 
   end if;   

  --游标赋值
  for myCusor in (
    select
       RAWTOHEX(SYS_GUID()) ID,
      '' commname,
        nvl(b.ymtime,yesterday)ymtime,
       nvl(b.loginnum, 0) loginnum,
       nvl(b.updatenum, 0) updatenum,
       nvl(b.slnum, 0) slnum,
       nvl(b.cznum, 0) cznum,
       nvl(b.wxuserNum, 0) wxuserNum,
       nvl(b.userNum, 0) userNum,
       nvl(b.wxUpdateNum, 0) wxUpdateNum,
       '' commno,
       a.streetname,
       1 typekind,
       0  orgnum,
       0  sernum
  from (select distinct t.streetname, t.streetno from SDE.COMMR t) a
  left join

 (select t.streetname,
         t.ymtime,
         sum(t.loginnum) loginnum,
         sum(t.updatenum) updatenum,
         sum(t.slnum) slnum,
         sum(t.cznum) cznum,
         sum(t.wxuserNum) wxuserNum,
         sum(t.userNum) userNum,
         sum(t.wxUpdateNum) wxUpdateNum
    from cr_exam_day_stats t where t.ymtime=yesterday
   group by t.streetname, t.ymtime) b
    on a.streetname = b.streetname
    )
 
    
  --插入数据
 
   loop
    insert into cr_exam_day_stats
      (id,
       commname,
       ymtime,
       loginnum,
       updatenum,
       slnum,
       cznum,
       wxusernum,
       usernum,
       wxupdatenum,
       commno,
       streetname,
       typekind,
       orgnum,
       sernum)
    values
      (myCusor.id,
       myCusor.commname,
       myCusor.ymtime,
       myCusor.loginnum,
       myCusor.updatenum,
       myCusor.slnum,
       myCusor.cznum,
       myCusor.wxusernum,
       myCusor.usernum,
       myCusor.wxupdatenum,
       myCusor.commno,
       myCusor.streetname,
       myCusor.typekind,
       myCusor.orgnum,
       myCusor.sernum);
     --插入到数据备份表
    insert into cr_exam_day_stats_bak
      (id,
       commname,
       ymtime,
       loginnum,
       updatenum,
       slnum,
       cznum,
       wxusernum,
       usernum,
       wxupdatenum,
       commno,
       streetname,
       typekind,
       orgnum,
       sernum)
    values
      (myCusor.id,
       myCusor.commname,
       myCusor.ymtime,
       myCusor.loginnum,
       myCusor.updatenum,
       myCusor.slnum,
       myCusor.cznum,
       myCusor.wxusernum,
       myCusor.usernum,
       myCusor.wxupdatenum,
       myCusor.commno,
       myCusor.streetname,
       myCusor.typekind,
       myCusor.orgnum,
       myCusor.sernum);
 end loop;
  commit;
end day_street_kh;