[ORACLE] DB의 PL/SQL

• Procedure Language Extension to SQL 의 약자이다.

• 데이터 베이스 상에서 반복문( while, for ), 제어문( if )등을 지원한다.

  ​

  
  
  

PL/SQL 의 구성

1. 선언부 (선택)
2. 실행부(필수)
3. 예외처리부(선택)

DECLARE
  정의문( 선언부 )
BEGIN
  실행문( 실행부 )
EXCEPTION
  예외 처리문( 예외 처리부 )
END;
/

PL/SQL 의 사용

SET SERVEROUTPUT ON; -- 출력을 가능하도록 해준다.

DECLARE -- 선언부
    V_NAME VARCHAR2(30) := 'PL/SQL';
BEGIN -- 실행부
    DBMS_OUTPUT.PUT_LINE('오라클과' || V_NAME);
END; -- 종료
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;

• 선언부( DECLARE )에서 V_NAME을 VARCHAR2(30)로 선언했다.
  • V_NAME은 변수처럼 이용이 가능하다.
• 실행부( BEGIN )에서 DBMS_OUTPUT.PUT_LINE(); 함수를 사용해서 문자를 출력하고 있다.
  • V_NAME을 변수처럼 호출해서 출력한다.

PL/SQL 반복문

SET SERVEROUTPUT ON;
DECLARE
    V_CNT NUMBER := 0;
    V_TOT NUMBER := 0;
BEGIN
    LOOP EXIT WHEN V_CNT = 12;
      V_CNT :=V_CNT + 3;
      V_TOT := V_TOT + V_CNT;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
    DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);
END;    
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;

• 선언부( DECLARE )에서 V_CNT, V_TOT 를 각각 0으로 초기화한다.
• 실행부( BEGIN )에서 반복문을 실행한다.
  • LOOP ~ LOOP END; 사이에 반복문을 정의한다.
  • EXIT 반복문이 종료되는 시점을 의미한다.( V_CNT가 12일때 종료)
  • V_CNT는 루프마다 3씩 증가된다.
  • V_TOT는 V_CNT의 총합을 나타낸다.
  • DBMS_OUTPUT.PUT_LINE(); 함수를 통해 연산 결과를 출력한다.

PL/SQL WHILE문

SET SERVEROUTPUT ON;
DECLARE
    V_CNT NUMBER := 0;
    V_TOT NUMBER := 0;
BEGIN
    WHILE V_CNT < 10
    LOOP
        V_CNT := V_CNT +1;
        V_TOT :=V_TOT + V_CNT;
    END LOOP;

     DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
     DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);
END;    
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;

• WHILE문은 종료 조건으로 V_CNT < 10 을 사용하고 있다.
• 이 외 부분은 LOOP 문과 같다.

PL/SQL FOR문

SET SERVEROUTPUT ON;
DECLARE
    V_CNT NUMBER := 0;
    V_TOT NUMBER := 0;
BEGIN
    FOR V_CNT IN 1 .. 10
    LOOP
        V_TOT := V_TOT + V_CNT;
    END LOOP;

     DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
     DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);

END;    
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;

• FOR문은 종료조건을 IN을 사용했다.
• 1 IN 10은 1부터 10까지를 의미한다.
  • 루프가 10번 돈다.

PL/SQL CASE문

자바의 swich case 문과 비슷한 기능을 PL/SQL도 제공한다.

SET SERVEROUTPUT ON;
DECLARE
    V_NAME VARCHAR2(30) := 'LEE';
    V_CASE NUMBER := 1;

BEGIN
    CASE WHEN MOD(V_CASE, 2) = 0 THEN GOTO TEST1;
        WHEN MOD(V_CASE, 20) = 1 THEN GOTO TEST2;
        ELSE GOTO ERR;
    END CASE;

<<TEST1>>
    DBMS_OUTPUT.PUT_LINE(V_NAME || 'IS WOMAN');
    GOTO SUB_END;
<<TEST2>>
    DBMS_OUTPUT.PUT_LINE(V_NAME || 'IS MAN');
    GOTO SUB_END;
<<ERR>>
    DBMS_OUTPUT.PUT_LINE('ERROR');
    GOTO SUB_END;

<<SUB_END>>
    DBMS_OUTPUT.PUT_LINE('EXIT SUB : ' || V_NAME);
END;
/
SET SERVEROUTPUT OFF;

• CASE WHEN 조건을 통해 조건이 만족한다면( THEN ) GOTO 이동를 통해 이동한다.
• << 구간 이름 >> 구간을 정의할 수 있다.
  • GOTO를 통해 구간으로 이동할 수 있다.

PL/SQL 입력 처리

PL/SQL을 사용해서 입력 프롬프트를 열어 값을 직접 입력받게 할 수 있다.

-- 입력 창을 띄운다.
ACCEPT P_NAME PROMPT 'NAME: '
ACCEPT P_SAL PROMPT 'SALARY: '
ACCEPT P_DEPTNO PROMPT 'DEPTNO: '


DECLARE
    V_NAME VARCHAR2(10) := UPPER('&P_NAME');
    V_SAL NUMBER(7, 2) := &P_SAL;
    V_DEPTNO NUMBER(2) := &P_DEPTNO;

BEGIN
    IF V_DEPTNO = 30 THEN
        V_SAL := V_SAL * 1.2;
    ELSIF V_DEPTNO = 60 THEN
        V_SAL := V_SAL * 1.1;
    END IF;

    INSERT INTO EMPLOYEES(
        EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID, JOB_ID, EMAIL)
    VALUES(
        EMPLOYEES_SEQ.NEXTVAL, V_NAME, SYSDATE, V_DEPTNO,
        'IT_PROG', V_NAME||'@NAVER.COM'
    );        
END;
/

  

• ACCEPT 명령어를 사용해서 직접 입력을 받을 수 있다.
• 입력받은 값은 P_NAME, P_SAL, P_DEPTNO 에 저장할 수 있다.
• 선언부에서 입력받은 값들(P_NAME, P_SAL, P_DEPTNO)을 통해 V_NAME, V_SAL, V_DEPT를 초기화한다.
• IF ~ THEN 문법을 사용해서 조건문을 작성한다.
  • ELSIF 는 ELSE IF를 의미한다.
  • IF END를 사용해서 조건문을 닫아준다.
• INSERT INTO 를 사용해 테이블에 직접 입력을 해준다.

/*
부서 번호를 입력받아서 급여의 함을 출력하는 SCRIPT를 작성하라.
*/
SET SERVEROUTPUT ON;
ACCEPT DEPTNO PROMPT 'DEPT NUMBER:';

DECLARE
    V_SAL NUMBER := 0;
BEGIN
    SELECT SUM(SALARY) INTO V_SAL
    FROM EMPLOYEES WHERE DEPARTMENT_ID = &DEPTNO;

    DBMS_OUTPUT.PUT_LINE(&DEPTNO||'부서의 합 : ' || TO_CHAR(V_SAL, '&999,999,999');
END;
/
SET SERVEROUTPUT OFF;




/*
사원 번호가 101인 사원의 급여에 1000을 더하여 갱신하라
*/
SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;
SET SERVEROUTPUT ON;

DECLARE
    SAL NUMBER := 0;
    V_SAL EMPLOYEES.SALARY%TYPE := 1000;
BEGIN
    UPDATE EMPLOYEES
    SET SALARY = SALARY + V_SAL
    WHERE EMPLOYEE_ID=101;
END;
/

SET SERVEROUTPUT OFF;

출처 : https://qkrrudtjr954.github.io/data%20base/2017/12/18/pl-sql.html