[Oracle] 프로시저 / ORA-06553 - 호출 시 인수의 갯수나 유형이 잘못되었습니다 해결법

 

• 함수와의 가장 큰 차이점

함수는 결과 값을 반환하지만

프로시저는 특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 프로그램이다.

 

 

• 언제 사용?

테이블에서 데이터를 추출해 입맛에 맞게 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 이용한다.

 

 

 

• 프로시저 구문 예시

CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC (AV_EMP_NO     IN VARCHAR2,
                                             AV_EMP_NM     IN VARCHAR2,
                                             AV_AGE   IN NUMBER)
IS
   V_AGE      EMP_INFO.AGE%TYPE;
   V_GRD_CD   EMP_INFO.GRD_CD%TYPE;            -- EMP_INFO라는 테이블의 컬럼의 타입을 가져온다
   V_CNT      NUMBER := 0;                     -- 이렇게 새 변수에 값을 할당할 수도 있다.
BEGIN
   -- 1, 동일한 데이터가 있는지 체크
   SELECT COUNT (*)
     INTO V_CNT
     FROM EMP_INFO
    WHERE EMP_NO = AV_EMP_NO;


   -- 없으면 INSERT
   IF V_CNT = 0
   THEN
      INSERT INTO EMP_INFO (EMP_NO, EMP_NM, AGE)
           VALUES (AV_EMP_NO, AV_EMP_NM, AV_AGE);
   
    -- 있으면 UPDATE        
   ELSE                                                         
      UPDATE EMP_INFO
         SET EMP_NO = AV_EMP_NO, EMP_NM = AV_EMP_NM, DUTY_DEG = AV_AGE;
   END IF;

   DBMS_OUTPUT.PUT_LINE (' INSERT 혹은 UPDATE 수 : ' || SQL%ROWCOUNT);

   COMMIT;
END;

 

 

 

• 프로시저 실행법

프로시저는 반환 값이 없으므로 함수처럼 SELECT 절에는 사용할 수 없고 다음과 같이 실행해야 한다.

EXEC MY_NEW_JOB_PROC ('0000', '홍길동', 15)

 

 

 

• OUT 매개변수

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재 여부라고 했다. 그런데 프로시저에서도 값을 반환하는 방법이 있는데 바로 OUT 매개변수를 통해서 실현할 수 있다.

 

프로시저 생성 시 OUT 매개변수는 반드시 OUT 키워드를 명시해야 한다. 

CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC (AV_EMP_NO     IN VARCHAR2,
                                             AV_EMP_NM     IN VARCHAR2,
                                             AV_AGE   IN NUMBER,
                                             AV_TEST	   OUT VARCHAR2)
IS
  
  // 위와 동일..
  
   COMMIT;
END;

 

 

이제 프로시저를 실행하고 OUT 매개변수 값을 참조해야 하는데, 이때 별도의 변수를 선언해서 매개변수로 전달한 뒤 값을 참조해야 한다.

 

 

필자는 이렇게 안하고, 아래처럼 호출했다가 

EXEC MY_NEW_JOB_PROC ('0000', '홍길동', 15, '')

 

이런 에러가 계속 떴다.

ORA-06553: PLS-306: 'MY_NEW_JOB_PROC' 호출 시 인수의 갯수나 유형이 잘못되었습니다

 

 

올바른 방법은, OUT 매개변수를 전달하기 위해 익명 블록을 만들어서 호출하는 것이다.

DECLARE
    test VARCHAR2;
BEGIN
    MY_NEW_JOB_PROC('0000', '홍길동', 15, test) -- 여기서 EXEC를 붙이지 않는다!
END;

 

주의할 점은 익명 블록에서 프로시저를 실행하면 EXEC를 붙이지 않는다. 

다른 함수나 프로시저, 패키지에서 이 프로시저를 실행할 경우도 마찬가지이다. 

 

 

여기서 신기한 점은, JAVA단에서 iBatis를 이용해 프로시저를 호출할 때

 

AGE 컬럼이 테이블에서타입이 NUMBER형임에도 불구하고,

자바단에서 parameter를 넘길 때 jdbcType="INTEGER" javaType="java.lang.Integer" 가 아니라,

아래와 같이 String형으로 넘겨도 프로시저 호출 시에 잘 들어간다는 점이였다. 

이게 왜 그런지는 아직 못알아 냈다.. (아시는 분 댓글 부탁드립니다)

<parameterMap id="procMap" class="map"> 
		<parameter property="EMP_NM" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
		<parameter property="EMP_NO" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
		<parameter property="AGE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
		<parameter property="TEST" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
</parameterMap>


<procedure id="testProcedure" parameterMap="procMap">
		{CALL MY_NEW_JOB_PROC(?, ?, ?, ?)}
</procedure>