sumin

예외처리

PL/SQL 코드를 작성할 때 발생할 수 있는 오류에는 크게 두 가지가 있다. 하나는 문법 오류로써 객체(테이블, 뷰, 함수, 프로시저 등)나 키워드 이름을 잘못 참조하거나 함수나 프로시저의 매개변수를 잘못 명시했을 때 발생되는 오류로 이들은 컴파일할 때 걸러진다. 두 번째는 컴파일 때는 아무런 문제가 없으나 런타임 때 로직을 처리하면서 발생하는 오류인데 이를 예외exception 라고 한다. 컴파일 시 발생하는 오류도 예외에 속하긴 하지만 보통 예외라고 하면 런타임 때의 오류를 말한다.

예외에는 다시 오라클에서 발생시키는 시스템 예외와 사용자가 의도적으로 발생시킬 수 있는 사용자 정의 예외로 구분할 수 있다. 시스템 예외로는 나눗셈 연산을 할 때 0으로 나눈다거나 SELECT INTO 절을 사용해 데이터를 가져올 때 조건에 만족하는 데이터가 없으면 발생하는 예외 등이 있다. 시스템 예외는 오라클 내부에 미리 정의된 예외라고 할 수 있다. 이에 반해 사용자 정의 예외는 사용자가 직접 예외를 정의해서 사용하는 것을 말한다. 먼저 예외처리 구문에 대해 살펴 보자.

기본구문

예외처리 구문을 보면 CASE문과 비슷한 구조임을 알 수 있다. 처리할 예외명을 언급하고 그 예외에 해당되면 예외처리 구문을 나열하는 식이다. WHEN 다음에 위치하는 예외명은 아무 이름이나 사용할 수는 없고 시스템 예외 중 일부와 사용자가 직접 정의한 예외명을 사용할 수 있다. 구체적인 예외명을 알 수 없을 때는 OTHERS를 사용하면 되는데 이렇게 하면 오라클 시스템에서 PL/SQL 코드 상에서 발생한 런타임 예외를 자동으로 잡아 준다. 자동으로 예외를 잡아 주니 이때 처리할 수 있는 예외는 물론 시스템 예외에 한정된다. 그러면 의도적으로 예외를 발생시켜 보자.

◈ 예제

10을 0으로 나누려니 오류가 발생했다. 예외처리 구문을 사용하지 않으니 시스템에서 오류를 뱉어낸 것이다. 이번에는 예외처리 구문을 넣어 보자.

예외처리 구문 덕분에 시스템 에러 대신 “오류가 발생했습니다”란 메시지가 출력되었다. PL/SQL 코드의 실행부에서 예외가 발생하는 즉시 이후 코드는 처리되지 않고 제어권은 예외처리 구문으로 넘어간다. 먼저 위의 익명 블록을 예외처리 부분이 있는 것과 없는 것으로 나누어 2개의 프로시저로 만들어 보자.

◈ 예제

<예외처리 없는 프로시저>

<예외처리 있는 프로시저>

예외처리가 없는 프로시저를 실행했더니 시스템에서 예외를 발생시켜 해당 프로시저 실행이 중단됐음은 물론 익명 블록에서 “Success!”를 출력하는 부분도 실행되지 않았다. 반면 예외처리가 구현된 프로시저를 실행했을 때는 예외 처리부로 제어권이 넘어가 “오류가 발생했습니다”란 메시지를 출력한 다음 익명 블록의 “Success!”란 메시지도 출력되었다.

예외처리를 함으로써 개발자가 프로그램 제어권을 획득해 여러 단계에 걸쳐 수행되는 프로그램의 정상적 동작을 보장할 수 있었는데 반해 예외처리를 하지 않으면 오류가 난 시점에 프로그램의 흐름이 끊어져 버린다. 따라서 아주 간단한 코드를 작성할 때 조차도 반드시 예외처리 구문을 넣도록 하자.

시스템 예외

예외처리 시 OTHERS 외에도 시스템 예외명을 사용할 수 있는데 이를 미리 정의된 예외predefined exception라 한다. 즉 고유의 예외명칭을 사용할 수 있다는 것인데 이들 중 대표적인 예외를 정리하면 다음과 같다.

위의 표를 보면 젯수가 0일 때의 예외가 ‘ZERO_DIVIDE’이므로, 예외처리 구문에서 OTHERS 대신 사용할 수 있다.

◈ 예제

미리 정의된 예외는 그 수가 제한되어 있지만 오라클 내부에서 처리하는 예외의 수는 매우 많다. 따라서 보통 예외처리를 할 때 미리 정의된 예외를 먼저쓰고 맨 마지막에 OTHERS를 명시하는 형태로 사용한다. 여러 개의 예외를 명시할 때 OTHERS는 반드시 맨 끝에 명시해야 한다. 이제 2개 이상의 예외명을 명시해 보자.

결과를 보면 알겠지만 예외처리는 EXCEPTION 절에 명시한 순서대로 처리된다. 맨 처음 명시한 예외가 발생하면 해당되는 로직을 처리하고 이후 예외는 무시하고 프로시저는 종료된다.

※ 프로시저

함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값을 반환하지는 않는 서브 프로그램이다. 일반적으로 프로젝트 현장에서는 시스템 설계가 끝난 후 업무를 분할하고 이 분할한 업무 단위로 로직을 구현해야 하는데, 개별적인 단위 업무는 주로 프로시저로 구현해 처리한다. 즉 테이블에서 데이터를 추출해 입맛에 맞게 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 사용한다.

1. 프로시저 생성

함수나 프로시저 모두 DB에 저장된 객체이므로 프로시저를 스토어드(Stored, 저장된) 프로시저라고 부르기도 한다.(함수도 스토어드 함수라고도 한다). 프로시저의 생성 구문은 다음과 같다.

기본구문

• CREATE OR REPLACE PROCEDURE: 함수와 마찬가지로 CREATE OR REPLACE 구문을 사용해 프로시저를 생성한다.

• 매개변수: IN은 입력, OUT은 출력, IN OUT은 입력과 출력을 동시에 한다는 의미다. 아무것도 명시하지 않으면 디폴트로 IN 매개변수임을 뜻한다. OUT 매개변수는 프로시저 내에서 로직 처리 후 해당 매개변수에 값을 할당해 프로시저 호출 부분에서 이 값을 참조할 수 있다. 그리고 IN 매개변수에는 디폴트 값 설정이 가능하다.

그럼 jobs 테이블에 신규 JOB을 넣는 프로시저를 만들어 보자. jobs 테이블에는 job 번호, job명, 최소, 최대 금액, 생성일자, 갱신일자 컬럼이 있는데, 생성일과 갱신일은 시스템 현재일자로 등록할 것이므로 매개변수는 총 4개를 받도록 하자.

◈ 예제

성공적으로 컴파일되었다. p_job_id부터 p_max_sal까지 총 4개의 매개변수를 전달받아 이 값들을 jobs 테이블에 입력하고 있다. INSERT문을 사용하므로 COMMIT문을 사용해서 최종적으로 DB에 변경사항을 반영하고 있다.

2. 프로시저 실행

함수는 반환 값을 받으므로 실행할 때 ‘호출’이라고 명명하지만 프로시저는 ‘호출’ 혹은 ‘실행’한다고 표현하는데 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼 SELECT 절에는 사용할 수 없고 다음과 같이 실행해야 한다.

기본구문

① 프로시저 실행

그럼 my_new_job_proc 프로시저를 실행해 보자.

◈ 예제

오류가 나지는 않았지만 제대로 입력됐는지 jobs 테이블을 조회해 보자.

성공적으로 입력됐다. 재차 확인하기 위해 다시 한 번 프로시저를 실행해 보자.

이번에는 오류가 발생했다. 원인은 jobs 테이블의 job_id는 PRIMARY KEY로 잡혀 있는데도 불구하고 동일한 job_id(SM_JOB1)를 또 입력하려고 시도했기 때문이다. 이렇게 기본적인 데이터 무결성 문제는 오라클에서 자동으로 걸러준다. 오라클에게 오류 처리를 맡길 것이 아니라, 동일한 job_id가 들어오면 신규 INSERT 대신 다른 정보를 갱신하도록 프로시저를 수정해 보자.

다시 실행해 보자. 이번에는 최소 급여값, 최대 급여값을 수정해서 입력해 보자.

동일한 job_id 값을 입력했더니 INSERT를 하지 않고 매개변수로 전달된 다른 정보를 UPDATE했음을 확인할 수 있다.

프로시저의 매개변수가 많으면 실행할 때 매개변수 값의 개수나 순서를 혼동할 소지가 매우 많다. 이런 경우에는 다음과 같은 형태로 매개변수와 입력 값을 매핑해 실행하면 매우 편리하다.

② 프로시저 실행

‘=>’ 기호를 사용해 해당 매개변수명과 값을 연결하는 형태로 실행할 수 있다. my_new_job_proc 프로시저를 이 형태로 실행해 보자.

◈ 예제

1. FOR문

FOR문도 다른 프로그래밍 언어에서 사용하는 것과 비슷한 형태이다. 오라클에서 제공하는 FOR문의 기본 유형은 다음과 같다.

인덱스는 초깃값에서 시작해 최종값까지 루프를 돌며 1씩 증가되는데 인덱스는 참조는 가능하지만 변경할 수는 없고 참조도 오직 루프 안에서만 가능하다. 그리고 REVERSE를 명시하면 순서가 거꾸로 된다. 즉 최종값부터 시작해 최솟값에 이르기까지 감소하면서 루프를 돈다. 구구단 3단을 출력하는 익명 블록을 FOR문으로 변경해 보자.

◈ 예제

2. CONTINUE문

CONTINUE문은 FOR나 WHILE 같은 반복문은 아니지만  반복문 내에서 특정 조건에 부합할 때 처리 로직을 건너뛰고 상단의 루프 조건으로 건너가 루프를 계속 수행할 때 사용한다. EXIT는 루프를 완전히 빠져 나오는데 반해 CONTINUE는 제어 범위가 조건절로 넘어간다. 참고로 CONTINUE문은 오라클 11g버전부터 지원하고 있다. 구구단 3단에서 5를 제외하고 처리하는 문장을 작성해 보자.

◈ 예제

CONTINUE문을 사용해 인덱스 i 값이 5가 됐을 때는 하단의 로직을 수행하지 않고 다시 FOR 조건절로 제어가 옮겨져 처리됐음을 알 수 있다.

3. NULL문

PL/SQL에서는 NULL문을 사용할 수 있다. NULL문은 아무것도 처리하지 않는 문장이다. 아무 것도 처리하지 않는데 굳이 사용할 필요가 있을까? PL/SQL 코드를 작성하다 보면 가끔씩 필요할 때가 있다. NULL문은 보통 IF문이나 CASE문을 작성할 때 주로 사용하는데 조건에 따라 처리 로직을 작성하고 앞에서 작성한 모든 조건에 부합되지 않을 때 즉 ELSE절을 수행할 때 아무것도 처리하지 않고 싶은 경우 NULL문을 사용한다.

기본구문

절차적 언어인 PL/SQL은 다른 프로그래밍 언어에서 제공하는 다양한 처리문들을 포함하는데 이들을 통칭해 PL/SQL 제어문이라고 한다. 제어문에는 특정 조건에 맞을 때 처리하는 조건문, 반복 처리 시 사용하는 반복문, GOTO나 NULL과 같은 순차적 제어문이 있다.

1. IF문

특정 조건에 따라 처리를 하는 것을 조건문이라 하는데 그 대표적인 것이 바로 IF 문이며 그 구조는 다음과 같다.

기본구문

<조건이 1개일 경우>

<조건이 2개일 경우>

<조건이 n개일 경우>

IF 다음에 오는 조건 값이 TRUE이면 해당 조건 처리 문장이 실행된다. IF문의 용법은 프로그래밍 언어에 따라 조금씩 차이가 있는데 오라클에서는 조건 다음에는 THEN을 문장 맨 마지막에는 END IF를 써야한다.

조건이 1개 이상이면 ELSE 가 아닌 ELSE IF를 사용한다.

그럼 두 변수를 선언해 초기화한 뒤 둘 중 큰 수를 출력하는 로직을 IF문을 사용해 구현해 보자.

◈ 예제

vn_num2가 vn_num1보다 크므로 ELSE 부분으로 제어가 넘어가 실행된 것을 확인할 수 있다. 이번에는 조건이 여러 개인 IF문을 살펴 보자.

DBMS_RANDOM 패키지를 사용해 10부터 120까지 숫자를 생성한 후 10의 자리(-1)에서 ROUND 처리를 해서 실행할 때마다 무작위로 10, 20, 30, … 120까지 수를 vn_department_id 변수에 할당한다. 이로 인해 이 PL/SQL 블록의 실행 결과는 실행할 때마다 달라질 것이다. 그리고 나서 이 변수 값에 해당하는 부서번호를 가진 사원을 무작위로 1명 선택해 급여를 가져와 vn_salary 변수에 넣어 IF 문을 사용해 범위에 맞게 출력하는 로직이다. 급여가 3000 이하이면 ‘낮음’, 3,001에서 6,000까지는 ‘중간’, 6,001에서 10,000까지는 ‘높음’, 그 이상은 ‘최상위’ 라는 텍스트를 출력하고 있다. 이처럼 여러 조건을 검토해 로직을 처리할 때는 IF~ELSIF~ELSE~END IF 구문을 사용한다.

2. CASE문

① 기본구문

② 기본구문

두 가지 유형으로 CASE문을 사용할 수 있다. 첫 번째는 CASE 다음에 바로 표현식을 두고 이 표현식 결과 값에 따라 WHEN… THEN에서 처리하는 유형이다.

두 번째 유형은 표현식 자체를 각각의 WHEN 다음에 두고 THEN 이하에서 처리하는데 보통 후자를 많이 사용한다. 그 이유는 WHEN 이하에서 다양한 표현식을 사용해 조건을 검색할 수 있기 때문이다.

급여에 따라 ‘높음’, ‘낮음’을 출력하는 IF문을 CASE문으로 변환해 보자.

◈ 예제

3. LOOP문

LOOP문은 루프를 돌며 반복해서 로직을 처리하는 반복문이다. 이러한 반복문에는 LOOP문 외에도 WHILE문, FOR문이 있는데, 먼저 가장 기본적인 형태의 반복문인 LOOP문에 대해 살펴 보자.

기본구문

LOOP문은 ‘LOOP … END LOOP;’ 형태로 사용한다. 반복문은 특정 조건을 만족할 때만 루프를 돌며 로직을 처리해야 하는데 LOOP문은 특정 조건이란 것이 없다. 그 대신 “이런 경우에 루프를 빠져 나가라” 라는 EXIT를 사용해 루프를 종료한다. 따라서 EXIT를 누락하면 무한루프에 빠지게 되니 사용할 때 주의해야 한다. 일반적으로 LOOP문에서는 IF문을 사용해 루프를 빠져나가는 조건을 걸기도 하지만, “EXIT WHEN 조건”을 사용해 WHEN절에서 명시한 조건에 맞을 때 빠져 나가라고 명시할 수 있다. 구구단 중 3단을 출력하는 익명 블록을 LOOP문을 사용해 구현해 보자.

◈ 예제

4. WHILE문

일반적인 프로그래밍 언어에서 대표적인 반복문을 꼽으라면 WHILE문과 FOR문을 들 수 있다. 오라클에서도 역시 이 두 문장을 제공하는데 먼저 WHILE문에 대해 살펴 보자.

기본구문

WHILE문은 LOOP문과 비슷하지만 WHILE 다음에 조건을 붙여 해당 조건에 만족할 때만 루프를 돌면서 로직을 처리한다. WHILE문을 사용해 구구단 3단을 출력해 보자.

◈ 예제

WHILE 다음에 조건 처리를 한 부분만 제외하면 다른 부분은 LOOP문과 같지만 LOOP문과 달리 vn_cnt 값이 9보다 작거나 같은 경우라는 조건을 주었다는 점을 눈여겨 보자. LOOP문에서는 루프를 빠져 나가는 조건을 주었지만 WHILE문에서는 루프를 수행하는 조건을 준 것이다.

WHILE문에서도 루프를 돌다가 특정 조건에 부합하면 EXIT를 써서 루프를 빠져나올 수 있다.

1. 변수

변수는 다른 프로그래밍 언어에서 사용하는 변수와 개념이 같으며 선언부에서 변수 선언을 하고 실행부에서 사용한다. 변수 선언 방식은 다음과 같다.

기본구문

변수 선언과 동시에 초깃값을 할당할 수 있는데, 초깃값을 할당하지 않으면 데이터 타입에 상관없이 그 변수의 초깃값은 NULL이 된다. 물론 선언부에서는 선언만 하고 실행부에서 초깃값을 할당하기도 한다.

변수로 선언할 수 있는 데이터 타입은 크게 두 가지로 구분할 수 있는데, 하나는 SQL 데이터 타입이고 다른 하나는 PL/SQL 데이터 타입이다.  PL/SQL 데이터 타입인 BOOLEAN, PLS_INTEGER, BINARY_INTEGER 등이 있다.

2. 상수

상수는 변수와는 달리 한 번 값을 할당하면 변하지 않는다. 상수 선언도 변수 선언과 비슷하다.

상수를 선언할 때는 반드시 CONSTANT란 키워드를 붙여 변수와 구분하며 선언할 때 반드시 초기화해야 한다. 실행부에서 상수를 다른 값으로 변경할 수 없다.  상수는 특정 값을 할당해 놓고 실행부에서 사용하고자 할 때 사용된다.

예를 들어, 2.01이란 특정 변환 값이 있다. 실행부에서 이 값을 곱해서 연산을 하는 부분이 많은데 만약 이 값이 3.01로 변경이 됐다면 코드 전체를 훑어 2.01을 3.01로 수정한 뒤 다시 컴파일해야 한다. 이 값을 100번 사용했다면 100번 수정해야 한다는 뜻이다. 하지만 이 값을 상수로 만들면, 값이 바뀌더라도 선언부에서 한 번만 바꾸면 된다.

3. 연산자

PL/SQL 블록에서는 모든 SQL 연산자를 사용할 수 있으며, 연산자 우선순위는 다음과 같다.

우선순위가 가장 높은 ‘**’ 연산자는 제곱 연산자를 뜻하는데 PL/SQL에서만 사용할 수 있다. 예를 들어, 2**3는 2의 3제곱을 뜻한다.

◈ 예제

2**2*3**2에서 ‘**’ 연산자가 우선순위가 있으므로, 풀어 쓰면 (2^2^*3^2^) = (4*9) = 36이 된 것이다.

4. 주석

PL/SQL뿐만 아니라 SQL 구문에서도 주석을 사용할 수 있다. 한 줄 전체를 주석 처리할 때는 맨 앞에 ‘–’를 명시하고, 여러 줄의 코드에 주석을 붙일 때는 주석 처리할 부분의 처음과 끝에 ‘/*’와 ‘*/’를 붙인다.

5. DML문

PL/SQL 블록 상에서 사용하는 변수, 상수, 연산자는 사실 부차적인 용도로 사용될 뿐이다. 실제로 PL/SQL 블록을 작성하는 원래의 목적은 테이블 상에 있는 데이터를 이리저리 가공해서 특정 로직에 따라 무언가를 처리하는 것이며, 따라서 주로 사용되는 것은 SQL문이다. SQL문 중 DDL은 PL/SQL 상에서 직접 쓸 수 없고 DML문만 사용한다.

예를 들어, 사원 테이블에서 특정 사원의 이름과 부서명을 가져와 출력하는 코드를 작성한다고 해 보자.

◈ 예제

vs_emp_name와 vs_dep_name라는 두 개의 변수를 선언한 뒤, 사원 테이블에서 사번이 100인 사원의 이름과 부서명을 가져와 변수에 할당한 뒤 출력하였다. 테이블에 있는 데이터를 선택해 변수에 할당할 때는 SELECT 문에서 INTO절을 사용한다는 점이다. 이때 선택하는 컬럼에 따라 변수의 순서, 개수, 데이터 타입을 반드시 맞춰 줘야 한다.

또한 사원 테이블의 사원명과 부서명 타입을 동일하게 맞춰서 변수를 선언했는데 변수의 개수가 많아지면 해당 컬럼에 대응되는 변수 타입을 일일이 찾기가 번거로울 수 있다. 이럴 때는 다음과 같이 %TYPE 키워드를 쓰면 해당 변수에 컬럼 타입을 자동으로 가져온다.

서브 쿼리는 한 SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문을 의미한다. 최종 결과를 출력하는 쿼리를 메인 쿼리라고 한다면 중간 단계 혹은 보조 역할을 하는 SELECT문을 서브 쿼리라 한다. 조인 절에서 소개했던 SQL문 중 괄호 안에 들어있는 SELECT문이 바로 서브 쿼리에 속한다. 하나의 SQL문을 기준으로 메인 쿼리를 제외한 나머지 모든 SELECT문을 서브 쿼리로 보면 되며 서브 쿼리는 여러 개를 사용할 수 있다.

서브 쿼리는 다양한 형태로 사용된다.  SELECT, FROM, WHERE 절 모두에서 사용할 수 있을 뿐만 아니라, INSERT, UPDATE, MERGE, DELETE 문에서도 사용할 수 있다. 서브 쿼리는 그 특성과 형태에 따라 다음과 같이 구분할 수 있다.

• 메인 쿼리와의 연관성에 따라

  • 연관성 없는(Noncorrelated) 서브 쿼리

  • 연관성 있는 서브 쿼리

• 형태에 따라

  • 일반 서브 쿼리(SELECT 절)

  • 인라인 뷰(FROM 절)

  • 중첩 쿼리(WHERE 절)

1. 연관성 없는 서브 쿼리

메인 쿼리와의 연관성이 없는 서브 쿼리를 말한다. 즉 메인 테이블과 조인 조건이 걸리지 않는 서브 쿼리를 가리킨다.

◈ 예제

위 쿼리는 전 사원의 평균 급여 이상을 받는 사원 수를 조회하는 쿼리다. 메인 쿼리와 서브 쿼리 모두 사원 테이블을 조회하고 있지만, 메인 쿼리와 서브 쿼리 사이의 연관성은 없다.  먼저 서브 쿼리에서 평균 급여를 구한 뒤 메인 쿼리에서는 이 평균값보다 큰 사원을 조회한 것이다.

2. 연관성 있는 서브 쿼리

메인 쿼리와의 연관성이 있는 서브 쿼리 메인 테이블과 조인 조건이 걸린 서브 쿼리를 말한다.

◈ 예제

쿼리는 연관성 있는 서브 쿼리로 서브 쿼리 안에서 메인 쿼리에서 사용된 부서 테이블의 부서번호와 job_history 테이블의 부서번호가 같은 건을 조회하고 있다. 또한 EXISTS 연산자를 사용해서 서브 쿼리 내에 조인 조건이 포함되어 있다. 따라서 결과는 job_history 테이블에 있는 부서만 조회되었다.

3. 인라인뷰

FROM 절에 사용하는 서브 쿼리를 인라인 뷰InlineView 라고 한다. 원래 FROM 절에는 테이블이나 뷰가 오는데, 서브 쿼리를 FROM 절에 사용해 하나의 테이블이나 뷰처럼 사용할 수 있다. 뷰를 해체하면 하나의 독립적인 SELECT문이므로 FROM 절에 사용하는 서브 쿼리도 하나의 뷰로 볼 수 있어서 인라인 뷰라는 이름이 붙은 것이다.

◈ 예제

위 쿼리는 기획부(90) 산하에 있는 부서에 속한 사원의 평균급여보다 많은 급여를 받는 사원 목록을 추출한 것인데, 기획부 산하 평균급여를 구하는 부분을 서브 쿼리로 작성했고 이를 FROM 절에 위치시켰다.