[MYBATIS] 동적 프로시저 선언 및 결과

 

프로시저를 동적으로 선언하고 결과값의 컬럼값과 값이 동적인 경우

MYBATIS에서 어떻게 해야 하는지 기록을 목적

 

1. Service에서 선언과 호출

//선언부
        HashMap<String,Object> tx02Map = new  HashMap<>();
        tx02Map.put("procedureName",pce);
        tx02Map.put("slipNo","47672");
        List<HashMap<String,Object>> dataList = restDao.getSourceData(tx02Map);

 

2.  Mapper(DAO) 호출

List<HashMap<String, Object>> getSourceData(HashMap<String, Object> pce);

 

3. Mybatis 선언부

<select id="getSourceData" statementType="CALLABLE" parameterType="hashmap" resultMap="dynamicResultMap">

          {
               CALL ${procedureName}
                    (
                       #{slipNo, 			mode=IN,  jdbcType=VARCHAR, javaType=string}
                      ,#{result,            mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=dynamicResultMap}
                    )
           }

     </select>
    <resultMap id="dynamicResultMap" type="hashmap">
        <result column="*" property="*" />
    </resultMap>

4. Procedure (PL)

CREATE OR REPLACE PROCEDURE TEST(
    v_slipNo IN VARCHAR2,
    resultArr OUT SYS_REFCURSOR)
    IS
 
    --입력부
    slipNo    VARCHAR2(10)    := '';
    --RISE OUT_OF_ERROR
    e_JOB_NAME    VARCHAR(50)    := 'TEST';
    e_JOB_SOURCE  VARCHAR(15)    := 'OUT_OF_ERROR';
    e_JOB_COMMENT VARCHAR(200)   := '';
    OUT_OF_ERROR EXCEPTION;
BEGIN
    -- 변수 선언
    slipNo := v_slipNo;
    BEGIN
        OPEN resultArr FOR
            SELECT 	AA	    AS	AA	            /*	회사코드	*/
                 ,	BB	    AS	BB	            /*	회계코드	*/
                 
            FROM TX290T
            WHERE 1 = 1
        ;
    END;

/* EXCEPTION LINE */
EXCEPTION
    WHEN OUT_OF_ERROR THEN
        ROLLBACK;
        BEGIN
            INSERT INTO WORK_REST_STATE (JOB_NAME, JOB_SOURCE, CREATE_USER, CREATE_DT, JOB_COMMENT, JOB_STATE)
            VALUES ( e_JOB_NAME
                   , e_JOB_SOURCE
                   , 'SYSTEM'
                   , SYSDATE
                   , e_JOB_COMMENT
                   , '1');
            COMMIT;
--             r_result := e_JOB_COMMENT;
        END;
    WHEN OTHERS THEN
        ROLLBACK;
        e_JOB_SOURCE  := 'OTHERS';
        e_JOB_COMMENT := 'OTHERS Error : ' ||  SUBSTR(SQLERRM, 1, 200);
        BEGIN
            INSERT INTO WORK_XXXX_STATE (JOB_NAME, JOB_SOURCE, CREATE_USER, CREATE_DT, JOB_COMMENT, JOB_STATE)
            VALUES ( e_JOB_NAME
                   , e_JOB_SOURCE
                   , 'SYSTEM'
                   , SYSDATE
                   , e_JOB_COMMENT
                   , '1');
            COMMIT;
        END;
--         r_result := e_JOB_COMMENT;
        RETURN ;
/*
    EXCEPTION LINE
 */
END;

 

5. 결과

tx02Map에 AA와 BB가 들어간 것을 확인