[Oracle] Stored Sub Program: SP

Stored Sub Program(저장 서브프로그램)

필요시 PL/SQL 호출해서 사용하고자 데이터베이스에 PL/SQL 구문을 저장해놓은 프로그램입니다. 이 프로그램은 Oracle에 저장해서 사용자간 공유하여 사용 가능하며, 메모리, 성능, 재사용성 측면에서 장점이 있습니다. 그 종류로는 SF(Stored Function), SP(Stored Prcedure), Trigger 등이 있습니다.

 

Stored Procedure

SP(저장 프로시저)는 오직 특정한 기능과 작업을 처리하고자 사용하는 서브 프로그램입니다. 쿼리문에서 호출이 안되고 오직 단독으로만 실행할 수 있습니다. 파라미터 여부에 따라 두가지로 나누어집니다.

 

1. 파라미터가 없는 프로시저

--프로시저의 선언
CREATE OR REPLACE PROCEDURE 프로시저명
IS | AS(DECLARE 대신 사용, 선언부가 없어도 반드시 명시해줘야 된다.)
  선언부
BEGIN
  실행부
EXCEPTION
  예외처리부
END [프로시저명];

-- 프로시저의 실행 : 실행 명령어
EXEC 프로시저명; 

-- 프로시저의 삭제
DROP PROCEDURE 프로시저명;

 

 

 

2. 파라미터 있는 프로시저

-- 프로시저의 선언
CREATE OR REPLACE PROCEDURE 프로시저명
[(
    --입력하거나 DEFAULT를 지정할 수 있다.
    파라미터1 [MODES] 자료형 [:= | DEFAULT 기본값], 
    파라미터2 [MODES] 자료형 [:= | DEFAULT 기본값],
    파라미터3 [MODES] 자료형 [:= | DEFAULT 기본값],
    ....
)]
IS
    선언부
BEGIN
    실행부
EXCEPTION
    예외처리부
END;

저 위에 MODES 자리에는 IN, OUT, IN OUT이 들어갑니다. IN은 생략할 때 지정되는 디폴트 값으로 프로시저가 호출되면 파라미터 값을 넣어주는 모드로, 가장 많이 사용합니다. OUT은 프로시저의 결과값을 반환해주고, IN OUT은 호출할 때 값을 입력받고 그 값을 바탕으로 실행 결과 값을 반환합니다.

파라미터의 자리수는 지정이 안되며, NOT NULL 제약 조건도 사용할 수 없습니다.

파라미터가 있는 프로시저를 실행할 때는 아래와 같은 코드를 사용합니다.

EXEC 프로시저명(파라미터값1, 파라미터값2, ....);

추가로 기본값이 지정된 파라미터라면, 위의 파라미터값은 생략 가능합니다.

 

예시를 들어보겠습니다. 먼저 파라미터가 없는 프로시저입니다.

 

--프로시저 선언
CREATE OR REPLACE PROCEDURE PRO_NOPARAM
IS
    ENO VARCHAR2(8);
    ENAME VARCHAR2(20);
BEGIN
    ENO := '8888';
    ENAME := '홍길동';
    
    INSERT INTO EMP (ENO, ENAME)
    VALUES(ENO, ENAME);
END;
/

"Procedure PRO_NOPARAM이(가) 컴파일되었습니다." 라고 뜨면 성공입니다. 해당 프로시저는 좌측 메뉴에서 "프로시저" 디렉토리에서도 확인할 수 있습니다.

 

프로시저를 실행해보고, EMP table에서 ENO가 8888인 사람을 확인하면 됩니다.

--프로시저 실행
EXEC PRO_NOPARAM;

SELECT *
    FROM EMP
    WHERE ENO = '8888';

 

이번에는 파라미터가 있는 프로시저입니다. 생성과 선언부는 위와 비슷합니다.  지금보면 DNO, DNAME, LOC까지는 값을 받을 수 있도록 선언하고, DIRECTOR에는 디폴트 값으로 1111을 넣었습니다. 따라서 파라미터 입력할 때 DIRECTOR 자리는 생략해도 1111이 들어가게됩니다.

CREATE OR REPLACE PROCEDURE P_NEW_DEPT
(
    DNO IN VARCHAR2,
    DNAME IN VARCHAR2,
    LOC IN VARCHAR2,
--    DIRECTOR IN VARCHAR2 := '1111' --안 넣어도 자동으로 입력됨.
    DIRECTOR IN VARCHAR2 DEFAULT '1111' --이렇게도 가능하다.
)
IS 

BEGIN
    INSERT INTO DEPT
    VALUES (
        DNO,
        DNAME,
        LOC, --3개만 이제 넣어줄 때 해도 1111이 DIRECTOR에 들어감.
        DIRECTOR
    );
END;
/

 

프로시저를 호출해보겠습니다. 파라미터가 있는 프로시저는 호출 시에 파라미터 값을 입력하여 전달해야 합니다. 디폴트값이 있는 영역은 제외할 수 있습니다.

--프로시저 호출 시 파라미터 전달
EXEC P_NEW_DEPT('99', '배포', '수원');
EXEC P_NEW_DEPT('98', '테스트', '대전', '2001');

SELECT * FROM DEPT;

파라미터에서 생략한 배포 부서는 DIRECTOR가 디폴트 값인 1111이 입력된 점을 확인할 수 있습니다.