Elephant11

DROP   PROCEDURE Proc_master;
CREATE PROCEDURE Proc_master
  (sSIPNUM in  varchar2)
IS
begin
    begin
      insert into imms.mc_comsip_m
        (SIPNUM) values (sSIPNUM);
    exception
       when DUP_VAL_ON_INDEX then -- key 중복 error 일 경우 update 수행
            rollback; 
            raise_application_error(-20502, 'insert other error...DUP');
       when OTHERS then
            rollback;
            raise_application_error(-20502, 'insert other error...');         
    end;  
--    begin
    
--    end; 
        
END Proc_master;

==========================================================================================

DROP   PROCEDURE Proc_test;
CREATE PROCEDURE Proc_test
  (sPJTCOD     in  varchar2,
   sSTART_YM   in  varchar2,
   sPRLCOA     in  varchar2,
   sPRICOD     in  varchar2,
   sP_EMPNO    in  varchar2,
   sMsg       out varchar2)
IS
  sOPTTYP varchar2(10);

CURSOR  M1 IS
  select OPTTYP from imms.MC_POSUM_D
   where PJTCOD   = sPJTCOD  
     and START_YM = sSTART_YM
     and PRLOCA   = sPRLCOA  
     and PRICOD   = sPRICOD  
     and P_EMPNO  = sP_EMPNO;
begin    

  begin
    Insert into imms.MC_POSUM
      (select * from imms.MC_POSUM
        where PJTCOD   = sPJTCOD  
          and START_YM = sSTART_YM
          and PRLOCA   = sPRLCOA  
          and PRICOD   = sPRICOD  
          and P_EMPNO  = sP_EMPNO);
          
  end;

  open M1;
  Loop
    FETCH M1
      into sOPTTYP;

      begin    
        Insert into imms.MC_POSUM_D
          (select * from imms.MC_POSUM_D     
            where PJTCOD   = sPJTCOD       
              and START_YM = sSTART_YM     
              and PRLOCA   = sPRLCOA       
              and PRICOD   = sPRICOD       
              and P_EMPNO  = sP_EMPNO
              and OPTTYP   = sOPTTYP);    
     exception
       when DUP_VAL_ON_INDEX then -- key 중복 error 일 경우 update 수행
            rollback; 
            raise_application_error(-20502, 'insert other error...DUP');
            sMsg := '-20502, insert other error...';
       when OTHERS then
            rollback;
            raise_application_error(-20502, 'insert other error...');         
            sMsg := '-20502, insert other error...';
     end;
      
  End Loop;      
  Commit;
  
close M1;

        
END Proc_test;

==========================================================================================

PROCEDURE SAMPLE_TEST IS
  aa varchar2(10) := 'test';
  bb varchar2(10) := 'test1';
  cc varchar2(10) := 'test2';
BEGIN
  begin
     insert into test_tab(a, b, c)
     values (aa, bb, cc);
  exception
       when DUP_VAL_ON_INDEX then -- key 중복 error 일 경우 update 수행
            begin
               update test_tab
                  set b = bb,
                      c = cc
                where a = aa;
            exception
                 when OTHERS then -- 기타 error 일 경우
                      raise_application_error(-20501, 'update error...');         
            end;
       when OTHERS then
            rollback;
            raise_application_error(-20502, 'insert other error...');         
  end;
END;