Exception handling

오라클에서의 에러는 다음과 같이 두 종류가 있다.
- Syntax Error
- Runtime Error (Exception) 

Every Oracle error has a number, but exceptions must be handled by name
모든 오라클 에러는 번호가 있지만, Exception은 반드시 이름을 가지고 처리한다.


   ○ Oracle defined error : Predefined exception                   1.When name then
                                      Non-predefined exception            2.When others then
                                                                                       3.After naming exception
   ○ User defined error                                                       4.create user defined exception
                                                                                       5.DBMS_STANDARD.RAISE_APPLICATION_ERROR


1번(Predefined exception)과 2번(Non-predefined exception) 예제

오라클의 대표적인 predefined exception 들이다.

NO_DATA_FOUND       리턴되는 행이 없을 때
TOO_MANY_ROWS     많은 행을 리턴할 때
INVALID_CURSOR       유효하지 않은 커서
ZERO_DIVIDE             0으로 나눌때
DUP_VAL_ON_INDEX  중복된 값을 입력시

drop table t1 purge;
create table t1 (col1 number constraint t1_no_nn not null);

create or replace procedure p1
          (p_no number,
           p_no2 number)
is
    v_no number;
begin
    v_no := 100/p_no;
    dbms_output.put_line(v_no);

    insert into t1 values (p_no2);
       exception
           when ZERO_DIVIDE then          -- predefined exception
                dbms_output.put_line('Your program attempts to divide a number by zero.');
           when others then                     -- non predefined exception
                dbms_output.put_line('Your program attempts to insert to null value');
end;
/

exec p1 (10, 1);
exec p1(10, 0);                --> ZERO_DIVIDE exception발생
exec p1 (10, NULL);        --> ORA-01400 상황

3번,Non-predefined exception(Atfer naming exception)

PRAGMA : 컴파일러에 대한 슈도명령어,
패키지에 선언해 두면 모든 plsql에서 exception으로 처리할 수 있다.

프로시져 내에서 Exception 정의

테이블 삭제후 생성

drop table t1 purge;
create table t1 (col1 number constraint t1_no_nn not null);

create or replace procedure p1
 (p_no number)
is
 e_null_insert exception;
 pragma exception_init(e_null_insert, -1400);

 v_no number;
begin
 insert into t1 values (p_no);
exception
 when e_null_insert then
  dbms_output.put_line('Your program attempts to insert a null into t1.col1');
end;
/

exec p1(null)     --> ORA-01400

Excetion을 package로 만듬

create or replace package exception_package
is
  e_null_insert exception;
  pragma exception_init(e_null_insert, -1400);
end;
/

 

create or replace procedure p1
  (p_no  number)
is
  v_no number;
begin
  insert into t1 values (p_no);
exception
  when exception_package.e_null_insert then
    dbms_output.put_line('Your program attempts to insert a null into hr30.t1.col1');
end;
/

exec p1 (NULL)

4번 사용자 정의 예외(User defined error)예제

먼저 아래의 SQL로 테이블의 상황을 보여주면

select sum(salary), department_id
     from employees
     group by department_id
     order by department_id

SUM(SALARY)	DEPARTMENT_ID
4400	10
19000	20
17500	50
19200	60
30100	80
58000	90
20300	110
7000

create or replace procedure p2
 (p_dept_id number)
is
 v_sum_salary number;
 e1       exception;
begin
 select sum(salary) into v_sum_salary
 from employees
 where department_id = p_dept_id;

 if v_sum_salary < 20000 then
  raise e1;                         -- 예외(exception)를 발생시킨다.
 end if;

dbms_output.put_line(v_sum_salary);

exception
 when e1 then
  dbms_output.put_line('The total salary is too low!');

end;
/

exec p2(90);     --> 정상처리됨.
exec p2(10);     --> department_id 10의 sum(salary)가 4400으로 20000보다 적으므로 예외 발생

5.DBMS_STANDARD.RAISE_APPLICATION_ERROR예제

마치 oracle의 non-predicated exception이 발생한 것처럼 처리

declare
 v_sum_salary number;
 e1 exception;
begin
 select sum(salary) into v_sum_salary
 from employees
 where department_id = 20;

 if v_sum_salary < 20000 then
  dbms_standard.raise_application_error(-20001, 'too low');
 end if;
end;
/

 

declare
 v_sum_salary number;
 e1 exception;
 pragma exception_init(e1, -20001);
begin
 select sum(salary) into v_sum_salary
 from employees
 where department_id = 20;


 if v_sum_salary < 20000 then
  dbms_standard.raise_application_error(-20001, 'too low');   /* raise e1; */
 end if;

 dbms_output.put_line(v_sum_salary);
exception
 when e1 then
  dbms_output.put_line('exception arised!');
end;
/

오라클home\RDBMS\ADMIN의 아래에
stdspec.sql
predefined exception로 검색하면 exception이 선언되어 있음

참조 : http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm#784
관련 질문 : http://kin.naver.com/detail/detail.php?d1id=1&dir_id=10110&docid=1105489&qb=7ZaJ7Jq07J20IOyeiOq4sOulvCBleGNlcHRpb24=&enc=utf8&section=kin&rank=10&sort=0&spq=0