• 참고 사이트
  • PL/SQL https://goddaehee.tistory.com/99
  • PL/SQL https://gdtbgl93.tistory.com/147

1. PL/SQL

• Procedural Language extension to SQL
• SQL을 확장한 절차적 언어(Procedural Language)

1-1. PL/SQL 특징

• 종류 : Procedure, Function, Trigger
• 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합
• 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보냄
• 오라클에서 지원하는 프로그래밍 언어의 특성을 수용, SQL에서는 사용할 수 없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완

1-2. PL/SQL 장점

• 프로시저 생성자와 SQL의 통합
• 잘 만들어진 PL/SQL 명령문의 성능 향상
• 모듈식 프로그램 개발 가능 : 논리적인 작업을 진행하는 여러 명령어들을 하나의 블록으로 만들 수 있음
• 이식성이 좋음
• 예외 처리 가능
• 아래의 SQL 단점 해결 가능
  • 변수가 없음
  • 한번에 하나의 명령문만 사용 가능하기 떄문에 트래픽이 상대적으로 증가
  • 제어문 사용 불가 (IF, LOOP)
  • 예외 처리가 없음
• 대용량 데이터를 연산해야 할 때, WAS등의 서버로 전송해서 처리하려면 네트워크에 부하가 많이 걸릴 수 있음 > 프로시져나 함수를 사용하여 데이터를 연산하고 가공한 후, 최종 결과만 서버에 전송하면 부담을 줄일 수 있음
• 로직 수정을 위해 서버를 셧다운 시키지 않아도 됨(서버에서는 단순히 DB에 프로시저를 호출하여 사용하면 됨)
• 쿼리문을 직접 노출하지 않으므로 SQL injection의 위험성이 줄어듦
• 블록 단위로 유연하게 사용할 수 있음

1-3. PL/SQL 단점

• 유지보수가 힘듦
• 대용량 처리가 많을 경우, DB에 부하를 줄 수 있음
• Git 같은 형상 관리를 사용할 수 없음

2. PL/SQL 문법

• 블록 단위의 실행을 제공
  • BEGIN과 END;를 사용
  • 마지막 라인에 /를 입력하여 해당 블록이 실행되도록 함
• 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용
  • 변수 선언은 DECLARE 절에서만 가능
  • BEGIN 섹션에서 새 값이 할당될 수 있음
• IF문을 사용하여 문장들을 분기, LOOP문을 사용하여 일련의 문장을 반복 가능
• 커서를 사용하여 여러 행 검색 및 처리
• PL/SQL에서 사용 가능한 SQL : Query, DML, TCL
  • DDL(CREATE, DROP, ALTER, TRUNCATE …), DCL(GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능
• PL/SQL의 SELECT문은 해당 SELECT의 결과를 PL/SQL Engine으로 보낸다.
  • 이를 캐치하기 위한 변수를 DECLARE 해야 함
  • INTO 절을 선언하여 넣을 변수를 꼭 표현해주어야 함
  • SELECT 문장은 반드시 한 개의 행이 검색되어야 함
  • INTO절이 누락되거나, 검색되는 행이 없으면 에러 발생
• 한 문장이 종료할 때마다 세미콜론(;)을 사용, 한 문장이 끝났다는 것을 명시
• 단일행 주석 -- / 여러 행 주석 /* */

2-1. PL/SQL 구조

2-1-1. DECLARE(선언부)

• PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분
• 변수/상수/커서 등을 선언

2-1-2. BEGIN(실행부)

• 절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술
• 필수적으로 존재해야 함

2-1-3. EXCEPTION(예외 처리부)

• PL/SQL문이 실행되는 중에 발생되는 에러(예외 사항)를 해결하기 위한 문장을 기술

2-1-4. END(실행문 종료)

• END 뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시
• 필수적으로 존재해야 함

2-2. PL/SQL Block의 종류

• 익명 블록 : 이름이 없는 PL/SQL Block
• 이름 있는 블록 : DB의 객체로 저장되는 블록
  • 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램
  • 함수 : 리턴 값을 반드시 반환해야 하는 프로그램
  • 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
  • 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

3. 실습 준비

3-1. PL/SQL 결과 출력

• PUT_LINE 프로시저를 이용
• 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경

SET serveroutput ON
BEGIN 
    dbms_output.put_line('ynjch');
END;

3-2. 테스트용 테이블 준비

• USER_INFO 테이블 생성

CREATE TABLE USER_INFO 
(
    USER_NUM NUMBER(10) NOT NULL 
    , USER_NM VARCHAR2(20) 
    , USER_BIRTH VARCHAR2(8)
    , CONSTRAINT user_info_pk PRIMARY KEY(USER_NUM)
);
SELECT * FROM USER_INFO;
DROP TABLE USER_INFO;

• USER_INFO 테이블 시퀀스 생성

CREATE SEQUENCE USER_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;

• 시퀀스 조회 및 초기화

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'USER_NUM_SEQ';
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY -1000;
SELECT USER_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY 1;

• USER_SCORE 테이블 생성

CREATE TABLE USER_SCORE 
(
    USER_NUM NUMBER(10) NOT NULL 
    , USER_GRADE VARCHAR(5)
    , CONSTRAINT user_score_pk PRIMARY KEY(USER_NUM)
);
SELECT * FROM USER_SCORE;
DROP TABLE USER_SCORE;

• USER_INFO 데이터 INSERT

INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '차은우', '19971026');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '강태오', '19971001');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '서강준', '19911022');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '송강', '20010107');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '로운', '19710606');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '김수현', '19970714');
INSERT INTO USER_SCORE values(1, 'A');
INSERT INTO USER_SCORE values(2, 'C');
INSERT INTO USER_SCORE values(3, 'A');
INSERT INTO USER_SCORE values(4, 'D');
INSERT INTO USER_SCORE values(5, 'B');
INSERT INTO USER_SCORE values(6, 'C');

• STORE_INFO 테이블 생성

CREATE TABLE STORE_INFO 
(
    STORE_NUM NUMBER(10) NOT NULL 
    , STORE_NM VARCHAR2(30) 
    , STORE_TEL VARCHAR2(20) 
    , STORE_ADDR VARCHAR2(30)
    , OWNER_NUM NUMBER(10)
    , CONSTRAINT store_info_pk PRIMARY KEY(STORE_NUM)
    , CONSTRAINT store_info_fk FOREIGN KEY(OWNER_NUM) REFERENCES USER_INFO (USER_NUM)
);
SELECT * FROM STORE_INFO;
DROP TABLE STORE_INFO;

• STORE_INFO 테이블 시퀀스 생성

CREATE SEQUENCE STORE_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;

• 시퀀스 조회 및 초기화

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'STORE_NUM_SEQ';
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY -1000;
SELECT STORE_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY 1;

• 시퀀스 증가 함수 만들기

CREATE FUNCTION FUNC_STORE_NUM_SEQ RETURN NUMBER IS
BEGIN
    RETURN STORE_NUM_SEQ.NEXTVAL;
END;
/
SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION' AND NAME = 'FUNC_STORE_NUM_SEQ';
DROP FUNCTION FUNC_STORE_NUM_SEQ;

• STORE_INFO 데이터 INSERT

INSERT INTO STORE_INFO (STORE_NUM, STORE_NM, STORE_TEL, STORE_ADDR, OWNER_NUM)
SELECT              FUNC_STORE_NUM_SEQ, '코스트코 대전점', '033-345-5751', '대전시 중구', 1 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '광명 IKEA', '02-111-4453', '경기도 광명시', 2 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '교보문고 광화문점', '02-2341-4277', '서울시 종로구', 3 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '신세계 영등포점', '02-3245-8775', '서울시 영등포구', 5 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '63빌딩 아쿠아리움', '02-2341-4277', '서울시 영등포구', 5 FROM DUAL;

COMMIT;

4. 변수

• 선언부(DECLARE) 변수명, 데이터 타입을 기술
  • identifier : 변수명(식별자)
  • CONSTANT : 상수로 지정 (초기치를 반드시 할당해야 함)
  • datatype : 자료형을 기술
  • NOT NULL : 값을 반드시 포함
  • expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression];

4-1. 변수 선언

• 단일 변수 선언

DECLARE NAME VARCHAR2(10);
DECLARE NAME VARCHAR2(10) := 'ynjch';
DECLARE NAME VARCHAR2(10) DEFAULT 'ynjch';

• 여러 개 선언 시

DECLARE 
	NAME    VARCHAR2(20);
	AGE     NUMBER(2); 
	GENDER  VARCHAR(5)   DEFAULT '여';

4-2. Type으로 변수 선언

4-2-1. %ROWTYPE

• 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태
  • 변수명 테이블명%ROWTYPE

DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    dbms_output.put_line(rowData.USER_NUM||'. '||rowData.USER_NM);
END;

4-2-2. %TYPE

• 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태
  • 변수명 테이블명.컬럼명%TYPE

DECLARE
    userNum NUMBER(5) := 1;
    userNm USER_INFO.USER_NM%TYPE;
    userBirth USER_INFO.USER_BIRTH%TYPE;
BEGIN
    SELECT
        USER_NM, USER_BIRTH INTO userNm, userBirth
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    dbms_output.put_line(userNm||'('||userBirth||')');
END;

• SELECT 문을 이용하여 값 대입
  • INTO 절에 조회 결과 값을 저장할 변수를 기술
  • SELECT 문은 INTO 절에 의해 하나의 행만을 저장 가능
  • SELECT 이후 작성한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야 함 > 개수와 데이터 타입, 길이를 일치시켜야 함

5. 반복문

• FOR LOOP문, LOOP문으로 나뉨

5-1. FOR LOOP문

• index는 자동 선언되는 binary_integer형 변수이며 1씩 증가
• REVERSE 옵션이 사용 될 경우 index는 upper_bound에서 lower_bound로 1씩 감소
• IN 다음에는 coursor나 SELECT 문이 올 수 있음

FOR index in [REVERSE] 시작값 .. END값 LOOP
    STATEMENT 1
    STATEMENT 2
    ...
END LOOP;

5-1-1. 예제

• 짝수, 홀수 판별
  • IF문을 사용하여 분기처리

BEGIN
    FOR i IN 1..5 LOOP
        IF MOD(i,2) = 0 THEN 
            dbms_output.put_line(i||'는 짝수');
        ELSE
            dbms_output.put_line(i||'는 홀수');
        END IF;
    END LOOP;
END;

• USER_NUM 1~3 까지의 데이터 출력하기

DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    FOR i IN 1..3 LOOP
        userNum := i;
        SELECT * INTO rowData
        FROM USER_INFO
        WHERE USER_NUM = userNum;
        dbms_output.put_line(rowData.USER_NM||'('||rowData.USER_NUM||')');
    END LOOP;
END;

• SELECT 문을 이용하여 별도의 변수 선언 없이 사용 가능

BEGIN
    FOR userList IN (
        SELECT * FROM USER_INFO
    ) LOOP
        dbms_output.put_line(userList.USER_NM||'('||userList.USER_NUM||')');
    END LOOP;
END;

• 나와 나이 비교하기

DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
        
    FOR userList IN (
        SELECT * FROM USER_INFO WHERE USER_NUM <> userNum
    ) LOOP
        IF ( SUBSTR(rowData.USER_BIRTH,1,4) = SUBSTR(userList.USER_BIRTH,1,4) ) THEN
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'의 친구');
        ELSIF ( SUBSTR(rowData.USER_BIRTH,1,4) > SUBSTR(userList.USER_BIRTH,1,4) ) THEN
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 많다');
        ELSE 
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 적다');
        END IF;
    END LOOP;
END;

5-1-2. 예제

• 테이블 JOIN하여 유저들의 점수 조회하기 1
  • USER_INFO, USER_SCORE 테이블의 컬럼을 담은 트랜잭션 GTT 생성(TX_USER_INFO_SCORE)
  • WITH문, FOR LOOP문 사용

CREATE GLOBAL TEMPORARY TABLE TX_USER_INFO_SCORE (
    USER_NUM NUMBER(10) NOT NULL 
    , USER_NM VARCHAR2(20) 
    , USER_BIRTH VARCHAR2(8)
    , USER_GRADE VARCHAR(5)
)
ON COMMIT DELETE ROWS;

DECLARE
    userCnt NUMBER;
    rowData TX_USER_INFO_SCORE%ROWTYPE;
BEGIN 
    -- 1. userCnt 구하기 
    SELECT COUNT(1) INTO userCnt FROM USER_INFO;
    dbms_output.put_line('userCnt : '||userCnt);
    
    -- 2. for loop
    FOR i IN 1..userCnt LOOP
        WITH TEMP_USER_INFO_SCORE AS -- WITH 문
        (
            SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
            FROM USER_INFO I
            LEFT JOIN USER_SCORE S
            ON I.USER_NUM = S.USER_NUM
        )
        SELECT * INTO rowData FROM TEMP_USER_INFO_SCORE WHERE USER_NUM = i;
        dbms_output.put_line(rowData.USER_NM ||'('|| rowData.USER_NUM ||')''s score : ' || rowData.USER_GRADE);
    END LOOP;
END;

• 테이블 JOIN하여 유저들의 점수 조회하기 2
  • FOR [변수명] IN [쿼리] LOOP 사용
  • WITH문, FOR LOOP문 사용

DECLARE
BEGIN     
    FOR userList IN (
        WITH TEMP_USER_INFO_SCORE AS -- WITH 문
        (
            SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
            FROM USER_INFO I
            LEFT JOIN USER_SCORE S
            ON I.USER_NUM = S.USER_NUM
        )
        SELECT * FROM TEMP_USER_INFO_SCORE ORDER BY USER_NUM
    ) LOOP
        dbms_output.put_line(userList.USER_NM ||'('|| userList.USER_NUM ||')''s score : ' || userList.USER_GRADE);
    END LOOP;
END;

5-2. LOOP문

• EXIT : 무조건 LOOP 문을 빠져나감
• EXIT WHEN : WHEN 절에서 LOOP를 빠져나가는 조건을 제어

LOOP 
    STATEMENT 1
        다른 LOOP를 포함하여 중첩으로 사용 가능
    EXIT [WHEN CONDITION]
END LOOP;

5-2-1. 예제

• 1에서 5까지 숫자 출력하기

DECLARE
    startNum NUMBER := 1;
    endNum NUMBER := 5;
    loopCnt NUMBER := 0;
BEGIN
    LOOP
        dbms_output.put_line('현재 숫자 : '||startNum);
        loopCnt := loopCnt + 1;
        startNum := startNum + 1;
        EXIT WHEN loopCnt >= endNum;
    END LOOP;
    dbms_output.put_line('반복 횟수 : '||loopCnt);
END;

5-2-2. WHILE LOOP문

• 5-2-1. 의 예제를 WHILE LOOP문으로 표현

DECLARE
    startNum NUMBER := 1;
    endNum NUMBER := 5;
    loopCnt NUMBER := 0;
BEGIN
    WHILE loopCnt < endNum LOOP
        dbms_output.put_line('현재 숫자 : '||startNum);
        loopCnt := loopCnt + 1;
        startNum := startNum + 1;
    END LOOP;
    dbms_output.put_line('반복 횟수 : '||loopCnt);
END;

6. 제어문

• 일반적인 프로그래밍에서 사용되는 IF문, CASE문 등의 제어문(조건절) 사용 가능

6-1. IF문

• ELSE IF를 ELSIF로 쓰는 것에 주의

IF 조건1 THEN
    처리문1
ELSIF 조건2 THEN
    처리문2
ELSE
    처리문
END IF;

6-1-1. 예제

DECLARE
    userNum NUMBER := 1;
    userBirthYear VARCHAR(4);
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    userBirthYear := SUBSTR(rowData.USER_BIRTH, 1, 4);
    
    IF (2022 - userBirthYear + 1) > 49 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 50세 이상입니다.');
    ELSIF (2022 - userBirthYear + 1) > 29 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 30세 이상입니다.');
    ELSIF (2022 - userBirthYear + 1) > 19 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 이상입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 미만입니다.');
    END IF;
END;

6-2. CASE문

• 조건에 따른 값을 대입하거나, PL/SQL 명령문 실행 시 사용

6-2-1. 예제

• 성적 확인

DECLARE
    userNum NUMBER := 1;
    userGrade USER_SCORE.USER_GRADE%TYPE;
    resultStr VARCHAR(30);
BEGIN 
    SELECT USER_GRADE INTO userGrade
    FROM USER_SCORE 
    WHERE USER_NUM = userNum;
    
    resultStr := 
    CASE userGrade
        WHEN 'A' THEN 'Perfect'
        WHEN 'B' THEN 'Very Good'
        WHEN 'C' THEN 'Good'
        ELSE 'Hmm...'
    END;
    DBMS_OUTPUT.PUT_LINE(userGrade||' is '||resultStr);
END;

7. 예외 처리

• 시스템 예외 : Oracle에서 예외 상황에 대해 미리 정의해놓은 예외
• 사용자 정의 예외 : 예상되는 특정 예외 상황에 대해 개발자가 직접 정의한 예외
• 처리할 예외들을 차례로 선언한 뒤, 마지막에 OTHERS를 선언하여 나머지 예외를 처리하도록 함

EXCEPTION WHEN [예외명1] THEN [예외처리 구문1]
WHEN [예외명2] THEN [예외처리 구문2];

7-1. 예외 처리

• 프로시저 실행 후 예외가 발생하더라도 로직은 정상 작동되도록 예외 처리 구문 작성

CREATE OR REPLACE PROCEDURE PROC_TEST
IS
    v_num NUMBER := 0;
BEGIN
    v_num := 10/0;
    EXCEPTION WHEN OTHERS THEN 
        dbms_output.put_line('오류가 발생했습니다!');
        dbms_output.put_line('에러코드 : ' || SQLCODE);
        dbms_output.put_line('에러메시지 : ' || SQLERRM);
        dbms_output.put_line('매개변수 있는 에러메시지 : ' || SQLERRM(SQLCODE));
END;
/
EXEC PROC_TEST();
/* 결과
오류가 발생했습니다!
에러코드 : -1476
에러메시지 : ORA-01476: 제수가 0 입니다
매개변수 있는 에러메시지 : ORA-01476: 제수가 0 입니다 */

7-2. 시스템 예외

• Oracle에서 지원하는 대표적 시스템 예외 목록

예외명	예외 코드	설명
PROGRAM_ERROR	ORA-06501	PL/SQL 코드상에서 내부 오류를 만났을 경우
STORAGE_ERROR	ORA-06500	프로그램 수행 시 메모리가 부족할 경우
TIMEOUT_ON_RESOURCE	ORA-00051	데이터베이스 자원을 기다리는 동안 타임아웃 발생 시
NO_DATA_FOUND	ORA-01403	SELECT INTO 시 데이터가 한 건도 없을 경우
TOO_MANY_ROWS	ORA-01422	SELECT INTO 절 사용할 때 결과가 한 로우 이상일 때
VALUE_ERROR	ORA-06502	수치 또는 값 오류
ZERO_DIVIDE	ORA-01476	0으로 나눌 때
INVALID_NUMBER	ORA-01722	문자를 숫자로 변환할 때 실패할 경우
DUP_VAL_ON_INDEX	ORA-00001	유일 인덱스가 있는 컬럼에 중복값으로 INSERT, UPDATE 수행
CASE_NOT_FOUND	ORA-06592	CASE문 사용 시 구문 오류
ACCESS_INTO_NULL	ORA-06530	LOB과 같은 객체 초기화 되지 않은 상태에서 사용
CURSOR_ALREADY_OPEN	ORA-06511	커서가 이미 OPEN 된 상태인데 OPEN 하려고 시도
INVALID_CURSOR	ORA-01001	존재하지 않는 커서를 참조
NOT_LOGGED_ON	ORA-01012	로그온되지 않았는데 DB를 참조할 때
LOGIN_DENIED	ORA-01017	잘못된 사용자 이름이나 비밀번호로 로그인을 시도

7-3. 사용자 정의 예외

• 입력 받은 값이 유효하지 않은 경우 예외 처리하도록 프로시저 구성
• RAISE : 직접 정의한 예외, 시스템 예외를 발생시키기 위해 작성
  • ex_not_exist_num EXCEPTION; : 선언부에 작성하여 예외를 정의
  • RAISE NO_DATA_FOUND; : 시스템 예외를 발생시키도록 함

CREATE OR REPLACE PROCEDURE PROC_CHK_USER_NUM (p_user_num USER_INFO.USER_NUM%TYPE)
IS
    v_cnt NUMBER := 0;
    ex_not_exist_num EXCEPTION;
BEGIN
    SELECT COUNT(1)
    INTO v_cnt
    FROM USER_INFO 
    WHERE USER_NUM = p_user_num;
    
    -- 입력 데이터에 따른 결과값 조건 처리
    IF p_user_num = 1 THEN
        RAISE NO_DATA_FOUND;
    ELSIF v_cnt > 0 THEN 
        dbms_output.put_line('등록된 사용자입니다.');
    ELSE 
        RAISE ex_not_exist_num;
    END IF;
    
    -- 예외 처리
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('NO_DATA_FOUND!!');
    WHEN ex_not_exist_num THEN
        dbms_output.put_line('등록된 사용자가 없습니다.');
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;
/

-- 결과 
EXEC PROC_CHK_USER_NUM(1); /* NO_DATA_FOUND!! */
EXEC PROC_CHK_USER_NUM(2); /* 등록된 사용자입니다. */
EXEC PROC_CHK_USER_NUM(111); /* 등록된 사용자가 없습니다. */
EXEC PROC_CHK_USER_NUM('YNJCH'); 
/* ORA-06502: PL/SQL: 수치 또는 값 오류: 문자를 숫자로 변환하는데 오류입니다 */

7-4. 예외 내용 확인

• Oracle에서 기본 제공하는 빌트인(built-in) 함수 이용
  • SQLCODE : 실행부에서 발생한 예외에 해당하는 코드를 반환
  • SQLERRM : 예외에 대한 정보를 담은 메시지 반환
• 상세 정보를 표시하기 위해 dbms_utility 이용
  • dbms_utility.format_call_stack
  • dbms_utility.format_error_stack
  • dbms_utility.format_error_backtrace

7-4-1. 효율적인 예외 처리 방법

• 시스템 예외인 경우 OTHERS 사용
• 예외 처리 루틴을 공통 모듈화하여, 예외 발생 시 로그를 기록하도록 테이블을 만들어 관리
• 사용자 정의 예외는 별도 테이블을 만들어 관리