ORACLE PL/SQL 예외 처리

1. 예외 처리

  1.1 PL/SQL의 오류 처리 방법
PL/SQL에서 프로그래램의 모든 오류는 예외이다.
* 예외의 종류
- 시스템 오류(예를 들어, '메모리 초과', '인덱스의 중복 키')
- 사용자 오류
- 사용자에게 보내는 어플리케이션 경고

PL/SQL은 예외 처리기 구조를 이용하여 오류를 잡아내서 처리하는데, 선형코드 모델(linear code model)의 반대 개녀념인 이벤트 지향 모델(event-driven model)로 오류를 처리한다. 어떤 예외가 발생하든지 간에, 예외부의 동일 예외 처리기로 처리가 된다.

     1.1.1 예외처리 전략 선택하기
오류 처리를 위한 일관되딘 전략과 구조를 세우는 것이 중요하다
다음 질문을 고려해야한다
- 오류를 검토해서 수정할 수 있게 하려면 오류를 언제, 어떻게 남길것인가
- 사용자에게 오류가 발생했음을 언제, 어떻게 알려줄 것인가
- pl/sql 모든 블록에 예외 처리부를 만들 것인가
- 최고 레벨이나 가장 바깥쪽 블럭에서만 예외 처리부를 만들 것인가
- 오류 발생시 트랜잭션을 어떻게 관리할 것인가

     1.1.2 예외 처리 개념과 용어
일반적으로 두 가지 형태의 예외가 있다
- 시스템 예외 : 오라클이 정의하는 오류, 보통 pl/sql 실행 시간 엔진이 오류 조건을 탐지하여 발생하는 예외다. 어떤 시스템 예외는 NO_DATA_FOUND처럼 이름이 있긴 하지만 대부분 예외는 단순히 번호와 설명만 있다.
- 프로그래머 정의 예외 : 프로그래머가 정의하는 예외로, 애플리케이션마다 다르다. EXCEPTION_INIT 프라그마를 이용하여 특정 오라클 오류와 예외 이름을 연관짓거나 RAISE_APPLICATION_ERROR를 이용하여 해당 오류에 번호와 설명을 할당할 수도 있다.

  1.2 예외 정의하기
예외가 정의되어야만 예외가 발생되거나 처리될 수 있다. 미리 정의된 예외 대부분에는 번호와 메시지가 할당되어 있다.

     1.2.1 이름이 있는 예외 선언하기
PL/SQL이 STANDARD 패키지에 선언한 예외는 내부 오류나 시스템 발생 오류와 관련이 있다. 그러나 애플리케이션에서 사용자가 만나는 문제 대부분은 해당 애플리케이션에 따라 다르다. 일단 오류가 발생하면 유형이나 소스에 상관없이 예외부에서 처리된다.

예외 처리를 위해서는 예외명을 알아야 하며 PL/SQL선언부에서 예외를 선언해야한다.
선언방법

예외명 EXCEPTION;

예외명 형식은 불린 변수명 형식과 유사하며 참조할 수 있는 방법은 두가지 뿐이다.
- 프로그램 실행부(예외 발생 부분)의 RAISE 문에서의 방법

RAISE 예외명;

- 예외부(에러를 처리하는 부분)의 WHEN 구문에서의 방법

WHEN 예외명 THEN

     1.2.2 예외명과 오류 코드 연관짓기
예외에 이름이 없어도 상관없지만, 이름이 없는 예외는 코드 파익이나 관리를 어렵게 만든다.
예로 ORA-01843: not a valid month 같은 날짜 관련 오류 발생을 파악한 프로그램을 작성한다면

EXCEPTION   WHEN OTHERS THEN     IF SQLCODE = -1843 THEN

위와 같은 예외 처리기를 작성할 수 있다. 하지만 불분명한 코드로써 주석이 필요하다.
(SQLCODE는 발생한 오류 중 마지막 오류 번호를 리턴하는 내장 함수다)

     1.2.3 EXCEPTION_INIT 활용에 대한 권장사항
EXCEPTION_INIT 프라그마를 이용하여 위의 예제의 WHEN구문을 변경 가능하다.

EXCEPTION   WHEN invalid_month THEN

기억하기 힘든 리터럴 오류 번호를 하드코딩할 필요없이 코드이름으로 파악할 수 있게 되었다.

EXCEPTION_INIT은 컴파일 시간 명령어 또는 예외명과 내부 오류 코드를 연관짓는 프라그마로 컴파일러에게 EXCEPTION으로 선언된 식별자를 지정된 오류 번호와 연관짓게 명령한다.

문법

DECLARE   예외_이름 EXCEPTION;   PRAGMA EXCEPTION_INIT (예외_이름, 정수);

정수는 리터럴 정수 값으로 이름 붙여진 예외와 연관짓고자 하는 오라클 오류 번호 값이다.

오류번호 제약사항
- -1403(NO_DATA_FOUND 오류 코드)를 사용할 수 없다.
- 0이 될수 없고 1을 제외한 어떤 양수도 될수 없다.
- -10000000 보다 작은 음수가 될 수 없다.

     1.2.4 EXCEPTION_INIT의 활용 추천
EXCEPTION_INIT 프라그마는 다음 두 상황에서 가장 유용하다
- 코드에서 자주 참조되는 다른 익명 시스템 예외에 이름을 부여할 경우.
- RAISE_APPLICATION_ERROR로 발생하는 애플리케이션 지정 오류에 이름을 할당할 경우

위의 두 경우에 EXCEPTION_INIT 사용을 패키지로 모아둬서 예외 정의가 곳곳에 산재하지 않게 한다.

CREATE OR REPLACE PACKAGE dynsql  IS    invalid_table_name EXCEPTION;        PRAGMA EXCEPTION_INIT (invalid_table_name, -903);    invalid_column_name EXCEPTION;        PRAGMA EXCEPTION_INIT (invalid_column_name, -904);    프로그램에서는 다음과 같이 작성하면 된다.  WHEN dynsql.invalid_column_name THEN ...

     1.2.5 이름이 있는 시스템 예외 활용
PL/SQL의 패키지 STANDARD에서 이름이 있는 예외가 가장 중요하고 또 보편적으로 쓰인다.
이 말은 패키지명을 접두사로 붙이지 않아도 이름이 있는 예외를 참조할 수 있음을 의미한다.

WHEN NO_DATA_FOUND THEN  WHEN STANDARD.NO_DATA_FOUND THEN

위 두문장은 같은 의미이다.

LOB형 처리시 사용되는 DBMS_LOB 같은 다른 내장 패키지에도 미리 정의되딘 예외가 있다.
DBMS_LOB은 기본 패키지가 아니기 때문에 예외를 참조하려면 패키지명을 붙여야 한다.

WHEN DBMS_LOB.invalid_argval THEN ...

다음은 STANDARD 기반 미리 정의된 예외로 호출 시 SQLCODE에 반환되는 값인 오라클 오류 번호와 간단한 설명을 보여준다.
* SQLCODE는 가장 마지막에 실행된 SQL이나 DML문의 상태 코드를 반환하는 PL/SQL 내장 함수다. 마지막 문장에 오류가 없다면 SQLCODE는 0을 반환한다.
한 경우만 제외하고 SQLCODE 값은 오라클 오류 코드와 동일하다.(100, NO_DATA_FOUND의 ANSI 표준 오류 번호)

예외명  오라클 오류/SQLCODE	설명
CURSOR_ALREADY_OPEN  ORA-6511 SQLCODE= -6511	이미 OPEN된 커서를 OPEN하려는 경우. 커서를 OPEN하거나 재 OPEN하기 위해 커서를 CLOSE해야한다
DUP_VAL_ON_INDEX  ORA-00001 SQLCODE= -1	INSERT, UPDATE문으로 고유 인덱스가 잡혀진 컬럼에 값을 저장하는 경우
INVALID_CURSOR  ORA-01001 SQLCODE= -1001	존재하지 않는 커서를 참조하는 경우. 보통 커서가 OPEN되기 전에 FETCH하거나 CLOSE할때 발생
INVALID_NUMBER  ORA-01722 SQLCODE= -1722	PL/SQL에서 문자열을 숫자로 변환하는 SQL문이 실패했을 경우. 이 예외는 SQL문에서만 발생하는 VALUE_ERROR와는 다르다
LOGIN_DENIED  ORA-01017 SQLCODE= -1017	부적합한 계정으로 오라클 RDBMS에 여연결하려고 하는 경우. 보통 3세대 언어에 PL/SQL을 내장할 때 발생한다.
NO_DATA_FOUND  ORA-01403 SQLCODE= +100	다음 세가지 경우에 발생 . 1. 결과가 없는 SELECT INTO문(묵시적 커서)을 실행할 때, 2. 로컬 PL/SQL 테이블의 초기화되지 않은 행을 참조할 때, 3. 패키지 UTL_FILE로 파일의 내용이 끝난 다음에 내용을 읽어 들일 때
NOT_LOGGED_ON  ORA-01012 SQLCODE= -1012	오라클 RDBMS에 저접속하기 전에 데이터베이스를 호출하는 경우(보통 DML문으로)
PROGRAM_ERROR  ORA-06500 SQLCODE= -6501	PL/SQL에 내부 문제가 발생하는 경우. 일반적으로 메시지에 'Contact Oracle Support'란 말이 있다
STORAGE_ERROR  ORA-06500 SQLCODE= -6500	메모리 초과나 오류가 발생했을 경우
TIMEOUT_ON_RESOURCE  ORA-00051 SQLCODE= -51	자료 대기 중 RDBMS에서 타임아웃이 발생한 경우
TOO_MANY_ROWS  ORA-01422 SQLCODE= -1422	하나 이상의 결과 값을 반환하는 SELECT INTO문을 실행한 경우. SELECT INTO는 한 행만 반환할 수 있다. SQL문이 여러 행을 반환하는 경우에는 묵시적 커서로 선언하고 FETCH를 이용해서 해당 커서에서 한번에 한행씩 가져와야 한다.
TRANSACTION_BACKED_OUT  ORA-00061 SQLCODE= -61	명시적으로 ROLLBACK을 실행했거나 다른 동작(예를들어 원격 데이터베이스에서 SQL/DML이 실패했을 때)의 결과로 원격 트랜잭션 부분이 롤백되는 경우
VALUE-ERROR  ORA-06502 SQLCODE= -6502	형 변환, 절단, 부적합한 숫자형과 문자형 데이터를 사용하는 경우. 일반적으로 많이 발생하는 예외다. 이 오류가 PL/SQL블록의 SQL DML문에서 발생하면, INVALID_NUMBER 예외가 발생한다.
ZERO_DIVIDE  ORA-01476 SQLCODE= -1476	0으로 나누눗셈을 하는 경우

      1.2.6 예외 영역
예외 영역은 프로그램에서 예외가 다루는 부분으로 블록에 예외가 발생하게 되면 예외는 해당 블록을 다룬다.
다음 표는 여러 예외의 영역을 보여준다.

예외 유형	영역 설명
이름이 있는 시스템 예외	특정 블록에서 선언되거나 특정 블록에 한정된게 아니기 때문에, 전체 영역에서 사용 가능하다. 모든 블록에서 이름이 있는 시스템 예외를 발생시켜 처리할 수 있다.
이름이 있는 프로그래머 정의 예외	예외가 선언된 블록(해당 블록에 중첩된 블록도 포함)의 실행부와 예외부에서 발생되고 처리된다. 패키지 스펙에 예외가 정의된 경우, 예외 영역은 해당 패키지에 EXECUTE 권한이 있는 소유자의 모든 프로그램이다.
익명 시스템 예외	WHEN OTHERS절의 PL/SQL 예외부에서 처리된다. 익명 시스템 예외에 이름을 붙이면, 해당 이름의 영역은 이름이 있는 프로그래머 정의 예외 영역과 동일하게 된다.
익명 프로그래머 정의 예외	RAISE_APPLICATION_ERROR 호출에서만 정의되며 호출 프로그램으로 다시 예외가 넘겨진다.

  1.3 예외발생시키기
애플리케이션에서 예외를 발생시키는 방법에는 세가지가 있다.
- 오류가 감지되면, 오라클은 예외를 발생시킨다.
- RAISE문으로 예외를 발생시킨다.
- 내장 프로시저 RAISE_APPLICATION_ERROR로 예외를 발생시킨다.

     1.3.1 RAISE문
RAISE문으로 이름이 있는 예외를 원하는 만큼 발생시킬 수 있다.

RAISE 예외명; RAISE 패키지명.예외명; RAISE;

첫번째는 현재 블록에 정의된 예외나 패키지 STANDARD에 정의되딘 시스템 예외를 발생시키는데 사용된다.
두번째 형식은 예외가 패키지(STANDARD제외)내에 선언되어 있고, 해당 예외를 해당 패키지 외부에서 발생시키는 경우 사용된다.
세번째 형식은 예외명이 없는데 예외부의 WHEN 구문 안에서만 사용된다.

     1.3.2 RAISE_APPLICATION_ERROR 사용하기
RAISE_APPLICATION_ERROR가 실행되면 현재 PL/SQL 블록의 실행은 즉시 중단되고 OUT이나 IN OUT 매개변수에 가해진 변경작업은 취소된다.
이 때, 패키지된 변수 같은 전역 데이터 구문과 데이터베이스 객체에 가해진 변경작업(INSERT, UPDATE, DELETE문으로 실행된 변경작업)은 롤백되지 않는다. DML 작업을 되돌리기 위해서는 예외부에서 명시적으로 ROLLBACK을 실행해야 한다.

  1.4 예외 처리하기
예외가 발생하면 현재 PL/SQL 블록은 정상적인 실행을 멈추고 예외부로 제어를 넘긴다. 발생한 예외는 현재 PL/SQL 블록의 예외 처리기에서 처리되거나 개폐 블록으로 전달된다.
예외를 처리하거나 잡아내기 위해서는 해당 예외에 대한 처리기를 작성해야 하며 모든 실행 가능문 뒤에 예외 처리기가 위치한다. 

EXCEPTION    WHEN 예외명1    THEN        실행 가능 문장들;    WHEN 예외명N    THEN        실행 가능 문장들;    WHEN OTHERS    THEN        실행 가능 문장들;  END;

WHEN 구문에서 사용된 예외명과 발생한 예외명이 일치하며면 해당 예외가 처리된다. 예외 처리가되지 않거나 발생한 예외와 일치하는 이름이 있는 예외가 없는 경우에는 WHEN OTHERS 절 관련 실행 가능 문장이 실행된다.
하나의 예외 처리기는 하나의 오류만을 잡아낼 수 있으며, 처리기의 문장이 실행되고 나면 즉시 블록 밖으로 제어가 넘어간다.
WHEN OTHERS절은 선택사항이며 없을 경우에는 미처리되딘 예외는 즉시 개폐블록(존재하는 경우)으로 전달된다.

     1.4.1 처리기절에서 SQLCODE와 SQLERRM 사용하기
발생한 오류 정보는 SQLCODE함수로 알아낼 수 있다. SQLCODE는 현재 오류 번호(0은 오류 스택에 오류가 없음을 의미)를 반환하고 SQLERRM은 현재 오류나 전달받은 오류 번호의 오류 메시지를 반환한다.
WHEN OTHERS와 SQLCODE를 조합하면, EXCEPTION_INIT 프라그마를 사용하지 않고도 여러가지 특정 예외를 다룰 수 있다.

예제

PROCEDURE delete_company (company_id_in IN NUMBER)  IS  BEGIN    DELETE FROM company WHERE company_id = company_id_in;  EXCEPTION    WHEN OTHERS    THEN    /*    || 예외 처리기 이나에서 익명 블록을 사용하여,    || 오류 코드 정보를 위한 로컬 변수선언    */    DECLARE      error_code NUMBER := SQLCODE;      error_msg VARCHAR2 (512) := SQLERRM; -- Maximum length of SQLERRM string.    BEGIN      IF error_code = -2292      THEN         /* 자식 레코드 발견. 자식레코드 제거 후 부모 레코드 다시 제거 */         DELETE FROM employee WHERE company_id = company_id_in;         DELETE FROM company WHERE company_id = company_id_in;      ELSIF error_code = -2291      THEN         /* 부모 키 없음 */         DBMS_OUTPUT.PUTLINE ('Invalide company ID: '||TO_CHAR(company_id_in) );      ELSE        /* WHEN OTHERS 속의 WHEN OTHERS와 유사 */        DBMS_OUTPUT.PUTLINE ('Error deleting company, error: '||error_msg );      END IF;    END;  --익명 블록 끝  END delete_company;

* 예외 다음부터 실행을 중단하지 않고 계속 진행하고 싶은 경우 각각의 영역을 만들어 예외부에서 NULL문으로 다음 블록으로 진행하도록 만든다.

     1.4.2 표준화된 오류 처리기 프로그램 사용하기
전체 어플리케이션에서 동일한 방식으로 오류를 처리하고 기록하는 경우, 지원과 유지보수가 편리해 질 것이다.
이런 통일된 관리를 위하여 표준화된 패키지를 이용하여 예외부를 작성할 수 있다.
오라일리 사이트에 있는 errpkg.pkg에는 표준화된 오류 처리 패키지 프로토타입이 있으므로 참고 가능하다.