oracle存储过程框架

create or replace
procedure sp_dm_sample( p_BU in char(1), p_xxx in varchar(100), p_xxx out number, .... ,
      p_xxx in char(3), ...
      ... )
as
 v_row_count  INTEGER default 0;
 
 -- declare for XXX table ----------------------------
 v_xxx   CHAR(8);
 v_xxx   VARCHAR(3);
 ...
 
 -- declare for YYY table ----------------------------
 v_yyy   CHAR(8);
 v_yyy   VARCHAR(3);
 ...
 
 -- delcare cursors ----------------------------------
 cursor c_xx(v_claimno CHAR)
 is
 select c.abc as xyz,   c.def,  c.claimno, ...
     p.polno,    p.effdt, ...
 from msclmreg c, msclmpol p, msclmpolcc, r
 where c.claimno = p.claimno and ...
   and c.xxx is null ...
 
 r_xx    c_xx%rowtype;
 r_yy   CCCBSC%rowtype;
 
begin
 insert into DMLOG
  (PRG, TYPE, MSG, CRNTIME)
 values
  ('sp_dm_sample', 'I', 'Starts', CURRENT TIMESTAMP);
 commit;
 
 open c_xx(v_claimno);
 loop
  fetch c_xx into r_xx;
  exit when c_xx%NOTFOUND;
  
  v_row_count := v_row_count + 1;
  
  -- (1) do ...
  v_xxx := r_xx.xyz;
  v_claimno := r_xx.claimno;
  ...
  
  -- (2) do ...
  ...
  
  -- (3)  specific logics
  if p_BU = 'A' then
   -- PAMB logics
  else
   -- PBTB logics
  end if;
  
  -- select into
  select m.duedt,m.amount
        into v_PRENDDATE,v_PRAMT
        from mpremium m, msclmpolcc mc
        where m.polno = mc.polno
          and rownum <= 1;
        exception when no_data_found then  
              v_PRENDDATE := '';  
              v_PRAMT := '';
        end;
  
       
  -- debug
  dbms_output.put_line('v_claimno ='||v_claimno ); -- for coding debug or UT debug, pls comment it after stablized
  
  insert into DMLOG          -- for debug info for production
   (PRG, TYPE, key, MSG, CRNTIME)
  values
   ('sp_dm_sample', 'D', 'not found ...' || ..., v_claimno, CURRENT TIMESTAMP);
  
  
  ....
  
  -- (5) insert into case table  ------------------------------
  insert into CCCBSC
   (BatchNo,          ClaimNo,         OccurNo,        CLMNUM,                    LANUM,            LAFN,
    ... )
  values
   (v_xxx,    v_yyy,   v_zzz,   v_claimno, ...
    ... );
   
  if mod(v_row_count, 5000) = 0 then
   commit;
  end if;
  
 end loop;

 close c_xx;
 
 insert into DMLOG
  (PRG, TYPE, MSG, CRNTIME)
 values
  ('sp_dm_sample', 'I', 'Completed count=' || v_row_count, CURRENT TIMESTAMP);
 commit;
 
 
 -- exception handler
 exception when others then
  set sc = SQLCODE;
     set ss = SQLSTATE;
     rollback;
     insert into DMLOG
         (PRG, TYPE, "Key", MSG)
     values
         ('sp_convert_hospital_claim_medical_event',
          'E',
          v_CLAIMNO,
          'Program stoped by an exception. claim_no='||coalesce(v_CLAIMNO, '_null')
              ||', occ_no='   ||coalesce(v_OCCURNO, '_null')
              ||', SQLCODE='  ||char(sc)
              ||', SQLSTATE=' ||ss
              ||', loop No.: '||char(v_row_invol + 1));
 end;
 
end;