spring mybatis stored procedure ( oracle )

spring 3.1.1, mybatis 3.2.7, spring-mybatis 1.2.2, oracle 11g r2 환경하에서
spring task로 oracle stored procedure 실행 후 결과를 받는 실례 중 주요코드입니다.

 

1. sched.java
   

   @Scheduled(cron = "${cron.SCHED001_FILTER_INSERT}")
   public void  cronSched001FilterInsert() throws Exception { service.execProcdure("SCHED001_FILTER_INSERT"); }

2. service.java

   @Async
   public void execProcdure(String procNm) throws Exception {
   		HashMap<string,object> commandMap = new HashMap<string,object> ();			
   		commandMap.put("SCHED_ID", procNm);
   		dao.execJobProcedure("com.dbility.sched.mapper."+procNm,commandMap) ;
   		LOG.info("SCHED_LRU_MSG : {}", commandMap.get("result"));
   }

3. dao.java

   @Resource(name="sqlSessionTemplate")
   private SqlSessionTemplate sqlSession;
   
   public void execJobProcedure(String sqlId,HashMap<string,object> commandMap) throws SQLException {
   		sqlSession.selectOne(sqlId,commandMap);
   }

4. mapper.xml
   parameterType은 생략 가능하며,mode=OUT설정된 parameter에 procedure 결과가 저장됩니다.
   

   <select id="SCHED001_FILTER_INSERT" parameterType="java.util.HashMap" statementType="CALLABLE">
           { call DBILITYDBA.SCHED001_FILTER_INSERT(#{v_date,mode=IN,jdbcType=VARCHAR},#{v_shift,mode=IN,jdbcType=VARCHAR},#{result,mode=OUT,jdbcType=VARCHAR})}
   </select>

5. procedure.prc

   CREATE OR REPLACE PROCEDURE DBILITYDBA.SCHED001_FILTER_INSERT(V_DATE IN VARCHAR2,V_SHIFT IN VARCHAR2,V_MSG OUT VARCHAR2) IS
   START_TIME  NUMBER;
   END_TIME    NUMBER;
   /******************************************************************************
      NAME:       SCHED001_FILTER_INSERT
      PURPOSE:    예제
   
      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        2016/09/24   ROOKIE       1. Created this procedure.
   ******************************************************************************/
   BEGIN
   
       START_TIME := DBMS_UTILITY.GET_TIME;
   	
   	/*
   	SQL CODE
   	*/
      
       END_TIME := DBMS_UTILITY.GET_TIME;
              
       V_MSG := 'SCHED001_FILTER_INSERT ELAPSED: '||TO_CHAR( (END_TIME-START_TIME)/100,'FM9,990.990')||' ,Rows affected : '||TO_CHAR(SQL%ROWCOUNT,'FM999,999,990')';
          
       COMMIT;
          
       EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
        WHEN OTHERS THEN
          ROLLBACK;
          --DBMS_OUTPUT.PUT_LINE('SCHED001_FILTER_INSERT ERROR: '||SQLERRM);
          V_MSG := 'SCHED001_FILTER_INSERT ERROR: '||SQLERRM;
          RAISE;
   END SCHED001_FILTER_INSERT;
   /