SQL - 예외처리

예외

 

-- 예외 P. 474
CREATE OR REPLACE PROCEDURE TEST111
(
  V_STU_NO IN STUDENT.STU_NO%TYPE
)
IS
  V_STU_NAME STUDENT.STU_NAME%TYPE;
BEGIN
    SELECT STU_NAME INTO V_STU_NAME
        FROM STUDENT
      WHERE STU_NO = V_STU_NO;
    DBMS_OUTPUT.PUT_LINE (V_STU_NAME);
    
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE ('해당 데이터가 존재하지 않는다');
END TEST111;
/

SELECT * FROM STUDENT;
EXECUTE TEST111(20153075);

DECLARE
  V_WRONG DATE;
BEGIN
    SELECT ENAME INTO V_WRONG
        FROM EMP
      WHERE EMPNO = 7369;
      
      DBMS_OUTPUT.PUT_LINE ('예외가 발생하면 다음 문장은 실행되지 않습니다');
      
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('오류가 발생하였습니다 ' || TO_CHAR(SYSDATE, '[YYYY"년"MM"월"DD"일" HH24"시"MM"분"SS"초"]'));
END;
/

 

• RAISE
  • 예외를 직접 만드는 작업 → 예외를 일부러 발생시킨다
  • CNT_ERROR EXCEPTION : 예외(에러) 변수

CREATE OR REPLACE PROCEDURE TEST22
(
  V_SUB_NO IN ENROL.SUB_NO%TYPE
)
IS
  V_CNT NUMBER;
  CNT_ERROR EXCEPTION;
BEGIN
    SELECT COUNT(STU_NO) INTO V_CNT
      FROM ENROL
    WHERE SUB_NO = V_SUB_NO;
    
    IF V_CNT = 0 THEN
      RAISE CNT_ERROR;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE (V_SUB_NO || '과목 수강자는 ' || V_CNT || '명');
    
  EXCEPTION
    WHEN CNT_ERROR THEN
      DBMS_OUTPUT.PUT_LINE (V_SUB_NO || ' 과목 수강자는 없다');
END TEST22;
/

EXECUTE TEST22(109);