PL/SQL의 기초 및 선언(변수 활용, 조건문, 반복문) - ORACLE DB

 

 

PL/SQL(Procedural Language extension to SQL) 이란?

- 오라클 DB에서 내장된 절차적 프로그래밍 언어다.

- SQL의 한계(단점)을 극복하기 위해 SQL과 로직적인 문법(조건문, 반복문, 변수, 예외처리) 등을 제공한다.

 

---

 

종류

 

- 익명 블록 : 이름 없는 블록으로 한 번 사용하고 버려지는 문장

- 프로시저(Procedure) : 저장이 가능한 블록으로 단독으로 사용되거나 프로시저들간의 호출을 지원

- 함수 : 프로시저중 하나로 반환 기능이 있는 문장

 

 

---

 

문법

 

DECLARE; -- 선언부(옵션)
	-- 변수 선언
BEGIN; -- 실행부(필수)
	-- 프로그래밍 로직
EXCEPTION; -- 예외처리부(옵션)
	-- 예외처리 호직
END; -- 끝을 지정(필수)
/ -- 끝난 다음 종결을 의미

※ 프로시저 실행 전 필수!
SET SERVEROUTPUT ON;
- 프로시저의 출력문을 볼 수 있는 옵션이다.
- 프로시저를 사용하여 출력하는 내용을 화면에 보여주도록 설정하는 환경변수로 
  기본 값은 OFF여서 ON으로 변경이 필요하다.

DECLARE

'변수명'  [CONSTANT] <DATA_TYPE> [NOT NULL [:= <초기값> | DEFAULT <초기값>]]

-> <초기값> : 리터럴이 다른 변수, 연산자는 함수를 포함한 식

 

변수의 종류

- 기본 자료형 : 문자형(VARCHAR), 숫자(NUMBER), 날짜(DATE), 논리형(BOOLEAN) := TURE, FALSE, NULL

- 복합 자료형

  RECORD : 사용자가 정의한 일종의 객체(자료구조) 표현할 때 사용

  COLLECTION : 배열처럼 활용

- 테이블 참조형

   %TYPE : 테이블 한 개의 컬럼값을 사용할 때

   %ROWTYPE : 테이블 속성값을 모두 사용할 때

- 상수 : CONSTANT

- NOT NULL : NOT NULL로 지정

---

예제 1)

%TYPE 변수

DECLARE
    V_EMP_ID    EMPLOYEE.EMP_ID % TYPE;
    V_EMP_NAME  EMPLOYEE.EMP_NAME % TYPE;
BEGIN
    V_EMP_NAME := '&TEMP_NAME';

    SELECT EMP_ID, EMP_NAME
    INTO V_EMP_ID, V_EMP_NAME
    FROM EMPLOYEE WHERE EMP_NAME = V_EMP_NAME; 

    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || V_EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || V_EMP_NAME);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('직원을 찾을 수 없습니다.');
END;
/

PUT_LINE이라는 프로시저를 이용하여 출력(DBMS_OTUPUT패키지에 속해 있음)

 

:= 변수의 초기화

& : 입력창을 통해 임의로 데이터를 받는 키워드

 

---

 

예제 2)

%ROWTYPE 변수

DECLARE
    EMP EMPLOYEE % ROWTYPE; -- TABLE 전체 참조 변수 생성
    V_EMP_NAME VARCHAR(20);
BEGIN
    V_EMP_NAME := '&TEMP_NAME';

    SELECT * 
    INTO EMP
    FROM EMPLOYEE 
    WHERE EMP_NAME = V_EMP_NAME; 

    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP.EMP_NAME);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('직원을 찾을 수 없습니다.');
END;
/

---

 

조건문

1) IF문

IF <조건> THEN
    -- 조건 해당 시 처리문
ELSIF <조건2> THEN
    -- 조건2 해당 시 처리문
ELSIF <조건3> THEN
    -- 조건3 해당 시 처리문
ELSE
    -- ELSE 해당 시 처리문
END IF;

 

 

예제 )

이름을 입력 받아서 급여 등급별로 출력하는 예제
급여 등급
500만원 이상 - A
400만원 이상 - B
300만원 이상 - C
200만원 이상 - D
100만원 이상 - E
100만원 미만 - F 

DECLARE
    EMP EMPLOYEE % ROWTYPE; -- TABLE 전체 참조 변수 생성
    V_EMP_NAME VARCHAR(20);
    SALARY_GRADE VARCHAR(10);
BEGIN
    V_EMP_NAME := '&TEMP_NAME';

    SELECT * INTO EMP FROM EMPLOYEE 
    WHERE EMP_NAME = V_EMP_NAME; 
    
    IF (EMP.SALARY >= 5000000) THEN
    SALARY_GRADE := 'A';
    ELSIF (EMP.SALARY >= 4000000) THEN
    SALARY_GRADE := 'B';
    ELSIF (EMP.SALARY >= 3000000) THEN
    SALARY_GRADE := 'C';
    ELSIF (EMP.SALARY >= 2000000) THEN
    SALARY_GRADE := 'D';
    ELSIF (EMP.SALARY >= 1000000) THEN
    SALARY_GRADE := 'E';
    ELSE
    SALARY_GRADE := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('SALARY : ' || EMP.SALARY);
    DBMS_OUTPUT.PUT_LINE('SALARY_GRADE : ' || SALARY_GRADE);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('직원을 찾을 수 없습니다.');
END;
/

---

2) CASE문

CASE <변수명>
    WHEN <조건값1> THEN
    --실행문
    WHER <조건값2> THEN
    --실행문
    WHER <조건값3> THEN
    --실행문
    ELSE
    --실행문
END CASE;

예제 )

이름을 입력 받아서 급여 등급별로 출력하는 예제
급여 등급

100만원 미만 - F 

100만원 이상 - E

200만원 이상 - D

300만원 이상 - C

400만원 이상 - B
500만원 이상 - A

DECLARE
    EMP EMPLOYEE % ROWTYPE; -- TABLE 전체 참조 변수 생성
    V_EMP_NAME VARCHAR(20);
    SALARY_GRADE VARCHAR(10);
BEGIN
    V_EMP_NAME := '&TEMP_NAME';

    SELECT * INTO EMP FROM EMPLOYEE 
    WHERE EMP_NAME = V_EMP_NAME; 
    
    CASE FLOOR(EMP.SALARY / 1000000)
    WHEN 0 THEN
        SALARY_GRADE := 'F';
    WHEN 1 THEN
        SALARY_GRADE := 'E';
    WHEN 2 THEN
        SALARY_GRADE := 'D';
    WHEN 3 THEN
        SALARY_GRADE := 'C';
    WHEN 4 THEN
        SALARY_GRADE := 'B';
    ELSE
        SALARY_GRADE := 'A';
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('SALARY : ' || EMP.SALARY);
    DBMS_OUTPUT.PUT_LINE('SALARY_GRADE : ' || SALARY_GRADE);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('직원을 찾을 수 없습니다.');
END;
/

 

---

반복문

1) BASIC LOOP

LOOP
    -- 반복할 내용
END LOOP;

 

예제 )

DECLARE
    I NUMBER := 0;
BEGIN
    LOOP
        IF I > 5 THEN
            EXIT;
        END IF;
        DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1;
    END LOOP;
END;

 

결과 : 1 2 3 4 5

---

2) FOR LOOP

FOR <카운트 변수> IN [REVERSE] <시작값>..<종료값> LOOP
    -- 반복할 내용
END LOOP;

 

예제 )

DECLARE
    I NUMBER := 0;
BEGIN
    FOR I IN 1.. 5 LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

결과 : 1 2 3 4 5

---

3) WHILE

WHILE <조건문> LOOP
    -- 반복할 내용
END LOOP;

 

예제 )

DECLARE
    I NUMBER := 0;
BEGIN
    WHILE I < 5 LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1;
    END LOOP;
END;
/

결과 : 1 2 3 4 5