8. PL/SQL 예외처리

8. PL/SQL 예외처리

 

A. 예외란?

 

- SELECT 문이 행을 하나만 읽어 들일 것으로 예상했지만 여러 행을 읽어 오류 발생하며 블록이 종료됨

- 위와같이 런타임에 발생하는 오류를 예외라고 한다.

ex)

declare

  v_lname varchar2(15);

begin

  select last_name into v_lname

  from employees

  where first_name=‘John’;

  dbms_output.put_line (‘John’’s last name is : ‘ || v_lname);

exception

  when too_many_rows then

  dbms_output.put_line (‘ Your select statement retrleved multiple rows. Consider using a cursor. ‘);

end;

/

 

B. PL/SQL 예외 이해

§예외는 프로그램 실행 중에 발생한 PL/SQL 오류

§예외는 다음과 같이 발생

§Oracle 서버에서 묵시적으로 발생

§프로그램에 의해 명시적으로 발생

§예외를 다음과 같이 처리

§처리기로 트랩

§호출 환경으로 전달

 

C. 예외 유형

§묵시적으로 발생

§미리 정의된 Oracle 서버 오류

§미리 정의되지 않은 Oracle 서버 오류

§명시적으로 발생

§사용자 정의

 

D. 예외 트랩

§Syntax

EXCEPTION

  WHEN exception1 [OR exception2 …] THEN

  statement1;

  statement2;

  …

  [WHEN exception3 [OR exception4 …] THEN

  …

  [WHEN OTHERS THEN

  statement1;

  statement2;

  …] 

 

 E. 미리 정의된 Oracle 서버 오류

 

§미리 정의된 이름을 참조

§Sample

§NO_DATA_FOUND

§TOO_MANY_ROWS

§INVALID_CURSOR

§ZERO_DEVIDE

§DUP_VAL_ON_INDEX

 

F. 미리 정의되지 않은 Oracle 서버 오류 트랩

- 미리 정의되지 않은 예외는 표준 Oracle 오류.

- PRAGMA EXCEPTION_INIT 함수를 사용하여 표준 Oracle 오류가 있는 예외를 생성

ex)

declare

  e_insert_excep EXCEPTION;

  PRAGMA EXCEPTION_INIT(e_insert_excep, -01400);

begin

  insert into departments

  (department_id, department_name) values (280, NULL);

exception

  when e_insert_excep then

    dbms_output.put_line(‘INSERT OPERATION FAILED’);

    dbms_output.put_line(SQLERRM);

end;

/

 

G. 예외 트랩에 대한 함수

§SQLCODE : 오류 코드에 대한 숫자 값을 반환합니다.

§SQLERRM : 오류 번호와 연관된 메시지를 반환합니다.

ex)

DECLARE
error_code NUMBER;
error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK;
error_code := SQLCODE ;
error_message := SQLERRM ;
INSERT INTO errors (e_user, e_date, error_code,
error_message) VALUES(USER,SYSDATE,error_code,
error_message);
END;
/

 

H. 유저 정의 예외 트랩

- 응용 프로그램의 요구사항에 따라 고유의 예외를 정의 가능

ex)

declare

  v_deptno number := 500;

  v_name varchar2(20) := ‘Testing’;

  e_invalid_dept exception;

begin

  update departments set department_name = v_name

  where department_id = v_deptno;

  if sql%notfound then

    raise e_invalid_dept;

  end if;

  commit;

exception

  when e_invalid_dept then

    dbms_output.put_line(‘No such department id.’);

end;

/

 

I. 서브 블록의 예외 전달

- 서브 블록은 예외를 처리하거나, 포함하는 블록으로 예외를 전달 할 수 있다.

DECLARE
    . . .
    e_no_rows exception;
    e_integrity exception;
    PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
    FOR c_record IN emp_cursor LOOP
        BEGIN
            SELECT ...
            UPDATE ...
            IF SQL%NOTFOUND THEN
                RAISE e_no_rows;
            END IF;
        END;
    END LOOP;
EXCEPTION
    WHEN e_integrity THEN ...
    WHEN e_no_rows THEN ...
END;
/

 

J. RAISE_APPLICATION_ERROR 프로시저

§Syntax

raise_application_error (error_number,

message[, {TRUE | FALSE} ] );

§error_number : 예외에 대한 유저 지정번호 (-20,000 ~ -20,999)

§message : 유저가 지정한 예외 메시지

§비표준 오류 코드 및 오류 메시지를 반환하여 미리 정의된 예외가 반환되지 않도록 할 수 있음.

§다음과 같은 위치에서 사용

§실행 섹션

§예외 섹션

§Oracle 서버 오류와 일치하는 방식으로 유저에게 오류 조건을 반환.

- 실행 섹션에서의 사용

BEGIN
...
    DELETE FROM employees
        WHERE manager_id = v_mgr;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager');
    END IF;
...

- 예외 섹션에서의 사용

...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.');
END;
/

ex)

declare

  no_emp exception;

  pragma exception_init(no_emp, -20001);

begin

  delete from employees

  where employee_id = 9999;

  if sql%notfound then

    raise_application_error(-20001, ‘What!?!?!?!’);

  end if;

exception

  when no_emp then

    dbms_output.put_line(‘no data found’);

    dbms_output.put_line(sqlerrm);

end;

/