OracleSQL - PL/SQL

PL/SQL 이란?

PL/SQL (Procedural Laguage Extension to SQL)은 프로그래밍 언어의 특성을 가지는 SQL의 확장이며 데이터 조작과 질의 문장은 PL/SQL 의 절차적 코드 안에 포함된다. 즉, PL/SQL을 사용하면 SQL로 할 수 없는 절차적 작업이 가능하다.

 

여기서 절차적 이라는 단어가 가지는 의미는 어떤 것이 어떤 과정을 거쳐서 어떻게 완료되는지 그 방법을 정확하게 코드에 기술한다는 것을 의미한다.

 

PL/SQL은 절차적으로 표현하기 위해 변수를 선언할 수 있는 기능, 참과 거짓을 구별할 수 있는 기능, 실행 흐름을 컨트롤 할 수 있는 기능 등을 제공한다.

 

형식 및 구조

PL/SQL은 블럭 구조로 되어 있으며, 블럭은 선언 부분, 실행 부분, 예외 처리 부분의 세 부분으로 구성되어 있다. 반드시 실행 부분은 존재해야 하며 구조는 다음과 같다.

 

[DECLARE]
    -- 선언문(DECLAREATIONS) (변수)
BEGIN
    -- 실행문(STATEMENTS)     (실행)

    [EXCEPTION]
        -- 예외 처리문 (EXCEPTION HANDLERS)
END;

화면에 무언가를 띄우기 위해 DBMS에서 제공하는 DBMS_OUTPUT 패키지를 사용할 수 있다. DBMS_OUTPUT 패키지에서 PUT_LINE() 기능을 사용하면 화면에 원하는 데이터를 출력할 수 있다. 하지만  DBMS_OUTPUT.PUT_LINE()으로 화면에 결과를 출력하기 위해서는 먼저 DBMS에 환경변수를 설정해줘야 한다. 

 

SET SERVEROUTPUT ON;

-- SERVEROUTPUT 세션은 기본적으로 OFF이고, DBMS 실행시 최초 한번만 실행 해주면 된다.

다음은 PL/SQL의 사용 예이다.

 

DECLARE
    -- 선언부
    D1  NUMBER := 10;     -- " := " 은 PL/SQL에서 대입의 기능을 한다.
    D2  VARCHAR2(30) := 'HELLO';
    D3  VARCHAR2(30) := 'Oracle';
BEGIN
    -- 실행부
    DBMS_OUTPUT.PUT_LINE(D1);
    DBMS_OUTPUT.PUT_LINE(D2);
    DBMS_OUTPUT.PUT_LINE(D3);
END;
--==>> 
/*
10
HELLO
Oracle


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/


DECLARE
    -- 선언부
    D1  NUMBER := 10;     -- " := " 은 PL/SQL에서 대입의 기능을 한다.
    D2  VARCHAR2(30) := 'HELLO';
    D3  VARCHAR2(30) := 'Oracle';
BEGIN
    -- 실행부
    -- (연산 및 처리)

    -- (결과 출력)
    DBMS_OUTPUT.PUT_LINE(D1);
    DBMS_OUTPUT.PUT_LINE(D2);
    DBMS_OUTPUT.PUT_LINE(D3);
END;
--==>> 
/* 
10 
HELLO 
Oracle 


PL/SQL 프로시저가 성공적으로 완료되었습니다. 
*/

 

IF 문(조건문)

IF ~ THEN ~ ELSE ~ END IF;

ELSIF  --> PL/SQL에서는 ELSE IF 를 ELSIF로 사용해야 한다.

PL/SQL 의 IF 문은 다른 언어의 IF 조건문과 거의 유사하다. 일치하는 조건에 따라 선택적으로 작업으로 수행할 수 있도록 한다. TRUE 이면 THEN 과 ELSE 사이의 문장을 수행하고, FALSE나 NULL 이면 ELSE 와 END IF; 사이의 문장을 수행하게 된다.

 

기본 형식 및 구조는 다음과 같다.

 

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

사용 예는 다음과 같다.

 

-- 변수에 임의의 값을 대입하고 출력하는 구문 작성

DECLARE
    GRADE CHAR; -- NUMBER 형식은 () 없으면 최대범위 CHAR 형식은 () 없으면 최단범위(1문자)
BEGIN
    GRADE := 'F';

    IF GRADE = 'A'
        THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
    ELSIF GRADE = 'B'
        THEN DBMS_OUTPUT.PUT_LINE('GOOD');
    ELSE
        DBMS_OUTPUT.PUT_LINE('FAIL');
    END IF;
END;
--==>>
/*
EXCELLENT


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

 

CASE 문(조건문)

CASE ~ WHEN ~ THEN ~ ELSE ~ END CASE;

SQL에서 사용했던 CASE WHEN THEN ELSE END 구문과 거의 비슷하다. 기본 형식 및 구조는 다음과 같다.

 

CASE 변수
    WHEN 값1 THEN 실행문1;
    WHEN 값2 THEN 실행문2;
    ELSE 실행문3;
END CASE;

사용 예는 다음과 같다.

 

DECLARE
    -- 주요 변수 선언
    SEL        NUMBER := 3;
    RESULT   VARCHAR2(10);
BEGIN
    -- 연산 및 처리
    CASE SEL
        WHEN 1
        THEN DBMS_OUTPUT.PUT_LINE('남자입니다.');
        WHEN 2
        THEN DBMS_OUTPUT.PUT_LINE('여자입니다.');
        ELSE
        DBMS_OUTPUT.PUT_LINE('확인불가');
    END CASE;
END;
--==>> 
/*
(남자입니다.)
(여자입니다.)
확인불가


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

 

외부 입력 처리

PL/SQL 에서는 외부로 부터 사용자에게 입력받은 값을 내부 변수로 가져오는 기능을 제공한다. 기본 형식 및 구조는 다음과 같다.

 

ACCEPT 변수명 PROMPT '메시지';

--> 외부 변수로부터 입력받은 데이터를 내부 변수에 전달할 때 
      [ &외부변수명] 형태로 접근하게 된다.

사용 예는 다음과 같다.

 

ACCEPT NUM PROMPT '(남자 1, 여자 2) 입력하세요';

DECLARE
    -- 주요 변수 선언
    SEL        NUMBER := #  --> 외부 변수를 가져와 SEL에 대입
BEGIN
    -- 연산 및 처리
    CAES SEL
         WHEN 1
         THEN DBMS_OUTPUT.PUT_LINE('남자입니다.');
         WHEN 2
         THEN DBMS_OUTPUT.PUT_LINE('여자입니다.');
         ELSE
         DBMS_OUTPUT.PUT_LINE('확인불가');
    END CASE;
END;

 

기본 반복문

LOOP 문 : LOOP ~ END LOOP

조건에 상관 없이 무조건 반복하는 구문이다. 종료 조건이 없는 경우 무한 루프가 돈다.

 

-- 형식 및 구조
LOOP 
    -- 실행문
    EXIT WHEN 종료조건;

END LOOP;


-- 1 부터 10까지의 수 출력
DECLARE
    N NUMBER;
BEGIN 
    N := 1;
    
    LOOP 
        DBMS_OUTPUT.PUT_LINE(N);
        EXIT WHEN N >= 10;
        N := N + 1;
    END LOOP;

END;

 

WHILE 문 : WHILE LOOP ~ END LOOP

제어 조건이 TRUE 인 동안 일련의 문장을 반복하기 위해 WHILE LOOP 구문을 사용한다. 조건은 반복이 시작되는 시점에 체크하게 되어 LOOP 내의 문장이 한 번도 수행되지 않을 수도 있다. LOOP를 시작할 때 조건이 FALSE 이면 반복 문장을 탈출하게 된다.

 

-- 형식 및 구조
WHILE 조건 LOOP    -- 조건이 참일 경우 반복 수행
    -- 실행문;
END LOOP;


-- 1 부터 10까지의 수 출력
DECLARE
    N NUMBER := 0;
BEGIN 

    WHILE N <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N + 1;
    END LOOP;

END;

 

FOR 문 : FOR LOOP ~ END LOOP

시작수에서 1씩 증가하여 끝냄수가 될 때 까지 반복 수행한다.

 

-- 형식 및 구조
FOR 카운터 IN 시작수 .. 끝냄수 LOOP
END LOOP;


-- 1 부터 10까지의 수 출력
DECLARE 
    N    NUMBER;
BEGIN

    FOR N IN 1 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;

END;

 

특정 데이터타입 

%TYPE

%TYPE 은 특정 테이블에 포함되어 있는 컬럼의 자료형(데이터타입)을 참조하는 데이터타입이다.

 

-- 형식 및 구조
변수명 테이블명.컬럼명%TYPE [:= 초기값];


-- 사용 예 : 103 ID를 가진 사원의 FIRST_NAME 컬럼의 데이터 값을 얻어온다.
DECLARE 
    V_NAME    EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
    SELECT FIRST_NAME INTO V_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEES_ID = 103;
END;

 

%ROWTYPE

%ROWTYPE은 테이블의 레코드와 같은 구조의 구조체 변수를 선언하는 데이터타입이다.

 

-- 형식 및 구조
변수명 테이블명%ROWTYPE;


-- 사용 예 : 103 ID를 가진 사원의 FIRST_NAME, PHONE_NUMBER, EMAIL 컬럼의 데이터 값을 얻어온다.
DECLARE 
    V_EMP   EMPLOYEES%ROWTYPE;
BEGIN
    SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
               INTO V_EMP.FIRST_NAME, V_EMP.PHONE_NUMBER, V_EMP.EMAIL
    FROM EMPLOYEES
    WHERE EMPLOYEES_ID = 103;
END;

주의할 점이 있다면 여러 개의 행(ROWS) 정보를 얻어와 변수에 담으려 하면 저장하는 것 자체가 불가능하기 때문에 오라클 내부에서 에러가 발생한다. 여러 개를 가져오기 위해서는 반복문을 사용해야 한다.

 

-- 사원번호 1001 ~ 1060 까지의 모든 사원의 이름, 전화번호, 부서 정보를 출력 

DECLARE
    V_INSA TBL_INSA%ROWTYPE;
    V_NUM TBL_INSA.NUM%TYPE := 1001;
BEGIN
    LOOP

        SELECT NAME, TEL, BUSEO
                   INTO V_INSA.NAME, V_INSA.TEL, V_INSA.BUSEO
        FROM TBL_INSA
        WHERE NUM = V_NUM; -- WHERE NUM = 1001, 1002, ...;

        DBMS_OUTPUT.PUT_LINE(V_INSA.NAME || ' - ' || V_INSA.TEL || ' - ' || V_INSA.BUSEO);
       
        EXIT WHEN V_NUM >= 1060;
        V_NUM := V_NUM + 1;
    END LOOP;
END;

 

참고

1. INSERT, UPDATE, DELETE, (MERGE)
--> DML (Data Manipulation Language)
-- COMMIT / ROLLBACK 이 필요하다.

2. CREATE, DROP, ALTER, (TRUNCATE)
--> DDL (Data Definition Language)
-- 실행하면 자동으로 COMMIT 된다.

3. GRAINT, REVOKE
--> DCL (Data Control Language)
-- 실행하면 자동으로 COMMIT 된다.

4. COMMIT, ROLLBACK
--> TCL (Transaction Control Language)

-- 정적 PL/SQL 문 -> DML문, TCL문만 사용 가능
-- 동적 PL./SQL 문 -> DML문, DDL문, DCL문, TCL문 모두 사용 가능