OracleSQL - PL/SQL 프로시저(Procedure)

PL/SQL 에서 가장 대표적인 구조인 스토어드(Stored) 프로시저는 개발자가 자주 작성해야 하는 업무의 흐름을 미리 작성하여 데이터 베이스 내에 저장해 두었다가 필요할 때 마다 호출하여 실행할 수 있도록 처리해주는 구문이다. 

 

기본적인 형식 및 구조는 다음과 같다.

 

CREATE [OR REPLACE] PROCEDURE 프로시저명
[ ( 매개변수 IN 데이터타입            -- 입력 파라미터 (필수 X)
  , 매개변수 OUT 데이터타입 )       -- 출력 파라미터 (필수 X)
  , 매개변수 INOUT 데이터타입      -- 입출력 파라미터 (필수 X)
) ]
IS
    -- 선언 구문
    [주요 변수 선언;]
BEGIN
    -- 실행 구문
    ...
    [EXCEPTION]
         -- 예외 처리 구문
END;

-- ※ [] 는 생략이 가능

FUNCTION 과 비교했을 때 다른 점은 [RETURN 자료형]과 [RETURN 문] 부분이 존재하지 않으며, 프로시저 실행 시 넘겨주게 되는 매개변수의 종류가 IN, OUT, INOUT 으로 구분된다.

 

프로시저를 만들었다면 다음과 같이 실행(호출)해준다. 

 

 EXEC[UTE] 프로시저명 [ ( 인수1, 인수2, ...) ];

 

예제

프로시저의 예제를 위해 다음과 같이 테이블들을 추가한다.

 

 -- 테이블 생성 (TBL_STUDENTS)

CREATE TABLE TBL_STUDENTS
( ID       VARCHAR2(10)
, NAME  VARCHAR2(40)
, TEL      VARCHAR2(30)
, ADDR   VARCHAR2(100)
);
--==>> Table TBL_STUDENTS이(가) 생성되었습니다.


-- 테이블 생성 (TBL_IDPW)

CREATE TABLE TBL_IDPW
( ID       VARCHAR2(10)
, PW      VARCHAR2(20)
, CONSTRAINT IDPW_ID_PK PRIMARY KEY(ID)
);
--==>> Table TBL_IDPW이(가) 생성되었습니다.

학생이 계정을 만든다고 했을때 학생 정보와 아이디 패스워드가 들어가게 될것이다. 하지만 테이블이 서로 나뉘어져 있기 때문에 다음과 같이 여러번 입력이 되어야 할것이다.

 

-- 두 테이블 데이터 입력

INSERT INTO TBL_STUDENTS(ID, NAME, TEL, ADDR)
VALUES('superman', '김종호', '010-1111-1111', '제주도 서귀포시');
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_IDPW(ID, PW)
VALUES('superman', 'java006$');
--==>> 1 행 이(가) 삽입되었습니다.

하지만 프로시저를 사용한다면 간편하게 모든 정보를 입력받을 수 있다. 

 

-- 프로시저 PRC_STUDENTS_INSERT 생성

CREATE OR REPLACE PROCEDURE PRC_STUDENTS_INSERT
-- 매개변수 선언
( V_ID        IN  TBL_IDPW.ID%TYPE
, V_PW       IN  TBL_IDPW.PW%TYPE
, V_NAME   IN  TBL_STUDENTS.NAME%TYPE
, V_TEL       IN  TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN  TBL_STUDENTS.ADDR%TYPE
) 
IS
BEGIN
    -- 실행 구문
    INSERT INTO TBL_IDPW (ID, PW)
    VALUES (V_ID, V_PW);

    INSERT INTO TBL_STUDENTS (ID, NAME, TEL, ADDR)
    VALUES (V_ID, V_NAME, V_TEL, V_ADDR);

    COMMIT;
END;
--==>> Procedure PRC_STUDENTS_INSERT이(가) 컴파일되었습니다.

이제 이 프로시저만 실행시켜 준다면 INSERT를 여러번 할 필요 없이 올바르게 학생 정보를 넣을 수 있게 된다. 

 

EXEC PRC_STUDENTS_INSERT('batman', 'java006$', '김일웅', '010-2222-2222', '서울 마포구');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

 

예외 처리

프로시저는 실행부 내에서 예외 처리를 할 수 있다. 예외 처리를 통해 잘못된 실행이 감지되면 DB의 변동사항 적용시키지 않고 이전 상태로 돌리는 할 수 있다. 

 

기본적인 사용 방법은 다음과 같다.

CREATE [OR REPLACE] PROCEDURE 프로시저명
[ ( 매개변수 선언 부분 ) ]
IS
    [ 선언 부분 ]
    ...

    USER_DEFINE_ERROR    EXCEPTION;    -- 사용자 정의 예외를 위한 변수 선언

BEGIN
    [ 실행 부분 ]
    ...

    IF (사용자가 정의할 예외 조건)
         THEN RAISE USER_DEFINE_ERROR;  -- USER_DEFINE_ERROR 라는 예외를 발생 (THROW)
    END IF;

    -- 예외 처리 부분
    EXCEPTION
        WHEN USER_DEFINE_ERROR           -- 사용자 정의 예외가 발생한 경우
               THEN RAISE_APPLICATION_ERROR(-20001, '[예외처리 메시지]');
                        -- 에러 메시지를 띄우는 함수 실행
                         -- (-20000까지는 오라클에서 사용하는 에러코드이므로 그 이후 에러 코드를 사용)
                        ROLLBACK;     -- 이전상태로 롤백  
        WHEN OTHERS                            -- 이외의 예외가 발생한 경우
               THEN ROLLBACK;      -- 이전상태로 롤백

END;