[PL/SQL] 기본 문법 - %TYPE, %ROWTYPE Attribute, Exception 처리

PL/SQL 기본 문법

• 변수 선언: var_name data_type
• %TYPE Attribute
  : 테이블 컬럼과 같은 데이터 타입으로 설정할 경우 사용
  - 사용법: TABLE__NAME.COLUME_NAME%TYPE
  - ex) v_name emp.name%TYPE -- 데이터 타입을 가져오겠다.
• %ROWTYPE Attribute
  : 테이블 컬럼명과 데이터 타입을 그대로 가져올 경우 사용
  - ex) v_emp emp%ROWTYPE; -- v_emp.empno 로 데이터 접근
• IF Statement: 아래 소스 참고
• FOR LOOP Statement

CREATE OR REPLACE PROCEDURE Table_Test2
IS
  -- dept 테이블의 속성을 테이블 타입으로 설정
  TYPE dept_table_type  IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
  
  -- 테이블 타입으로 변수를 선언해서 사용
  dept_table    dept_table_type;
  i             BINARY_INTEGER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  /* dept_list 는 자동선언되는 BINARY_INTEGER형 변수로 1씩 증가한다.
     dept_list 대신 다른 문자열 사용 가능 */
  FOR dept_list IN (SELECT *
                    FROM   dept)
  LOOP
    dept_table(i).deptno  := dept_list.deptno;
    dept_table(i).dname   := dept_list.dname;
    dept_table(i).loc     := dept_list.loc;
    i := i +1;
  END LOOP;
  
  FOR cnt IN 0..i-1 LOOP
    DBMS_OUTPUT.PUT_LINE('부서번호 : ' || dept_table(cnt).deptno || '  부서명 : ' || dept_table(cnt).dname
                          || '  위치 : ' || dept_table(cnt).loc);
  END LOOP;
  
END;

 

 

PL/SQL 에러 처리 방법

1. Predefined Exception - 미리 정의된 에러

: NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, ZERO_DIVIDE, DUP_VAL_ON_INDEX ...

CREATE OR REPLACE PROCEDURE Show_emp
( p_sal   IN  emp.sal%TYPE)
IS
  v_ename   emp.ename%TYPE;
  v_sal     emp.sal%TYPE;
  v_job     emp.job%TYPE;
  
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  SELECT ename,   sal,    job
  INTO   v_ename, v_sal,  v_job
  FROM   emp
  WHERE  sal = p_sal;
 
 DBMS_OUTPUT.PUT_LINE('이름  ' || '급여  ' || '직책 ');
 DBMS_OUTPUT.PUT_LINE('---------------------------');
 DBMS_OUTPUT.PUT_LINE(v_ename || '  ' || v_sal || '  ' || v_job);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('해당 급여 받는 사람 없음, 다시 조사하세요');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('해당 급여 받는 사람 하나 이상, CURSOR 문 전환 필요');
END;

 

2. Non-Predefined Exception

: Oracle server error 중 미리 정의되지 않은 error를 명시적으로 사용자가 선언하여 사용

CREATE OR REPLACE PROCEDURE DEL_DEPT
(   p_deptno    dept.deptno%TYPE)
IS  
    -- exception 타입인 변수 선언
    FK_ERR      EXCEPTION;    			
    -- Oracle server에서 제공하는 error번호를 EXCEPTION_INIT을 이용하여 연결
    PRAGMA      EXCEPTION_INIT(FK_ERR, -2292);
BEGIN
    DBMS_OUTPUT.ENABLE;
    
    DELETE FROM DEPT
    WHERE  deptno = p_deptno;
    COMMIT;

-- 이 Section에서 error 처리
EXCEPTION
    WHEN FK_ERR THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('참조되는 emp가 있어 삭제불가하오니 양해바랍니다.');
END;

 

3. User Defined Exception

: business error - 이런 것들을 log table에 넣어서 업무 프로세스를 개선할 수 있다.

: RAISE문을 사용하여 에러 발생 시킴

----------------------------------------------------------
-- 오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 
-- 오류코드 -20000부터 -20999의 범위 내에서 사용자
-- 정의 예외를 만들수 있다.
-- STEP 1 : 예외의 이름을 선언 (선언절)
-- STEP 2 : RAISE문을 사용하여 직접적으로 예외를 발생시킨다(실행절)
-- STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)
----------------------------------------------------------
CREATE OR REPLACE PROCEDURE User_Exception
(v_deptno IN emp.deptno%TYPE)
IS
user_define_error EXCEPTION;  -- step 1: 예외 이름 선언
cnt NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  SELECT COUNT(empno)
  INTO cnt
  FROM emp
  WHERE deptno = v_deptno;
  
  IF cnt < 5 THEN
    RAISE user_define_error; -- step 2: RAISE문을 사용하여 직접적으로 예외 발생
  ELSE
    DBMS_OUTPUT.PUT_LINE('조직의 직무 향상 필요');
  END IF;
EXCEPTION
  -- 예외 발생할 경우 해당 예외참조
  WHEN user_define_error THEN
    RAISE_APPLICATION_ERROR(-20001, '부서에 인원 충원 필요');
END;