ORACLE PL/SQL의 에러 처리방법

	declare
          ....
        begin
          ....
        exception
          ....
        end;

오라클에서 제공하는 예측하여 정의한 오류 목록은 다음과 같다.

 
【예제】
$ vi zz.sql


CREATE OR REPLACE PROCEDURE test
(v_sal IN emp.sal%TYPE)
IS
  v_ename  emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE sal=v_sal;
  dbms_output.put_line('He id '¦¦ v_ename ¦¦'!!!!');
EXCEPTION
  WHEN no_data_found THEN
   raise_application_error(-20002,'Data not found....');

  WHEN too_many_rows THEN
   raise_application_error(-20003,'Too Many Rows....');

  WHEN others THEN
   raise_application_error(-20004,'Others Error....');

END;
/


SQL> start zz.sql

프로시저가 생성되었습니다.

SQL> set serveroutput on
SQL> execute test(9000);
BEGIN test(9000); END;

*
1행에 오류:
ORA-20002: Data not found....
ORA-06512: "SCOTT.TEST", 줄 10에서
ORA-06512: 줄 1에서


SQL>

【예제】
$ vi zz.sql


 create or replace procedure hire_emp
(v_emp_name     IN emp.ename%type,
 v_emp_job      IN emp.job%type,
 v_mgr_no       IN emp.mgr%type,
 v_emp_hiredate IN emp.hiredate%TYPE,
 v_emp_sal      IN emp.sal%type,
 v_emp_comm     IN emp.comm%type,
 v_dept_no      IN emp.deptno%type)
IS
 e_invalid_mgr exception;

pragma exception_init (e_invalid_mgr, -02291);

BEGIN
  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm,deptno)
   values(emp_id.nextval, v_emp_name, v_emp_job,v_mgr_no, v_emp_hiredate,
          v_emp_sal, v_emp_comm, v_dept_no);
  commit work;
  exception
   when e_invalid_mgr then
    raise_application_error(-20201, 'Deptno is not a valid department.');
end hire_emp;
/ 


SQL> create sequence emp_id start with 80;

시퀀스가 생성되었습니다.

SQL>
먼저 emp,dept 테이블의 deptno 컬럼에 promary key와 foreign key조건의 제약조건을 설정한다

SQL> alter table dept add constraint dept_deptno_pk primary key(deptno);
SQL> alter table emp add constraint deptno_emp_fk foreign key(deptno) references dept(deptno);
SQL> execute hire_emp('STONE','CLERK',9000, SYSDATE, 950, 300,44)
BEGIN hire_emp('STONE','CLERK',9000, SYSDATE, 950, 300,44); END;

*
1행에 오류:
ORA-20201: Deptno is not a valid department.
ORA-06512: "SCOTT.HIRE_EMP", 줄 21에서
ORA-06512: 줄 1에서


SQL>

사용자가 미리 에러에 대한 정의를 하는 경우이며, EXCEPTION 키워드에 의해 에러 조건명을 정의하고 RAISE 명령어에 의해 에러가 발생되면 exception 절에서 에러가 처리된다.

【예제】
$ vi test5.sql


create or replace procedure test5
(v_sal  IN emp.sal%TYPE)
IS
 v_low_sal      emp.sal%type := v_sal - 100;
 v_high_sal     emp.sal%type := v_sal + 100;
 v_no_emp       number(7,2);

 e_no_emp_returned 	exception;
 e_more_than_one_emp 	exception;

begin
 select count(ename) into v_no_emp from emp
  where sal between (v_sal - 100) and (v_sal + 100);
 if v_no_emp = 0 then
    raise e_no_emp_returned;
  elsif v_no_emp > 0 then
    raise e_more_than_one_emp;
 end if;

exception
 when e_no_emp_returned then
   dbms_output.put_line('There is no employee salary...');
 when e_more_than_one_emp then
   dbms_output.put_line('There is a row employee....');
 when others then
   dbms_output.put_line('Any other error occurred......');
end;
/ 


SQL> start test5.sql

프로시저가 생성되었습니다.

SQL> set serveroutput on
SQL> execute test5(9000)
There is no employee salary...

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> 

이 방법은 사용자가 실행한 SQL 문이 실행될 때 어떤 에러 코드와 에러 메시지가 발생하는지를 사용자가 직접 참조하여 처리하는 방법이다.
SQL 문을 실행한 후, SQLCODE 함수를 참조해 보면 SQL문의 실행 결과를 알 수 있다.

SQLCODE값의 이미는 다음과 같다.

【예제】
$ vi test7.sql


CREATE OR REPLACE PROCEDURE test7
(v_sal  IN emp.sal%TYPE)
IS
 v_ename        emp.ename%TYPE;
 v_err_code     number;
 v_err_message  varchar(255);

begin
 select ename into v_ename from emp
  where sal = v_sal;
   dbms_output.put_line('He is '¦¦ v_ename ¦¦ '....');

exception
 WHEN no_data_found THEN
   v_err_code    := SQLCODE;
   v_err_message := SQLERRM;

   dbms_output.put_line(v_err_code ¦¦ '   ' ¦¦ v_err_message);
 WHEN too_many_rows THEN
   raise_application_error(-2003, 'Too Many Rows...');
 WHEN others        THEN
   raise_application_error(-2004, 'Others Error...');
END;
/ 

SQL> start test7.sql

프로시저가 생성되었습니다.

SQL> set serveroutput on
SQL> execute test7(9000)
100   ORA-01403: 데이터를 찾을 수 없습니다.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>