27일차//[Oracle] PL 기초

장점

 

1. 대용량 데이터를 연산해야 할 때, WAS등의 서버로 전송해서 처리하려면 네트워크에 부하가 많이 걸릴 수 있다. 이때 프로시져나 함수를 사용하여 데이터를 연산하고 가공한 후에, 최종 결과만 서버에 전송하면 부담을 많이 줄일 수 있다.

 

2. 로직을 수정하기 위해 서버를 셧다운 시키지 않아도 된다. 서버에서는 단순히 DB에 프로시저를 호출하여 사용하면 된다.

 

3. 쿼리문을 직접 노출하지 않는 만큼, SQL injection의 위험성이 줄어든다.

 

4. 블록 단위로 유연하게 사용할 수 있다. 

 

 => 또한 SQL의 다음 단점을 해결 가능하다.

변수가 없다.

한번에 하나의 명령문만 사용 가능하기 떄문에 트래픽이 상대적으로 증가한다.

제어문이 사용 불가. (IF, LOOP)

예외처리가 없다. 등등

 

단점

 

1. 유지보수가 힘들다.

 

2. 대용량 처리가 많을 경우, DB에 부하를 줄 수 있다.

 

3. Git 같은 형상관리를 사용할 수 없다.

 

 

PL 의 기본구조

Script 구조

 

선언부(DECLARE) 변수, 상수, 초기화

실행부(BEGIN) QUERY, 제어문, 실제 처리 필수

예외처리부(EXCEPTION)  각종 오류 

END 필수

변수명 := 초기값

 

DBMS_OUTPUT.PUT_LINE(출력할 내용) 같이 사용. 

예시

SET SERVEROUTPUT ON   --콘솔 출력문장 보이게 한다.  -- 한번실행해야지 뒤에가 출력된다

BEGIN    --실행부
    
    dbms_output.put_line('HELLO PL');
END;
/ -- 생략가능


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

 

if

DECLARE
    counter INTEGER;
BEGIN
    counter := 1;
    counter := counter + 1;
    
    IF counter IS NULL THEN
    --IF counter = 2 THEN
    --IF counter > 2 THEN
    
     dbms_output.put_line('counter is null');
    
     --ELSIF 조건 THEN
     
    ELSE
        dbms_output.put_line('counter is NOT null');
    END IF;
        
END;
/

FOR

--FOR 문
DECLARE
    counter INTEGER;
    i INTEGER;  --loop형 변수
    
BEGIN
    FOR i IN 1..9 LOOP
         dbms_output.put_line('i = '|| i);
         counter := i  * 2;
         dbms_output.put_line('counter = '|| counter);
         END LOOP;
END;
/

LOOP

-- LOOP
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;
/

WHILE

-- WHILE
DECLARE
    v_count NUMBER := 0;
    v_total NUMBER := 0;
BEGIN
    WHILE v_count < 10  --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;
/

GOTO

--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 woman');
            GOTO sub_end;
         <<test2>>
            dbms_output.put_line(v_name || 'is man');
            GOTO sub_end;
         
         <<err>>
          dbms_output.put_line('err');
          GOTO sub_end;
          
          <<sub_end>>
          dbms_output.put_line('sub_end');
END;
/

 예외처리부

--예외처리부

DECLARE
    counter INTEGER;
BEGIN
    counter := 10;
    counter := counter/2;
    
    EXCEPTION WHEN OTHERS THEN 
         dbms_output.put_line('예외가 발생하였습니다');
END;
/

배열

-- varray : variable array   int varray_test
DECLARE
    TYPE varray_test IS VARRAY(3) OF INTEGER;
    myarr varray_test;
BEGIN
    myarr := varray_test(11,22,33);
    
     dbms_output.put_line('myarr(2)='|| myarr(2));  
END;
/

부서번호를 입력하면 급여의 합을 출력하는 스크립트

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

DECLARE
    v_salTotal NUMBER;
BEGIN
    SELECT SUM(salary) INTO v_salTotal
    FROM employees
    WHERE department_id = &p.deptno;
    
    dbms_output.put_line(&p_deptno || '번 부서의 급여의 합은' || TO_CHAR(v_salTotal,'$999,999,999'));
END;
/

 

ACCEPT

ACCEPT 변수 [datatype] [FORMAT] [PROMPT text] [HIDE]
① 변수    : 값을 저장하는 변수의 이름으로 존재하지 않으면 SQL *Plus가 생성한다.
② datetype : NUMBER, CHAR 또는 DATE이며, CHAR는 최대 240byte이다.
③ FORMAT   : 예를들면, A10 또는 9,999같은 형식을 명시한다.
④ PROMPT   : text는 사용자가 값을 입력하도록할 때 출력되는 문장이다.
⑤ HIDE     : 예를들면, 암호처럼 사용자가 입력되는 내용을 보이지 않게 한다.

사용자에게 값을 입력받아서 변수에 할당 할 때는 '&(앰퍼샌트)' 기호를 사용 한다.
&명령이 실행될때마다 입력값이 요구된다
🚫 주의: &는 외부에서 입력될때만 사용한다

주의 : 복습시  선언부의 변수와 ACCEPT  의 변수를 동일하게 해줘서 오류 발생 주의 바람