DB (14) PL/SQL

▶ PL/SQL(PROCEDURE LANGUAGE EXTENSION TO SQL)

- 오라클 자체에 내장되어 있는 절차적 언어이다.

- SQL문장 내에서 변수의 정의, 조건처리(IF), 반복처리(LOOP, FOR, WHILE), 예외처리 등을 지원하여 SQL의 단점을 보완

- 다수의 SQL문을 한 번에 실행 가능(BLOCK구조)

 

■ PL/SQL 구조

- [선언부 (DECLARE SECTION)] : DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분
- 실행부 (EXECUTABLE SECTION) : BEGIN으로 시작해서 END로 끝, SQL문(SELECT, UPDATE, DELETE, ..) 또는 제어문(조건문, 반복문) 등의 로직을 기술하는 부분
- [예외처리부 (EXCEPTION SECTION)] : EXCEPTION으로 시작, 예외발생시 해결하기 위한 구문을 미리 기술해둘 수 있는 부분

 

● 서버 아웃풋 옵션 켜주기

SET SERVEROUTPUT ON;

 

▶ DECLARE 선언부

- 변수 및 상수 선언하는 공안(선언과 동시에 초기화도 가능하다.)

- 일반타입 변수, 래퍼런스 변수, ROW타입 변수가 있다.

 

■ 일반타입 변수 선언 및 초기화

[표현법]
변수명 [CONSTANT] 자료형 [:= 값];

ex)

DECLARE
    EID NUMBER;
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER := 3.14;
BEGIN
    -- EID := 800;
    EID := &번호;
    -- 내가 값을 입력
    
    -- ENAME := '홍길동';
    ENAME := &이름;    
    -- 입력창에 글씨가 들어가면 ''를 써줘야한다.
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/ 
-- /가 있어야 블록종결로 간주되어 다음 PL문 실행이 가능하다.

 

■ 래퍼런스 타입 변수 선언 및 초기화

- 어떤 테이블의 어떤 컬럼의 데이터타입을 참조해서 그 타입으로 지정하는 것

[표현법]
변수명 테이블명.컬럼명%TYPE;

ex)

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    EID := '300';
    ENAME := '홍길동';
    SAL := 300000;
    
    -- 사번이 200번인 각 사원의 사번, 사원명, 연봉을 대입해보기
    SELECT 
        EMP_ID, EMP_NAME, SALARY
        INTO EID, ENAME, SAL
    FROM EMPLOYEE
    -- WHERE EMP_ID = 200;
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : ' || SAL);
    
END;
/

 

■ ROW타입 변수 선언

- 한 테이블의 한 행에 대한 모든 컬럼값을 한 번에 담을 수 있는 변수

[표현법]
변수명 테이블명%ROWTYPE

ex)

DECLARE
    E EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO E
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('사원명 : ' || E.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
    DBMS_OUTPUT.PUT_LINE('보너스 : ' || NVL(E.BONUS, 0));
END;
/

 

▶ BEGIN 실행부

■ 조건문

● IF 조건식 THEN 실행내용 END IF;

ex)

-- 사번 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스율 출력전 '보너스를 지급받지 않는 사원입니다' 출력
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;

    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS * 100 || '%');

END;
/

 

● IF 조건식 THEN 실행내용 ELSE 실행내용 END IF;

ex)

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;

    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS * 100 || '%');
    END IF;
END;
/

 

● IF 조건식 1 THEN 실행내용 ELSIF 조건식2 THEN 실행내용2 [ELSE 실행내용 N] END IF;

ex)

-- 급여가 500만원 이상이면 고급
-- 급여가 300만원 이상이면 중급
-- 그 외 초급
-- 출력문 : 해당 사원의 등급은 XX입니다.
DECLARE
    SAL EMPLOYEE.SALARY%TYPE;
    GRADE VARCHAR2(10);
BEGIN
    SELECT SALARY
     INTO SAL
     FROM EMPLOYEE
     WHERE EMP_ID = &사번;
     
     IF SAL >= 5000000 THEN GRADE := '고급';
     ELSIF SAL >= 3000000 THEN GRADE := '중급';
     ELSE GRADE := '초급';
     END IF;
     DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다.');
END;
/

 

● CASE 비교대상자 WHEN 동등비교값1 THEN 결과값1 WHEN 비교값2 THEN 결과값2 ELSE 결과값3 END;

ex)

DECLARE
    EMP EMPLOYEE%ROWTYPE;
    DNAME VARCHAR2(10);
BEGIN
    SELECT *
        INTO EMP
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DNAME := CASE EMP.DEPT_CODE
                WHEN 'D1' THEN '인사팀'
                WHEN 'D2' THEN '회계팀'
                WHEN 'D3' THEN '마케팅팀'
                WHEN 'D4' THEN '국내영업팀'
                WHEN 'D9' THEN '총무팀'
                ELSE '해외팀'
            END;
    DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME || '은 ' || DNAME || '입니다.');        
END;
/

 

■ 반복문

● BASIC LOOP문

[표현법]
LOOP
    반복적으로 실행할 구문;
    * 반복문을 빠져나갈 수 있는 구문
END LOOP;

 

반복문을 빠져나갈 수 있는 구문

1. IF 조건식 THEN EXIT; END IF;
2. EXIT WHEN 조건식;

ex)

-- 1~5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE 
    I NUMBER := 1;
BEGIN
    LOOP
    DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1;
        
--        IF I = 6 THEN EXIT; END IF;
        EXIT WHEN I = 6;
    END LOOP;
END;
/

 

● FOR LOOP문

[표현법]
FOR 변수 IN [REVERSE] 초기값 .. 최종값
LOOP
    반복적으로 수행할 구문;
END LOOP;

ex)

BEGIN
    FOR I IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

 

● WHILE LOOP문

[표현법]
WHILE 반복문이 수행될 조건
LOOP
    반복적으로 실행시킬 구문
END LOOP;

ex)

-- 짝수 구구단 만들기
DECLARE
    I NUMBER := 2;
    J NUMBER := 1;
BEGIN
    WHILE I <= 9
    LOOP
    J := 1;
        IF MOD(I, 2) = 0
            THEN
                WHILE J <= 9
                LOOP 
                    DBMS_OUTPUT.PUT_LINE(I || '*' || J || '=' || I*J);
                    J := J + 1;
                END LOOP;
        END IF;
        I := I + 1;
    END LOOP;
END;
/

 

▶ 예외처리부

[표현법]
EXCEPTION
    WHEN 예외명1 THEN 예외처리구문;
    WHEN 예외명2 THEN 예외처리구문;
    ...
    WHEN OTHERS THEN 예외처리구문;

시스템 예외(오라클에서 미리 정의해둔 예외)

• NO_DATA_FOUND : SELECT한 결과가 한 행도 없는 경우
• TOO_MANY_ROWS : SELECT한 결과가 여러 행인 경우
• ZERO_DEVIDE : 0으로 나눌 때 발생하는 에러
• DUP_VAL_ON INDEX : UNIQUE 제약조건에 위배되었을 때
• 등등

■ 에러 저장

PRAGMA EXCEPTION_INIT(DUP_EMPNO, -00001);
    -- 유니크제약조건에 위배된 경우 ORA-00001에러가 발생한다.
    -- ORA-00001에러를 저장하기 위해서 위쪽에 -00001을 써주었는데 ORA는 자동으로 인식하기 때문에 생략되었다.