저장 프로시저

PL/SQL 저장 프로시저는 반환되는 값 없이 특정 처리만을 수행하는 서브프로그램이다. 저장함수와 마찬가지로 저장 프로시저도 고유한 이름을 가지고 데이터베이스에 저장되며 이름을 식별자로 사용하여 반복적으로 재사용될 수 있다.

저장 프로시저는 입력값으로 매개변수를 가질 수 있다. 저장 프로시저는 배치 프로그램이나 오라클 스케줄러와 같이 데이터를 처리하는 프로그램에 유용하게 사용된다.

 

PL/SQL 저장 프로시저를 생성하는 기본 구조는 이러하다.

CREATE [ OR REPLACE ] PROCEDURE 프로시저명 [ ( 매개변수목록 ) ]
IS	-- IS 대신 AS를 사용해도 됨
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END;

앞의 함수 작성 형태와 비교해서 차이점을 꼽자면 함수는 반환되는 값의 데이터 타입이 있어야 하지만, 프로시저에는 없다는 것과 함수에서는 반드시 존재해야 하는 실행부의 RETURN 반환값 문이 프로시저에는 없다는 것이다.

 

프로시저의 실행부는 함수와는 달리 'RETURN 반환값'을 가지지는 않지만 반환값 없이 즉시 호출한 프로그램으로 복귀하는 RETURN문은 가질 수 있다. 프로시저의 끝까지 실행한 후 프로시저를 종료하는 경우라면 RETURN문은 필요하지 않다.

CREATE OR REPLACE PROCEDURE raise_bonus(a_empno NUMBER, a_amt NUMBER)
	-- 테이블 bonus에 사원의 커미션 값을 인상하는 프로시저
IS
	v_ename emp.ename%TYPE;
BEGIN
	-- 사원의 이름을 얻는다.
    BEGIN
    	SELECT ename
          INTO v_ename
          FROM emp
         WHERE empno = a_empno;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       -- 사원이 존재하지 않을 경우는 수행을 중단하고 복귀한다. 반환값이 없는 것이 함수와 다르다.
       RETURN;
    END;
    
    -- 보너스를 인상
    IF a_amt IS NOT NULL
    THEN
    	MERGE INTO bonus
        USING DUAL
           ON (bonus.ename = v_ename)
         WHEN MATCHED THEN 	-- 기존 보너스가 있는 경우는 인상할 값을 더한다.
      	   UPDATE SET comm = comm + a_amt
         WHEN NOT MATCHED THEN -- 기존 보너스가 없는 경우 새 로우를 추가
           INSERT (ename, comm)
           VALUES (v_ename, a_amt);
    END IF;
 EMD;

프로시저의 매개변수

프로시저의 매개변수 사용은 함수와 다르지 않다, 매개변수가 없는 경우 매개변수를 감싸는 괄호까지 생략해야 하는 것도 동일하다.