절차형 SQL의 오류 처리

정보처리기사 목차절차형 SQL레슨 109 / 233

예외(exception)는 블록을 실행하는 도중에 나는 오류이고, EXCEPTION 절은 그 오류를 받아 어떻게 할지 적어 두는 자리입니다. 데이터베이스가 이름을 붙여 둔 예외와 쓰는 사람이 만든 예외를 같은 꼴로 받습니다.

블록 안에서 오류가 나면 그 줄에서 실행이 멈추고 아래 문장들은 지나가지 않습니다. 받아 주는 자리가 없으면 오류 번호가 그대로 부른 쪽까지 올라갑니다. 화면에는 ORA-01403 같은 번호만 뜨고, 그 뒤에 하려던 처리는 아무것도 일어나지 않습니다. EXCEPTION 절을 두면 오류마다 할 일을 정해 두고 블록을 정상으로 끝낼 수 있습니다. 아래 그림은 찾는 행이 없어 오류가 난 흐름이 EXCEPTION 칸으로 뛰는 장면입니다.

지금 1~2번 줄 · DECLARE

지금 3번 줄 · BEGIN

오류 4~6번 줄 · SELECT 점수 INTO v_점수

지금 줄 없음

지금 9번 줄 · WHEN TOO_MANY_ROWS THEN

지금 ?번 줄

지금 12번 줄 · WHEN NO_DATA_FOUND THEN

지금 13~14번 줄 · DBMS_OUTPUT.PUT_LINE(

지금 15번 줄 · END;

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;오류 없이 끝남

EXCEPTION 칸이 없었다면 → ORA-01403으로 멈춤

지금 15번 줄 · END;

줄	바뀐 이름	값
2	v_점수	값 없음
4	난 오류	NO_DATA_FOUND

실행 결과

수강 내역 없음

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;

지금 ?번 줄

줄	바뀐 이름	값
2	v_점수	값 없음
4	난 오류	NO_DATA_FOUND

실행 결과

아직 출력이 없습니다 — 8단계에서 쌓입니다

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;

지금 줄 없음

줄	바뀐 이름	값
2	v_점수	값 없음
4	난 오류	NO_DATA_FOUND

실행 결과

아직 출력이 없습니다 — 8단계에서 쌓입니다

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN맞지 않음    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;

지금 9번 줄 · WHEN TOO_MANY_ROWS THEN

줄	바뀐 이름	값
2	v_점수	값 없음
4	난 오류	NO_DATA_FOUND

실행 결과

아직 출력이 없습니다 — 8단계에서 쌓입니다

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN맞음    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;

지금 12번 줄 · WHEN NO_DATA_FOUND THEN

줄	바뀐 이름	값
2	v_점수	값 없음
4	난 오류	NO_DATA_FOUND

실행 결과

아직 출력이 없습니다 — 8단계에서 쌓입니다

DECLARE  v_점수 NUMBER;BEGIN  SELECT 점수 INTO v_점수  FROM 수강  WHERE 학번 = 106;  DBMS_OUTPUT.PUT_LINE(v_점수);EXCEPTION  WHEN TOO_MANY_ROWS THEN    DBMS_OUTPUT.PUT_LINE(        '행이 여럿');  WHEN NO_DATA_FOUND THEN    DBMS_OUTPUT.PUT_LINE(        '수강 내역 없음');END;

지금 1~2번 줄 · DECLARE

줄	바뀐 이름	값
2	v_점수	값 없음

실행 결과

아직 출력이 없습니다 — 8단계에서 쌓입니다

9단계 중 1단계. 1~2번 줄 — 점수를 담을 변수를 만듭니다. 아직 값이 없습니다.

1EXCEPTION 절의 꼴

EXCEPTION 아래에 WHEN 예외이름 THEN을 두고 그 아래에 할 일을 적습니다. WHEN은 여럿을 둘 수 있습니다.

BEGIN
  실행할 문장;
EXCEPTION
  WHEN 예외이름 THEN
    처리할 문장;
  WHEN OTHERS THEN
    나머지 오류 처리;
END;

오류가 나면 그 자리 아래 문장은 실행되지 않습니다. 그림에서 점수를 찍는 줄을 지나지 않은 것이 그것입니다.
WHEN은 위에서부터 견주고 맞는 하나만 실행합니다. 나머지 WHEN의 문장은 지나갑니다. 맞는 WHEN이 하나도 없으면 오류는 받아지지 않고 그대로 밖으로 나갑니다.
WHEN OTHERS는 앞에서 받지 못한 모든 오류를 받습니다. 받는 범위가 가장 넓어서 반드시 맨 뒤에 둡니다. 뒤에 다른 WHEN을 두면 PLS-00370 오류가 나서 블록이 아예 만들어지지 않습니다.
오류를 받아 처리하고 나면 블록은 정상으로 끝납니다.
오류의 번호와 메시지는 SQLCODE와 SQLERRM으로 꺼내 씁니다.

2미리 정의된 예외

자주 나는 오류에는 데이터베이스가 이름을 붙여 두었습니다. 이런 것을 미리 정의된 예외(predefined exception)라 하고, 선언 없이 WHEN 뒤에 바로 씁니다. 시험에 나오는 넷입니다.

이름	언제 나나	오류 번호
NO_DATA_FOUND	SELECT INTO가 찾은 행이 없다	ORA-01403
TOO_MANY_ROWS	SELECT INTO가 찾은 행이 둘 이상이다	ORA-01422
DUP_VAL_ON_INDEX	기본키처럼 값이 겹치면 안 되는 칸에 같은 값을 넣었다	ORA-00001
ZERO_DIVIDE	0으로 나누었다	ORA-01476

앞의 둘은 SELECT INTO가 받는 행이 딱 하나여야 해서 생깁니다. 행이 없어도 둘 이상이어도 오류입니다.
이름이 붙지 않은 오류도 많습니다. 그런 것은 WHEN OTHERS로 받습니다.

3사용자 정의 예외

문법에는 어긋나지 않지만 업무 규칙으로는 틀린 값이 있습니다. 점수가 60점 미만이거나 수량이 음수인 경우입니다. 데이터베이스는 이것을 오류로 보지 않으므로, 쓰는 사람이 예외를 만들어 직접 일으킵니다. 이것이 사용자 정의 예외(user-defined exception)입니다. 선언부·RAISE·WHEN 세 곳에 나뉘어 적힙니다.

DECLARE
  이름 EXCEPTION;
BEGIN
  IF 조건 THEN
    RAISE 이름;
  END IF;
EXCEPTION
  WHEN 이름 THEN
    처리할 문장;
END;

선언부에 이름 EXCEPTION;으로 만듭니다. 자료형이 아니라 예외라는 뜻의 키워드입니다.
RAISE 이름;이 예외를 일으킵니다. 이 줄을 지나는 순간 흐름이 EXCEPTION 칸으로 뜁니다.
받는 쪽은 미리 정의된 예외와 같은 WHEN 이름 THEN입니다.

예외를 블록 안에서 받지 않고 부른 쪽에 오류로 알리고 싶을 때는 RAISE_APPLICATION_ERROR⁠를 씁니다.

RAISE_APPLICATION_ERROR(
    -20001, '알릴 메시지');

번호는 −20000부터 −20999까지만 쓸 수 있습니다. 이 범위가 쓰는 사람 몫으로 비워져 있습니다. 벗어난 번호를 적으면 ORA-21000 오류가 납니다.
부른 쪽은 ORA-20001: 알릴 메시지를 받습니다. 번호만 보이는 것이 아니라 적어 둔 메시지가 함께 갑니다.

	RAISE	RAISE_APPLICATION_ERROR
미리 할 일	선언부에 예외를 만든다	없다
붙는 것	예외 이름	번호와 메시지
누가 받나	같은 블록의 WHEN	블록을 부른 쪽

예제

오류가 난 뒤 흐름이 어디로 가는지를 봅니다. 학번 106인 정하윤은 수강 표에 행이 하나도 없습니다.

받는 자리가 없으면

EXCEPTION 절이 없는 블록입니다. 오류 번호가 그대로 나오고 그 뒤 줄은 실행되지 않습니다.

SET SERVEROUTPUT ON
DECLARE
  v_점수 NUMBER;
BEGIN
  SELECT 점수 INTO v_점수
  FROM 수강
  WHERE 학번 = 106;
  DBMS_OUTPUT.PUT_LINE(
      '끝까지');
END;
/

ORA-01403: no data found
ORA-06512: at line 4

'끝까지'는 찍히지 않았습니다. 오류 메시지의 4번째 줄은 DECLARE를 첫 줄로 센 번호이고, 그 자리는 SELECT INTO 문장입니다. 도구 설정인 SET SERVEROUTPUT ON은 블록의 줄 번호에 들지 않습니다.

미리 정의된 예외를 받는다

그림의 블록입니다. 같은 오류를 EXCEPTION 절이 받아 블록이 정상으로 끝납니다.

SET SERVEROUTPUT ON
DECLARE
  v_점수 NUMBER;
BEGIN
  SELECT 점수 INTO v_점수
  FROM 수강
  WHERE 학번 = 106;
  DBMS_OUTPUT.PUT_LINE(v_점수);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE(
        '행이 여럿');
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(
        '수강 내역 없음');
END;
/

수강 내역 없음

WHEN이 둘이지만 찍힌 줄은 하나입니다. 앞의 TOO_MANY_ROWS는 맞지 않아 지나갔습니다. 같은 꼴의 블록을 학생 표로 바꿔 돌리면 이번에는 행이 여럿이 찍힙니다 — 표를 학생으로, 고르는 열을 이름으로, 받는 변수를 VARCHAR2로, 조건을 WHERE 학과코드 = 'CS'로 넷을 함께 바꿉니다. 컴퓨터학과 학생이 셋이기 때문입니다.

사용자 정의 예외

107번 학생의 네트워크(NW) 점수는 50점입니다. 60점 미만을 업무 규칙상의 오류로 보고 예외를 만들어 일으킵니다.

SET SERVEROUTPUT ON
DECLARE
  기준미달 EXCEPTION;
  v_점수 NUMBER;
BEGIN
  SELECT 점수 INTO v_점수
  FROM 수강
  WHERE 학번 = 107
    AND 과목코드 = 'NW';
  IF v_점수 < 60 THEN
    RAISE 기준미달;
  END IF;
  DBMS_OUTPUT.PUT_LINE('통과');
EXCEPTION
  WHEN 기준미달 THEN
    DBMS_OUTPUT.PUT_LINE(
        '기준 미달');
END;
/

기준 미달

SELECT는 제대로 돌았고 오류도 나지 않았습니다. RAISE를 지나는 순간 흐름이 EXCEPTION 칸으로 뛰어 '통과'를 찍는 줄을 지나갔습니다.

부른 쪽에 오류로 알린다

프로시저 안에서 범위를 넘는 점수를 받으면 오류를 만들어 부른 쪽에 올립니다.

CREATE PROCEDURE 점수확인(
  p_점수 IN NUMBER
) IS
BEGIN
  IF p_점수 > 100 THEN
    RAISE_APPLICATION_ERROR(
        -20001, '점수 초과');
  END IF;
END;
/
EXECUTE 점수확인(120)

ORA-20001: 점수 초과

적어 둔 메시지가 오류 번호와 함께 나왔습니다. 그 아래로 오류가 난 자리를 알리는 ORA-06512 줄이 따라옵니다. 부른 쪽은 이 오류를 자기 블록의 EXCEPTION 절에서 다시 받을 수 있습니다.

시험 포인트

필기: 예외 처리 구조를 묻습니다. EXCEPTION 아래에 WHEN 예외이름 THEN 처리;를 두고, WHEN은 위에서부터 견주어 맞는 하나만 실행합니다. WHEN OTHERS는 나머지를 모두 받으므로 맨 뒤에 둡니다. 뒤에 다른 WHEN이 오면 만들 때 오류가 납니다. 오류가 나면 그 아래 문장은 실행되지 않고 흐름이 EXCEPTION 칸으로 갑니다. 미리 정의된 예외 넷은 NO_DATA_FOUND·TOO_MANY_ROWS·DUP_VAL_ON_INDEX·ZERO_DIVIDE입니다. 앞의 둘은 SELECT INTO가 행 하나만 받기 때문에 생깁니다. 사용자 정의 예외는 선언부에서 만들고 RAISE로 일으켜 WHEN으로 받습니다. RAISE_APPLICATION_ERROR의 번호 범위는 −20000부터 −20999까지입니다.
실기: 이름을 쓰게 하면 미리 정의된 예외(Predefined Exception)와 사용자 정의 예외(User-Defined Exception)이고, 예외를 일으키는 문장은 RAISE, 번호와 메시지를 붙여 부른 쪽에 올리는 것은 RAISE_APPLICATION_ERROR입니다. 미리 정의된 예외의 이름은 대문자에 밑줄까지 그대로 씁니다.

확인 문제

1. 과목 표에는 과목코드가 기본키입니다. 이미 자료구조(DS)가 들어 있는데 과목코드 'DS'로 행을 하나 더 넣었습니다. 이때 나는 예외의 이름을 고르세요.

① DUP_VAL_ON_INDEX
② NO_DATA_FOUND
③ TOO_MANY_ROWS
④ ZERO_DIVIDE

정답

①입니다. 기본키는 같은 값이 둘일 수 없습니다. 겹치는 값을 넣으면 DUP_VAL_ON_INDEX가 납니다.

② SELECT INTO가 찾은 행이 없을 때입니다. 여기서는 읽는 것이 아니라 넣고 있습니다.
③ SELECT INTO가 찾은 행이 둘 이상일 때입니다. 겹치는 값을 넣는 것과는 다릅니다.
④ 0으로 나눌 때입니다. 나누는 계산이 없습니다.

2. 다음 (가)~(라)에 맞는 말을 쓰세요. (가)·(나)는 한글 이름과 영문으로 함께, (다)·(라)는 블록에 적는 키워드로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(가) 자주 나는 오류에 데이터베이스가 이름을 붙여 둔 것입니다. 선언하지 않고 WHEN 뒤에 바로 이름을 씁니다.
(나) 문법에는 어긋나지 않지만 업무 규칙으로 틀린 경우를 오류로 다루려고, 쓰는 사람이 선언부에서 만드는 것입니다.
(다) (나)를 일으키는 문장입니다. 이 줄을 지나면 아래 문장을 건너뛰고 흐름이 예외 처리부로 갑니다.
(라) 번호와 메시지를 붙여 블록을 부른 쪽에 오류로 올리는 문장입니다. 번호는 −20000부터 −20999까지입니다.

정답

받는 답: (가) 미리 정의된 예외(Predefined Exception) · (나) 사용자 정의 예외(User-Defined Exception) · (다) RAISE · (라) RAISE_APPLICATION_ERROR

(가)의 실마리는 '선언 없이 바로 쓴다', (나)는 '쓰는 사람이 선언부에서 만든다', (다)는 '일으키면 흐름이 뛴다', (라)는 '번호와 메시지를 부른 쪽에 올린다'입니다.

같은 이름의 다른 꼴: (가)·(나) 예외나 Exception 하나만 쓴 꼴 — 둘이 갈리지 않습니다. 문제가 한글 이름과 영문을 함께 쓰라고 했습니다.
틀리는 꼴 WHEN OTHERS — (가)에: 이름이 붙지 않은 오류까지 모두 받는 자리입니다. 이름이 붙은 것들의 갈래 이름이 아닙니다.
틀리는 꼴 RAISE_APPLICATION_ERROR — (다)에: 번호와 메시지를 붙여 부른 쪽에 올립니다. 선언해 둔 예외 이름을 일으키는 문장은 RAISE입니다.

3. 다음 블록을 돌리면 무엇이 찍힐까요. 학생 표의 3학년은 최민준·정하윤과 김민수 셋입니다. 답을 끝까지 쓴 뒤 정답을 펼치세요.

SET SERVEROUTPUT ON
DECLARE
  v_이름 VARCHAR2(10);
BEGIN
  SELECT 이름 INTO v_이름
  FROM 학생
  WHERE 학년 = 3;
  DBMS_OUTPUT.PUT_LINE(v_이름);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(
        '학생 없음');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(
        '다른 오류');
END;
/

정답

받는 답: 다른 오류

SELECT INTO가 받을 수 있는 행은 하나뿐인데 3학년이 셋이라 TOO_MANY_ROWS가 납니다. 그 이름의 WHEN이 없어 WHEN OTHERS가 받습니다.

틀리는 꼴 이름 셋이 찍힘: SELECT INTO는 여러 행을 담지 못합니다. 담지 못하면 오류이고, 찍는 줄까지 가지 않습니다.
틀리는 꼴 학생 없음: 3학년이 있는데도 NO_DATA_FOUND가 난다고 봤습니다. 행이 없을 때 나는 예외입니다.
틀리는 꼴 아무것도 찍히지 않음: 맞는 WHEN이 없으면 오류가 밖으로 나간다고 봤습니다. WHEN OTHERS가 남은 오류를 모두 받습니다.

읽음으로 표시

끝까지 내려오면 저절로 표시됩니다. 이 기기에만 저장합니다.

정보처리기사 목차로