037 - [Oracle PL/SQL] Procedure - Exception handling in multiple blocks

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

delete products;
select * from products;

create or replace procedure add_products (
    p_prod_id number,
    p_prod_name varchar2:='Ukowun',
    p_prod_type varchar2 default 'Ukowun')
is
begin

    insert into products values (p_prod_id,p_prod_name,p_prod_type);
    dbms_output.put_line(p_prod_id||' '||p_prod_name||'  inserted ' );
    commit;

    exception
        when others then
        dbms_output.put_line ('error in insert '||p_prod_id||' '||p_prod_name);
        dbms_output.put_line (sqlcode);
        dbms_output.put_line (sqlerrm);
end;

 

 

 

begin
    add_products(10,'PC');       --<<< 정상
    add_products(10,'Labtop');   --<<< 오류
    add_products(20,'Keyboard'); --<<< 정상
end; 
-------------------------------------------------------
10 PC  inserted 
error in insert 10 Labtop
-1
ORA-00001: unique constraint (HR.PRODUCTS_PK) violated
20 Keyboard  inserted 


# 2번째 데이터인 Laptop 은 오류가 발생하여 삽입되지 않았다.
# 하지만 예외처리 조건이 있어서 3번째 데이터는 입력되었음
select * from products;
-----------------------------------------------------
   PROD_ID PROD_NAME            PROD_TYPE           
---------- -------------------- --------------------
        20 Keyboard             Ukowun              
        10 PC                   Ukowun

 

 

 

# procedure 구조를 아래와 같이 예외처리를 제외하고 수정한다.

delete products;
select * from products;


create or replace procedure add_products
(
    p_prod_id number,
    p_prod_name varchar2:='Ukowun',
    p_prod_type  varchar2 default 'Ukowun'
)
is
begin

    insert into products values (p_prod_id,p_prod_name,p_prod_type);
    dbms_output.put_line(p_prod_id||' '||p_prod_name||'  inserted ' );
    commit;
end;

 

 

 

begin
    add_products(10,'PC');        --<<< 정상
    add_products(10,'Labtop');    --<<< 오류발생하고 종료
    add_products(20,'Keyboard');  --<<< 앞에서 오류가 발생하여 종료되어 실행되지 않음
end;


--예외처리를 하지 않았으므로 2번째 데이터 삽입에서 오류가 발생하고 암묵적인 블록은 종료
--3번째 삽입은 insert를 시도도 못함.
select * from products;
-----------------------------------------------------
   PROD_ID PROD_NAME            PROD_TYPE           
---------- -------------------- --------------------
        10 PC                   Ukowun

 

 

 

# 이번에는 commit 부분까지 제외하여 수정한다

delete products;
select * from products;


create or replace procedure add_products
(   
    p_prod_id number,
    p_prod_name varchar2:='Ukowun',
    p_prod_type  varchar2 default 'Ukowun'
)
is
begin

  insert into products values (p_prod_id,p_prod_name,p_prod_type);
  dbms_output.put_line(p_prod_id||' '||p_prod_name||'  inserted ' );
  --commit 없음.

end;

 

 

 

begin
    add_products(10,'PC');       --<<< 정상
    add_products(10,'Labtop');   --<<< 오류 및 종료
    add_products(20,'Keyboard'); --<<< 미실행
    commit;                      --<<< 미실행
end;


--2번째 데이터 입력에서 오류가 나서 해당 블록이 종료되므로 다음 명령어들은 모두 미실행됨.
--그래서 commit 도 실행되지 않아서 rollback 됨.
--최종적으로 아무런 데이터도 입력되지 않고 종료됨.
select * from products;
-----------------------------------------------------
no rows selected