oracle 存储过程

create or replace procedure count_retire is

 nowYear varchar2(20);
 myCusor   SYS_REFCURSOR;
--totals   varchar2(20);
begin
 
 
 select to_char(sysdate,'yyyy') into nowYear from dual;
 delete  TJ_RETIREMAN a where a.data_year=  nowYear;
   commit;
 
 for myCusor in (
   select RAWTOHEX(SYS_GUID()) ID ,m.rpr_type,
       MAX(DECODE(m.sex, '1', m.total, 0)) MALE,
       MAX(DECODE(m.sex, '2', m.total, 0)) FEMALE,
       MAX(DECODE(m.sex, '1', m.total, 0))+ MAX(DECODE(m.sex, '2', m.total, 0)) total,
       m.commname,m.commno
  from (select
               RAWTOHEX(SYS_GUID()) ID,          
               count(*) as total,      
               t.sex,
               t.commno,
               c.commname,
               t.rpr_type
          from SQ_GE_PERSONINFO t,
               SQ_GE_COMM C
         where t.commno = c.commno
           and (t.is_del <> '-1' or t.is_del is null)
           and t.birth_date is not null
        -- and (EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM t.birth_date) + 1 >= 50 and
        --     t.sex = '2')
        --  or (EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM t.birth_date) + 1 >= 60 and
        --    t.sex = '1')
         group by t.commno, c.commname, t.rpr_type, t.sex
      )m
         
         group by m.rpr_type,m.commname,m.commno )
         
 
         
--插入数据
loop
    insert into TJ_RETIREMAN
      (
     id,
      commno,
      rpr_type,
       total,
      DATA_YEAR,
      commname,
      male_num,
      female_num
      )
    values
      (
       myCusor.id,
       myCusor.commno,
       myCusor.rpr_type,
       myCusor.total,
       nowYear,
       myCusor.commname,
       myCusor.male,
       myCusor.female
       );
        end loop;
          commit;
 
end count_retire;