[PL/SQL] 프로시저(Procedure)란?

프로시저란?

• 특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램
• 데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템이 저장한 것
• 테이블에서 데이터를 추출, 조작하고 결과를 다른 테이블에 다시 저장하거나 갱신하는 처리를 할 때 프로시저를 사용한다.

장점

• 하나의 요청으로 여러 SQL문을 실행 가능
• 네트워크 소요 시간을 줄여 성능개선이 가능
• 여러 어플리케이션과 공유 가능
• 기능 변경 편리 → 특정 기능을 변경할 때 프로시저만 변경하면 된다.

단점

• 문자나 숫자열 연산에 사용하면 java보다 느린 성능을 보일 수 있다.
• 프로시저가 앱의 어디에 사용되는 확인이 어렵다 → 유지보수가 어려움

프로시저 생성

CREATE OR REPLACE PROCEDURE 프로시져명 
(파라미터1 IN | OUT | IN OUT
,파라미터2 IN | OUT | IN OUT...);

IS[AS]
변수, 상수 등을 선언

BEGIN
로직을 실행할 쿼리문

[EXCEPTION 예외처리]

END 프로시져 명;

• IN은 입력(입력 매개변수), OUT은 출력(출력 매개변수), IN OUT은 입출력을 동시에 한다.
• 기본값은 IN이다.
• OUT파라미터는 프로시저에서 로직 수행후, 해당 매개변수에 값을 할당해서 프로시저 호출부분에서 이 값을 참조할 수 있다. → 에러코드, 에러메세지를 전달하는 목적으로 사용

프로시저 생성 예시

CREATE OR REPLACE PROCEDURE p_transfer_account
   (sid  IN  CHAR
  ,rid in char
   ,money IN  number
  , error_cd in out char
  , error_msg OUT VARCHAR2
   )
IS
  vbalance NUMBER(12);
  
BEGIN
   DBMS_OUTPUT.PUT_LINE(error_cd);
   INSERT INTO GEORAE_LOG VALUES
       (
       		seq_georae.nextval
         ,sid
         ,rid
         ,money
         ,'T'
         ,'192.168.1.3'
         ,SYSDATE
       );
	 SAVEPOINT t1;
   SELECT balance  INTO vbalance
   FROM bank_account
   WHERE account_no = sid;

   if vbalance < money THEN
     error_cd :='-1';
     error_msg := '잔액이 부족합니다.';
     return;
   end if;

  update bank_account set
   balance=balance-money
  where account_no = sid;
  INSERT INTO TRANSACTION_DETAIL VALUES
       (
             SEQ_TRANSACTION.NEXTVAL
         ,sid
           ,rid
         ,money
         ,'O'
         ,SYSDATE
       );
  update bank_account set
  balance=balance+money
  where account_no = rid;
   INSERT INTO TRANSACTION_DETAIL VALUES
       (
             SEQ_TRANSACTION.NEXTVAL
         ,sid
         ,rid
         ,money
         ,'I'
         ,SYSDATE
       );
  commit;
     error_cd :='0';
     error_msg := '계좌이체를 완료합합니다.';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK TO t1;
    COMMIT;
    error_cd := -2;
    error_msg := '해당계좌가 존재하지 않습니다.';
  WHEN TOO_MANY_ROWS THEN
    ROLLBACK TO t1;
    COMMIT;
    error_cd := -3;
    error_msg := '중복계좌가 존재합니다.';
  WHEN OTHERS THEN
    ROLLBACK TO t1;
    COMMIT;
    error_cd := -1;
    error_msg := '기타에러가 발생했습니다.';
END;