[오라클] 저장 프로시저(Stored Procedure) 사용 가이드

저장 프로시저(Stored Procedure) 사용 가이드

저장 프로시저란?

• 특정 처리 작업을 수행하는데 사용하는 저장 서브프로그램
• 용도에 따라 파라미터를 사용할 수도 있고, 사용하지 않을 수도 있다.

1. 파라미터를 사용하지 않는 프로시저

프로시저 생성하기

• 입력 데이터가 필요하지 않을 경우, 파라미터를 사용하지 않는 프로시저를 사용한다.
• CREATE [OR REPLACE] PROCEDURE를 사용하여 생성할 수 있다.
• 프로시저는 선언부, 실행부, 예외 처리부로 구성된다.

CREATE [OR REPLACE] PROCEDURE 프로시저 이름
IS | AS
    선언부
BEGIN
    실행부
EXCEPTION
    예외 처리부
END [프로시저 이름];

예시

CREATE OR REPLACE PROCEDURE pro_noparam
IS
   V_EMPNO NUMBER(4) := 7788;
   V_ENAME VARCHAR2(10);
BEGIN
   V_ENAME := 'SCOTT';
   DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
   DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;
/

프로시저 실행하기

• SQL*PLUS에서 실행: EXECUTE 프로시저 이름;
• PL/SQL 블록에서 실행:

BEGIN
   pro_noparam;
END;
/

프로시저 내용 확인하기

• USER_SOURCE 데이터 사전에서 조회

SELECT TEXT
  FROM USER_SOURCE
 WHERE NAME = 'PRO_NOPARAM';

프로시저 삭제하기

• DROP PROCEDURE 명령어로 삭제

DROP PROCEDURE PRO_NOPARAM;

2. 파라미터를 사용하는 프로시저

파라미터 작성 기본 형식

CREATE [OR REPLACE] PROCEDURE 프로시저 이름
[(파라미터 이름1 [modes] 자료형 [ := | DEFAULT 기본값],
  파라미터 이름2 [modes] 자료형 [ := | DEFAULT 기본값],
  ...
  파라미터 이름N [modes] 자료형 [ := | DEFAULT 기본값])]
IS | AS
    선언부
BEGIN
    실행부
EXCEPTION
    예외 처리부
END [프로시저 이름];

파라미터 모드

• IN: 값을 입력받는다.
• OUT: 값을 반환한다.
• IN OUT: 값을 입력받고 반환한다.

IN 모드 파라미터 예시

CREATE OR REPLACE PROCEDURE pro_param_in
(
   param1 IN NUMBER,
   param2 NUMBER,
   param3 NUMBER := 3,
   param4 NUMBER DEFAULT 4
)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('param1 : ' || param1);
   DBMS_OUTPUT.PUT_LINE('param2 : ' || param2);
   DBMS_OUTPUT.PUT_LINE('param3 : ' || param3);
   DBMS_OUTPUT.PUT_LINE('param4 : ' || param4);
END;
/

OUT 모드 파라미터 예시

CREATE OR REPLACE PROCEDURE pro_param_out
(
   in_empno IN EMP.EMPNO%TYPE,
   out_ename OUT EMP.ENAME%TYPE,
   out_sal OUT EMP.SAL%TYPE
)
IS
BEGIN
   SELECT ENAME, SAL INTO out_ename, out_sal
     FROM EMP
    WHERE EMPNO = in_empno;
END pro_param_out;
/

IN OUT 모드 파라미터 예시

CREATE OR REPLACE PROCEDURE pro_param_inout
(
   inout_no IN OUT NUMBER
)
IS
BEGIN
   inout_no := inout_no * 2;
END pro_param_inout;
/

프로시저 오류 정보 확인하기

• 생성할 때 발생하는 오류 확인: SHOW ERRORS;

SHOW ERR PROCEDURE pro_err;

• USER_ERRORS 데이터 사전 조회

SELECT *
  FROM USER_ERRORS
 WHERE NAME = 'PRO_ERR';

위 가이드를 통해 저장 프로시저의 생성, 실행, 확인 및 삭제 방법을 이해할 수 있습니다. 프로시저를 생성하고 관리하는 데 도움이 되길 바랍니다.