[SQL] PL/SQL

안녕하세요. 이번 글은 PL/SQL에 대한 설명입니다.

- PL/SQL이란?

• DB와 관련된 작업을 수행하는 명령어와 실행 필수 요소를 정의하는 명령어의 집합

키워드	필수 유무	설명
DECLARE	선택	선언부이며 실행에 필요한 변수, 상수, 커서 등 선언하는 공간
BEGIN	필수	실행부이며 조건문, 반복문, 함수, SELECT, DML 등을 정의하는 공간
EXCEPTION	선택	실행 중 발생하는 예외 및 오류를 다루는 공간

상단의 표는 PL/SQL를 다루기 위해서 필요하는 기본적인 블록에 대한 설명을 담고 있다. 선언부와 예외는 선택적으로 다룰 수도 있지만 BEGIN은 필수이다.

DECLARE
    BEGIN

	-- 한 줄 주석
    /*
    여러 문 주석
    입니다.
    */
    DBMS_OUTPUT.PUT_LINE('This is PL/SQL Printing');
    DBMS_OUTPUT.PUT_LINE(SYSDATE);

    END;
/

다음과 같이 DBMS_OUTPUT.PUT_LINE()을 통해 프로그래밍 언어에서 실습했던 출력문처럼 Console문을 출력할 수 있는 점을 확인할 수 있다. 또한 주석도 작성할 수 있는데 -- 을 통해 한 줄 주석을 작성할 수 있으며 /**/을 통해 여러 줄로 주석 작성할 수도 있다. PL/SQL을 작성할 때는 세미콜론(;)은 출력문과 END문에만 사용해야 한다. PL/SQL문의 작성을 마칠 때는 실행을 위해 반드시 슬래시(/)을 붙여야 한다.

DECLARE
    V_AGE NUMBER(10) := 26;
    V_NAME VARCHAR2(50) := '홍길동';
    V_COMMENT VARCHAR2(100);

BEGIN
    V_COMMENT := 'HELLO. HAVE A GOOD DAY :)';
    DBMS_OUTPUT.PUT_LINE(V_NAME || '님 (' || V_AGE || ')의 멘트 : ' || V_COMMENT);
    DBMS_OUTPUT.PUT_LINE(SYSDATE);

END;
/

상단의 쿼리문은 DECLARE 선언부를 활용한 PL/SQL문이다. 해당 쿼리문을 실행해보면 선언부에 설정해둔 변수를 기반으로 작성한 출력문이 콘솔창에 출력되는 것을 확인할 수 있다. 이처럼 변수 선언할 때는 [변수명] [자료형] := [값 또는 식]을 통해 선언 및 활용할 수 있다. 또한 프로그래밍 언어처럼 선언부에 선언만 한 채로 BEGIN문에다 변수에 값 또는 식을 대입시킬 수도 있다. 자료형은 NUMBER(), VARCHAR(), VARCHAR2(), DATE, BOOLEAN 총 다섯 가지로 구성되어있다.

DECLARE
    VC_NUMBER CONSTANT NUMBER(10) := 10;

BEGIN
    DBMS_OUTPUT.PUT_LINE('VC_NUMBER : ' || VC_NUMBER);

    VC_NUMBER := 20;
    DBMS_OUTPUT.PUT_LINE('VC_NUMBER : ' || VC_NUMBER);

END;
/

상단의 쿼리문은 CONSTANT라는 키워드를 사용해서 상수를 선언하고 값을 할당한 쿼리문이다. 해당 쿼리문을 실행하면 

ORA-06550: 줄 7, 열5:PLS-00363: 'VC_NUMBER' 식은 피할당자로 사용될 수 없습니다, 줄 7, 열5 :PL/SQL: Statement ignored 라는 오류에 대한 알림이 발생하는 점을 확인할 수 있다. 이와 같은 오류가 발생하는 이유는 상수라는 키워드 특성상 한 번 값을 할당하면 더 이상의 재할당이 안되기 때문이다. 프로그래밍 언어처럼 상수는 변수와 달리 불변의 특성이 있다. 그렇기 때문에 상단 쿼리문을 실행하면 출력이 되지 않는다.

다음과 같이 문제되는 구간을 주석 처리 후 실행하면 정상적으로 실행되는 점 또한 확인할 수 있다.

DECLARE
    VD_START_VALUE NUMBER(10) DEFAULT 100;

BEGIN
    DBMS_OUTPUT.PUT_LINE('VD_START_VALUE : ' || VD_START_VALUE);

    VD_START_VALUE := 200;
    DBMS_OUTPUT.PUT_LINE('VD_START_VALUE : ' || VD_START_VALUE);

END;
/

상단의 쿼리문은 DEFALUT라는 키워드를 사용한 PL/SQL문이다. 다음과 같이 DEFAULT 키워드를 사용해서 변수를 선언하면 별도의 값 또는 식의 대입이 없어도 기본적으로 설정해둔 값이 대입 및 활용가능하도록 설정할 수 있다. 또한 중간에 별도로 변수 안에 값을 임의로 대입할 수도 있다.

DECLARE
    VNNL_REQUIRED_VALUE VARCHAR2(100) NOT NULL := 'NOT NULL VALUE';

BEGIN
    DBMS_OUTPUT.PUT_LINE('VNNL_REQUIRED_VALUE : ' || VNNL_REQUIRED_VALUE);

END;
/

상단의 쿼리문 NOT NULL 키워드를 사용하여 변수 안에 반드시 값이 존재하도록 규정한 쿼리문이다. 실행해보면 이미지처럼 출력문이 정상적으로 출력되는 점을 확인할 수 있다.

DECLARE
    VNNL_REQUIRED_VALUE VARCHAR2(100) NOT NULL := 'NOT NULL VALUE';

BEGIN
    VNNL_REQUIRED_VALUE := '';
    DBMS_OUTPUT.PUT_LINE('VD_START_VALUE : ' || VNNL_REQUIRED_VALUE);

END;
/

상단 쿼리문을 실행하면 ORA-06502: PL/SQL: 수치 또는 값 오류 라는 알림이 발생하면서 쿼리문이 실행되지 않는다.

이러한 오류가 발생하는 이유는 NOT NULL이라는 키워드를 설정해두면 설정해둔 변수 안에는 반드시 값이 있도록 제어되기 때문이다. 그렇기 때문에 '' 처럼 빈 값이 설정되어있으면 오류가 발생하는 점을 확인할 수 있다.

DECLARE
    V_STU_NAME STUDENT_INFO.STU_NAME%TYPE := '홍길동';

BEGIN
    DBMS_OUTPUT.PUT_LINE('V_STU_NAME : ' || V_STU_NAME);

END;
/

다음과 같이 기존 테이블 내 칼럼이 가지고 있는 자료형을 기반으로 변수를 선언하고 값을 대입할 수도 있다.

형식은 [변수명] [테이블명.칼럼명]%TYPE := [값 또는 식]; 이며 VARCHAR2(), DATE와 같이 스칼라형으로 선언하는 것처럼 PL/SQL문을 작성할 수 있다.

DECLARE
    V_STUDENT STUDENT_INFO%ROWTYPE;

BEGIN
    SELECT * INTO V_STUDENT
      FROM STUDENT_INFO
     WHERE STU_NAME = '홍길동';

    DBMS_OUTPUT.PUT_LINE('V_STUDENT.STU_NUM : ' || V_STUDENT.STU_NUM);
    DBMS_OUTPUT.PUT_LINE('V_STUDENT.STU_NAME : ' || V_STUDENT.STU_NAME);
    DBMS_OUTPUT.PUT_LINE('V_STUDENT.STU_DEPT : ' || V_STUDENT.STU_DEPT);

END;
/

이전에는 테이블 내 칼럼명이 가지고 있는 자료형을 참조하는 방식으로 변수를 선언했다면 상단 쿼리문에는 테이블 전체를 참조해서 변수를 선언하고 활용할 수 있다. 형식은 [변수명] [테이블명]%ROWTYPE이며 ROWTYPE을 통해 특정 테이블에서 각각의 열 하나가 아닌 행 전체를 참조하는 방식으로 진행된다. 쿼리문을 보면 BEGIN 문 바로 하단에 SELECT문이 있는데 * (또는 칼럼명) 옆에 INTO 키워드와 변수명이 있는데 이는 SELECT 문 실행을 통한 결과를 변수에 대입시키는 역할을 하고 있다. 출력문에다 변수명.칼럼명을 입력 후 실행하면 쿼리문에 대한 결과를 확인할 수 있다.

- 조건 제어문

• IF 조건문 : IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF
• CASE 조건문 : 단순 CASE문, 검색 CASE문

상단의 이미지는 IF 조건문의 세 가지 종류에 대한 형식을 설명해주고 있다.

DECLARE
    V_STUDENT STUDENT_INFO%ROWTYPE;

BEGIN
    SELECT * INTO V_STUDENT
      FROM STUDENT_INFO
     WHERE STU_NAME = '홍길동';

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.STU_NAME || ' 학생은 ' || V_STUDENT.STU_GRADE || '학년입니다.');
    IF V_STUDENT.STU_GRADE = '1' THEN
        DBMS_OUTPUT.PUT_LINE('신입생');
    ELSIF V_STUDENT.STU_GRADE = '4' THEN
        DBMS_OUTPUT.PUT_LINE('졸업예정자');
    ELSE
        DBMS_OUTPUT.PUT_LINE('일반 재학생');
    END IF;

END;
/

상단의 쿼리문은 IF-THEN-ELSIF문을 활용해서 작성한 PL/SQL문이다. 해당 쿼리문을 실행해보면 홍길동 학생은 2학년이기 때문에 어느 조건문에도 해당되지 않으므로 ELSE문으로 이동하게 되며 일반 재학생이라고 출력될 것이다.

좌측의 CASE문은 값으로만 진위여부를 판단하는 단순 CASE문이며 우측은 조건식을 통한 진위여부를 판단하는 검색 CASE문이다. 전반적인 구조 내지 형식은 거의 동일하며 달라진 점이 있다면 WHEN과 THEN 사이에 들어가는 것이 단순한 값인지 조건식인지에 대한 차이와 CASE 옆에 비교 기준이 존재하는지에 대한 차이가 존재한다. 

DECLARE
    V_STUDENT STUDENT_INFO%ROWTYPE;

BEGIN
    SELECT * INTO V_STUDENT
      FROM STUDENT_INFO
     WHERE STU_NAME = '홍길동';

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.STU_NAME || ' 학생은 ' || V_STUDENT.STU_GRADE || '학년입니다.');

    CASE V_STUDENT.STU_GRADE
        WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('신입생입니다.');
        WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('졸업예정자입니다.');
        ELSE DBMS_OUTPUT.PUT_LINE('일반 재학생입니다.');
    END CASE;
    
END;
/

상단의 쿼리문은 WHEN 옆에 단순 값을 활용해서 작성한 단순 CASE PL/SQL 문이다.

DECLARE
    V_STUDENT STUDENT_INFO%ROWTYPE;

BEGIN
    SELECT * INTO V_STUDENT
      FROM STUDENT_INFO
     WHERE STU_NAME = '홍길동';

    DBMS_OUTPUT.PUT_LINE(V_STUDENT.STU_NAME || ' 학생은 ' || V_STUDENT.STU_GRADE || '학년입니다.');

    CASE
        WHEN V_STUDENT.STU_GRADE = 1 THEN DBMS_OUTPUT.PUT_LINE('신입생입니다.');
        WHEN V_STUDENT.STU_GRADE < 4 OR V_STUDENT.STU_GRADE >= 2 THEN DBMS_OUTPUT.PUT_LINE('일반 재학생입니다.');
        ELSE DBMS_OUTPUT.PUT_LINE('졸업예정자입니다.');
    END CASE;

END;
/

다음 쿼리문은 조건식을 활용한 검색 CASE문을 활용한 PL/SQL문이다. 상단 두 개에 대한 출력 결과는 모두 동일하다.

- 반복 제어문 (LOOP)

• BASIC LOOP : 기본 반복문
• WHILE LOOP : 조건식에 대한 결과를 기반으로 반복 수행
• FOR LOOP : 반복 횟수를 지정하여 반복 수행
• CUSOR FOR LOOP : 커서를 활용한 반복 수행
• EXIT : 수행 중인 반복 종료
• EXIT-WHEN : 조건식에 만족할 때 반복 종료
• CONTINUE : 수행 중인 반복의 현재 주기 생략
• CONTINUE-WHEN : 조건식에 만족할 때 현재 반복 주기 생략

반복 제어문에 대한 전반적인 문법 구조는 상단의 이미지와 같다.

DECLARE
    V_NUM NUMBER(10) := 0;

BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(V_NUM);
        V_NUM := V_NUM + 1;

        EXIT WHEN V_NUM > 10; -- 반복을 멈추는 시점 설정
    END LOOP;

END;
/

상단의 쿼리문은 단순 LOOP문을 활용한 PL/SQL문이다. 해당 쿼리문을 실행해보면 이미지처럼 0부터 10까지 반복문이 실행되는 점을 확인할 수 있다. 이때 EXIT WHEN 키워드를 사용해서 반복문을 종료하는 시점을 설정해서 10까지만 반복하고 종료하도록 설정되어있는 점을 확인할 수 있다. IF THEN 문을 사용해서도 반복문의 종료시점을 설정할 수 있다.

DECLARE
    V_NUM NUMBER(10) := 0;

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

END;
/

다음은 WHILE LOOP를 사용해서 작성한 쿼리문이다. 출력 결과는 상단의 단순 LOOP문과 동일하다. 해당 쿼리문을 살펴보면 WHILE와 LOOP 사이에 조건식을 작성해서 반복문을 구현하는 방식이며 V_NUM <= 10을 통해 V_NUM의 값이 10이 될 때까지 내부 로직을 반복해서 실행하겠다는 점을 확인할 수 있다.

DECLARE

BEGIN
    FOR i IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;

END;
/

상단의 쿼리문은 FOR LOOP문을 활용해서 반복문을 작성한 PL/SQL문이다. 해당 건의 결과는 상단의 두 개의 쿼리문과 동일하다. 해당 쿼리문을 보면 프로그래밍 언어와 거의 비슷한 구조를 지니고 있으며 0부터 10까지 반복하다가 반복문을 종료하고 있다는 점을 볼 수 있다.

DECLARE
BEGIN
    FOR i IN 0 .. 10 LOOP
        CONTINUE WHEN i = 2 OR i = 5;
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;

END;
/

상단 쿼리문은 FOR LOOP안에 CONTINUE WHEN을 활용해서 특정 조건에서 반복 내지 출력을 생략하는 PL/SQL문이다. 쿼리문을 실행해보면 상단 이미지처럼 특정 조건에 달성한 경우에만 출력을 생략하고 그 외에는 그대로 출력해주는 것을 확인할 수 있다. CONTINUE WHEN 뒤에 i = 2 OR i = 5라고 되어있는데 이는 i의 값이 2 또는 5일 때 건너뛴다는 뜻이다.