프로시저 호출하기(오라클, SqlServer)

mybatis의 sql mapping은 <select>, <insert>, <update>, <delete> 태그를 이용하여 해당 sql을 기재한다. 이 부분은 프로시저에서도 마찬가지인데, 프로시저 내용에 insert를 하든 delete를 하든 어째됐든 최종적으로 결과를 리턴해야 한다면 <select>태그를 이용하면 될 것이다.

오라클과 sqlserver의 프로시저들은 insert, update, delete는 기본적으로 동일 프로토타입으로 진행가능하기 때문에 DAO 클래스를 일관되게 사용할 수 있으나, 문제는 select가 되겠다.

1.SqlServer의 예 (2008에서 테스트했음)

sqlserver 의 select용 프로시저

CREATE PROC sampleProc

           @xxxfield int

           ,@yyyfield int

AS

BEGIN

         SELECT * FROM sampleTable
         WHERE x=@xxxfield AND y=@yyyfield;
END

위의 프로시저를 호출하는 mybatis mapper (namespace는 sample 이라고 해두자)

<mapper namespace="sample">

<select id="spSelect" statementType="CALLABLE" resultType="sampleVO">

     {call

            sampleProc(

                       #{xxxfield, mode=IN}

                       ,#{yyyfield, mode=IN}

            )

       }

</select>

..........

위의 mapper를 호출하는 java source

SampleVO vo = new SampleVO();

vo.setXxxfield(12);

vo.setYyyfield(20);

List<SampleVO> result = sqlSession.selectList("sample.spSelect",vo);

2.Oracle의 예 (10R2 : 10.2.0.1 에서 테스트했음)

oracle 의 select용 프로시저

CREATE PROC sampleProc

           xxxfield IN number

           ,yyyfield IN number

           ,vcursor OUT sys_refcursor 

IS

BEGIN

         OPEN vcursor FOR

         SELECT * FROM sampleTable
         WHERE x=xxxfield AND y=yyyfield;
END sampleProc;

위의 프로시저를 호출하는 mybatis mapper (namespace는 sample 이라고 해두자)

<mapper namespace="sample">

<resultMap id="sampleVOMap" type="SampleVo">
    <result property="idx" column="idx"/>
    <result property="intField" column="intfield"/>
    <result property="nvarcharField" column="nvarcharfield"/>
    <result property="numField" column="numfield"/>
</resultMap>

<!-- 여러건 취득 -->

<select id="spSelect" statementType="CALLABLE" resultType="sampleVO">

     {call

            sampleProc(

                       #{xxxfield, mode=IN, }

                       ,#{yyyfield, mode=IN}

                       ,#{vcursor, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=sampleVOMap}

            )

       }

</select>

..........

위의 mapper를 호출하는 java source

Map<String,Object> map = new HashMap<String,Object>();

map.put(xxxfield,new Integer(12));

map.put(yyyfield,new Integer(20));

sqlSession.selectList("sample.spSelect",map);

List<SampleVO> result = (List<SampleVO>)map.get("vcursor");

이용 편의성에서는 sqlserver쪽이 훨씬 나은 것 같다. sqlserver쪽은 resultset을 리턴할 수 있다고 하는데, 오라클에서는 안된단다. 그래서 저렇게 cursor를 사용해서 쓴다고 한다. 뿐만 아니라 resultMap도 정의해야 하고, 파라메터를 VO클래스 그대로 쓰기도 곤란하다.

소스에서 보다시피 IN 파라메터에 바인딩하도록 HashMap을 이용하여 인자로서 던졌는데, 인자로 사용한 인스턴스에서 결과를 빼내고 있다.

즉, mapper에 쓰여진 프로시저의 인자들을 IN,OUT에 관계없이 모두 Java단에서 넘긴 인수용 VO클래스에 매핑하고 있는 것이다...요거이 당연한 예긴데...테스트할 때 안돌아간다고 짜증만 나고 요건 생각이 안나더라...

한마디로, 여러모로 번거롭다.

그럼 SELECT .. INTO .. 형태의 SQL에서는 어떨까. 어떻긴..비슷하것지...

프로시저 소스

CREATE PROC sampleProc2

           vid IN sampleTable2.id%TYPE

           ,retval OUT sampleTable2.z%TYPE

IS

BEGIN

         SELECT z INTO retval FROM sampleTable2;

         /*건이 없으면 no data found 라면서  에러난다. 요거이 뽀인뜨 되것다. 따라서 먼저 count를 구하든가  EXCEPTION 처리를 추가하든가 한다.*/ 

         /*참고로 건이 있으면 해당 컬럼에 데이터가 null이더라도 에러는 나지 않는다.*/

         WHERE id=vid

EXCEPTION

       WHEN NO_DATA_FOUND THEN
              NULL;
END sampleProc;

mapper 소스

<select id="spSelect2" statementType="CALLABLE" resultType="sample2VO">

     {call

            sampleProc2(

                       #{vid, mode=IN}

                       ,#{retval, mode=OUT, jdbcType=VARCHAR}

            )

       }

<select>

java 소스

Sample2VO vo = new Sample2VO();
vo.setVid(12);

sqlSession.selectOne("sample.spSelect2",vo);

System.out.println(vo.getRetval());

요렇게 되것다.