프로시저

프로시저 : 일련의 쿼리를 하나의 함수처럼 실행하도록 하는 쿼리 집합

 - 네트워크 소요 시간을 줄여줌

 - 하나의 요청으로 여러 sql 실행이 가능(네트워크 부하를 줄일 수 있음)

 - 이식성이 좋다

 

*  PL SQL 

블록 구조로 구성되어있고 절차적 프로그래밍 언어

oracle 프로그래밍 언어 + 절차적 언어  = pl sql

 

구조:  언부 + 동작부( 로직처리 + 에러처리 ) 

선언부 : 데이터 형태 선언 및 변수 선언

동작부: : begin ~ end 사이에 쿼리들 집합

Declare (선택사항)

  변수 선언

Begin

  (PL/SQL 블럭)

 Exception

End;

 

**  실제 사용할 때는 다음과 같다.

1) 선언부
CREATE OR REPLACE PROCEDURE TEST_PROCEDURE
(
    PV_ID IN NUMBER
  , PV_NEW_ID IN NUMBER
  , PV_NEW_ITST_NM IN VARCHAR
  , RV_MSG IN OUT VARCHAR
) IS

 CNT1 NUMBER := 0;
 CNT2 NUMBER := 0;
 CNT3 NUMBER := 0;

2) 데이터 동작부
BEGIN
--1. 로직 제어부
	SELECT COUNT(*) INTO CNT1  FROM TEST1  WHERE ID = PV_ID;
  	SELECT COUNT(*) INTO CNT2  FROM TEST2  WHERE ID = PV_ID;
  	SELECT COUNT(*) INTO CNT3  FROM TEST3  WHERE ID = PV_ID;
  	SELECT COUNT(*) INTO CNT4  FROM TEST4  WHERE ID = PV_ID;
  
--) 코드 블록 1, 2
 IF CNT1 > 0 THEN 
 	INSERT INTO TEST1
    VALUES('ID1', '23', 333);
 ELSE
    DBMS_OUTPUT.PUT_LINE('TEST1 DATA ERROR');
    RV_MSG := 'ERROR1';
  END IF;
 
 IF CNT2 > 0 THEN 
 	INSERT INTO TEST2
    VALUES('ID2', '34', 557);
    
     UPDATE TEST2
	     SET USE_YN = 'N'
	       , UDT_DT = SYSDATE
	   WHERE ID = PV_ID;
    
 ELSE
    DBMS_OUTPUT.PUT_LINE('TEST2 DATA ERROR');
    RV_MSG := 'ERROR2';
  END IF;
  
  IF RV_MSG IS NULL THEN RV_MSG := 'OK'; END IF;

-- 커밋 
  COMMIT;
  
--2. 에러 처리부
  EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, SQLERRM);
END;

 

1) 선언부

Create or Replace 프로시저명(procedureName) {

   data1 IN VARCHAR(20)

  , num1 IN NUMBER

} IS

 

위와 같은 IS를 쓰는 선언부가 올 경우에는 DECLARE를 쓰면 안됨

 - IS: 프로시저나 펑션에 사용되는 것으로 declare 역할까지 수행함

 

2) 데이터 동작부

코드 블럭이 들어가는 곳으로, 각 기능을 수행하는 쿼리들의 집합

제어문, 반복문 등이 사용 가능하며 데이터 제어가 가능하다.

 ** 알아둘 것: 변수에 값을 대입할 때는 ':=' 를 쓰면 됨.(JAVA의 '='와 동일)

 

COMMIT :  데이터를 영구적으로 저장하고자 할 때 사용(위의 쿼리를 적용하고자 할 때 사용함)

 

3) 에러처리부

에러가 발생한 경우 에러 발생 방지를 위해 EXCEPTION을 작성하여 예외처리를 함

예문: 아래와 같은 형식으로 사용

EXCEPTION
WHEN EXCEPTION EXCEPTION명 THEN
BEGIN 
   .. (메시지 처리/실행함수 처리 등)
END;
WHEB OTHERS THEN (조건)
BEGIN 
   ..
END;

보통 '선언 - 호출 - 실행' 순으로 예외 처리를 하지만, 상단에 있는 예시처럼, 

EXCEPTION이 날 때, ROLLBACK을 하여 함수를 실행시키는 일을 나타내면서 처리를 할 수 있다.

 

 

 

 

 

 

 

출처 참고

https://goddaehee.tistory.com/99

[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

https://hoon93.tistory.com/38

오라클 PL/SQL과 블록(Block) 구조 및 특징

http://www.gurubee.net/oracle/plsql

Oracle PL/SQL 강좌