21.02.18 TIL (D+27 ORACLE PL/SQL)

학습 내용

 

PL/SQL 이란

 -> PROCEDURAL EXTENTION TO STRUCTURED QUERY LANGUAGE

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

 

SQL을 확장한 순차적인 언어

- PROCEDURE, FUNCTION, TRIGGER(자동호출함수, CALL BACK)

 

PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로

수행속도를 향상 시킬수 있다.

 

 

 - SCRIPT 구조

DECLARE
    -- 선언부
    MESSAGE VARCHAR2(10);
BEGIN
    -- 실행부
    MESSAGE := 'HELLO PL';
    
    dbms_output.put_line('MESSAGE = ' || message);
END;
/

    1. 선언부 : 변수, 상수, 초기화
    2. 실행부 : QUERY, 제어문, 실제처리, BEGIN으로 시작하고 END로 종료된다.
    3. 예외처리 : 각종 오류를 정의하고 처리하는 부분으로 선택 사항이다.

 

 

- SET SERVEROUTPUT ON

 

CONSOLE에 출력을 설정 (기본적으로 PL/SQL은 결과물을 보여주지 않는다.)

SET SERVEROUTPUT ON

 

---

 

 

- IF 문

 

기본적인 구조에서 IF문이 들어간 예제이다.

JAVA에서 공부한 IF문과 흡사하게 생겼으며, 다만 차이가 나는 것은

IF에 대한 조건이 끝나면 THEN을 입력해주고, ELSE IF는 ELSIF로 작성한다는 것.

마지막에는 IF절이 끝났다는 END IF; 를 꼭 붙어주어야 한다.

 

DECLARE
    COUNTER INTEGER;
BEGIN
    COUNTER := 1;
    COUNTER := COUNTER + 1;
    
    IF counter = 1 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER = 1');
    ELSIF COUNTER = 2 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER = 2');
    ELSIF COUNTER = 3 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER = 3');
    ELSIF COUNTER = 4 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER = 4');
    ELSE
        DBMS_OUTPUT.PUT_LINE('COUNTER IS NULL');
    END IF; 
        
END;
/

-- 출력값 : COUNTER = 2

 

- FOR 문

 

예제와 출력값을 비교해보면 어떤 원리로 작동되는지 쉽게 파악될 것이다.

 

DECLARE
    COUNTER INTEGER;
    I INTEGER;      -- LOOP용 변수

BEGIN

    FOR I IN 1..9 LOOP
        DBMS_OUTPUT.put_line('I = ' || I);
    END LOOP;

END;
/

/*
출력값 :

I = 1
I = 2
I = 3
I = 4
I = 5
I = 6
I = 7
I = 8
I = 9
*/

 

- LOOP문

FOR문 없이 LOOP문으로만 사용 가능하다.

V_COUNT가 10이 되면 LOOP을 빠져나가고, V_COUNT는 1씩 증가한다.

 

DECLARE
    V_COUNT NUMBER := 0;
    V_TOTAL NUMBER := 0;
BEGIN
    LOOP 
        EXIT WHEN V_COUNT = 10;
        
        v_count := v_count + 1;
        v_total := v_total + v_count;
        DBMS_OUTPUT.put_line('v_count = ' || v_count);
        
    END LOOP;
    
        DBMS_OUTPUT.put_line('v_total = ' || v_total);

END;
/
/*
결과값:
v_count = 1
v_count = 2
v_count = 3
v_count = 4
v_count = 5
v_count = 6
v_count = 7
v_count = 8
v_count = 9
v_count = 10
v_total = 55
*/

 

- WHILE 문

 

WHILE문은 먼저 조건을 명시하고 LOOP으로 감싸준다.

조건이 FALSE가 되면 LOOP을 빠져나가게 된다.

 

DECLARE
    V_COUNT NUMBER := 0;
    V_TOTAL NUMBER := 0;
BEGIN
    WHILE v_count < 10
    LOOP
        v_count := v_count + 1;
        v_total := v_total + v_count;
        DBMS_OUTPUT.put_line('v_count = ' || v_count);
    END LOOP;
        DBMS_OUTPUT.put_line('v_total = ' || v_total);

END;
/
/*
결과값 : 
v_count = 1
v_count = 2
v_count = 3
v_count = 4
v_count = 5
v_count = 6
v_count = 7
v_count = 8
v_count = 9
v_count = 10
v_total = 55
*/

- GOTO문

 

 요즘엔 많이 사용되지 않는 구문이라고 한다.

 구조만 보고 어떻게 작동되는 것인지 참고만 하자.

 

DECLARE
    V_NAME VARCHAR2(10) := 'LEE';
    V_CASE NUMBER := 0;
BEGIN
    CASE WHEN MOD(V_CASE, 2) = 0 THEN 
            GOTO TEST1;
         WHEN MOD(V_CASE, 2) = 1 THEN
            GOTO TEST2;
         ELSE
            GOTO ERR;
     END CASE;
     
     <<TEST1>>
        DBMS_OUTPUT.put_line(v_name || 'IS WOMEN');
        GOTO SUB_END;
     <<TEST2>>
        DBMS_OUTPUT.put_line(v_name || 'IS MEN');
        GOTO SUB_END;
     <<ERR>>
        DBMS_OUTPUT.put_line('ERROR');
        GOTO SUB_END;
        
     <<SUB_END>>
        DBMS_OUTPUT.put_line('END');
     
END;
/
/*
결과값 :
LEE IS WOMEN
END
*/

 

- 예외처리부

 

예외처리가 발생할 예시를 들었고, 예외가 발생되면 다음과 같이 작동된다.

 

DECLARE
    COUNTER INTEGER;
BEGIN
    COUNTER := 10;
    COUNTER := COUNTER / 0;

EXCEPTION WHEN OTHERS THEN  -- 예외처리
    dbms_output.put_line('예외가 발생했습니다.');

END;
/

--결과값 : 예외가 발생했습니다.

 

- VARRAY (VARIABLE ARRAY)

 

고정 길이를 가진 배열을 생성할 수 있다. (SQL에서는 배열 INDEX가 0이 아닌 1부터 시작한다는 것을 유의하자.)

 

형식 : TYPE 타입명 IS  VARRAY (크기) OF 요소값타입 [NOT NULL];

DECLARE
    TYPE VARRAY_TEST IS VARRAY(3) OF INTEGER;
 -- TYPE VARRAY명 IS VARRAY(최대 크기) OF 요소값_타입;
    MYARR VARRAY_TEST;
BEGIN
    MYARR := VARRAY_TEST(11, 22, 33);
    
    DBMS_OUTPUT.PUT_LINE('MYARR(2) = ' || MYARR(2));
END;
/
--결과값 : MYARR(2) = 22

 

- ACCEPT

 

데이터 값을 입력받기 위해 쓰는 구문이다.

ACCEPT p_DEPTNO PROMPT '부서번호를 입력하시오(급여의 합)'

결과값 : (아래와 같이 데이터 값을 받을 창이 생성된다.) 

 

문제풀이

 

EX) 사원번호 입력 받고 그 사원의 급여에 1000을 더한 값으로 갱신(UPDATE)하라.
사원번호, 갱신된 급여를 출력(SELECT)하라.

 

ACCEPT p_empno PROMPT '사원번호를 입력하시오(+1000)'

DECLARE
    v_sal NUMBER := 1000;
    v_salTotal NUMBER;
BEGIN
    -- select
    SELECT salary INTO v_salTotal
    FROM employees
    WHERE employee_id = &p_empno;

    dbms_output.put_line(&p_empno || '번 사원의 급여 = ' || v_salTotal);
    
    -- update
    UPDATE employees
    SET salary = salary + v_sal
    WHERE employee_id = &p_empno;    
    
    SELECT salary INTO v_salTotal
    FROM employees
    WHERE employee_id = &p_empno;

    dbms_output.put_line(&p_empno || '번 사원의 급여 = ' || v_salTotal);

END;
/

 

- ROWTYPE

 

- 테이블이나 뷰 내부의 컬럼 데이터형, 크기, 속성을 그대로 사용 할 수 있다.

- ROWTYPE 앞에 테이블 이름을 입력한다. EX) EMP%ROWTYPE

- 각 컬럼마다의 타입을 가져오려면 %TYPE을 붙여준다. EX) EMP.SALARY%TYPE

DECLARE
    V_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE;
    V_FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
    V_EMP EMPLOYEES%ROWTYPE;    -- COLUMN명이 자동적으로 변수명이 된다.
                                --%ROWTYPE 앞에 오는 것은 테이블 이름
                                -- EX) V_EMP.SALARY
BEGIN
    SELECT employee_id, first_name, salary
        INTO V_EMP.employee_id, V_EMP.first_name, V_EMP.SALARY 
    FROM employees
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || V_EMP.employee_id);
    DBMS_OUTPUT.PUT_LINE('사원명 : ' ||V_EMP.first_name);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || V_EMP.SALARY);
    
END;
/

 

---

 

  PROCEDURE   - INSERT, DELETE, UPDATE 에 사용된다.
  FUNCTION    - SELECT만 사용된다.
  TRIGGER     - UTIL (=CALLBACK)

 

---

 

PROCEDURE : 절차형 SQL을 활용하여 특정 기능을 수행하는 트랜잭션 언어.

 파라미터에는 IN, OUT이 모두 올 수 있다.
       

기본 형식

CREATE OR REPLACE PROCEDURE MYPROC(INNUM IN NUMBER, OUTNUM OUT NUMBER)
IS
    -- 선언부
BEGIN
    -- 실행부
    DBMS_OUTPUT.PUT_LINE('INNUM : ' || INNUM);
    
    OUTNUM := 333;
    
    -- 예외처리부
END;
/

 

호출 방법

 

VAR VAL NUMBER;      
  
EXECUTE myproc(111, :VAL);

PRINT VAL;

/*
결과값 :
       VAL
----------
       333
*/

 

매개 변수가 없어도 사용 가능하다.

 

CREATE OR REPLACE PROCEDURE HELLOPROC
IS
    MSG VARCHAR2(20);
    
BEGIN
    MSG := 'HELLOPROC 호출';
    DBMS_OUTPUT.PUT_LINE(MSG);
END;
/

EXECUTE helloproc;

-- 결과값 : HELLOPROC 호출

 

- FUNCTION

 

    구성이 프로시저와 유사하지만 파라미터에 IN만 사용이 가능하다.

    반환될 값은 반드시 RETURN 으로 넣어줘야 한다.
    SELECT

 

 구문 안에 리턴 값이 존재한다.

CREATE OR REPLACE FUNCTION FUNC(P_VAL IN NUMBER)
    RETURN NUMBER
IS
    V_VAL NUMBER;
BEGIN
    V_VAL := P_VAL;
    V_VAL := V_VAL * 2;
    
    RETURN V_VAL;
END;
/

SELECT FUNC(3)
FROM DUAL;

-- 결과값 : 6