mybatis, oracle procedure(오라클 커서)를 통한 데이터 조회

오라클 프로시져를 호출/커서를 통해 직접 결과를 서 직접 받는 예제

■ test_proc Procedure

CREATE OR REPLACE PROCEDURE test_proc (
    p_age       IN NUMBER,
    p_rc        OUT SYS_REFCURSOR )
IS

BEGIN
    OPEN p_rc FOR

        WITH BASE AS (
                SELECT 1 AS AGE, '김유신' AS NAME FROM DUAL    UNION ALL
                SELECT 2 AS AGE, '장유신' AS NAME FROM DUAL    UNION ALL
                SELECT 3 AS AGE, '최유신' AS NAME FROM DUAL    UNION ALL
                SELECT 4 AS AGE, '신유신' AS NAME FROM DUAL    UNION ALL
                SELECT 5 AS AGE, '박유신' AS NAME FROM DUAL    UNION ALL
                SELECT 6 AS AGE, '조유신' AS NAME FROM DUAL    UNION ALL
                SELECT 7 AS AGE, '이유신' AS NAME FROM DUAL    UNION ALL
                SELECT 8 AS AGE, '송유신' AS NAME FROM DUAL
            )
            SELECT AGE
                 , NAME

              FROM BASE
            WHERE AGE > p_age
        ;
END;
/

 
 
 

■ com.EmpVO.java

package com;
public class EmpVO {

       private int age;
       private String name;

       public int getAge() {
              return age;
       }
       public void setAge(int age) {
              this.age = age;
       }
       public String getName() {
              return name;
       }
       public void setName(String name) {
              this.name = name;
       }
}

 
 
 

■ com.Params.java

package com;
import java.util.List;

public class Params {

       private int age;
       private String name;
       // 프로시져의 결과값 리턴 용도
       private List<EmpVO> empList;
       public int getAge() {
              return age;
       }
       public void setAge(int age) {
              this.age = age;
       }
       public String getName() {
              return name;
       }
       public void setName(String name) {
              this.name = name;
       }
       public List<EmpVO> getEmpList() {
              return empList;
       }
       public void setEmpList(List<EmpVO> empList) {
              this.empList = empList;
       }
}

 
 
 

■ EmpMapper.xml

<!--  Search for Emp List  -->
<resultMap id="empMap" type="com.EmpVO"/>
<select id="getEmpList"
        statementType="CALLABLE"
        parameterType="com.Params">
    { CALL TEST_PROC (
            ${age},
            #{empList, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=empMap}
    ) }
</select>

 
 
 

■ EmpDAO.java

void getEmpList(Params params);

 
 
 

■ EmpService.java

public List<EmpVO> getEmpList(final Params params);

 
 
 

■ EmpServiceImpl.java

@Override
public List<EmpVO> getEmpList(final Params params) {

    final ProfitDAO empDAO = sqlSession.getMapper(ProfitDAO.class);       
    empDAO.getEmpList(params);

    return params.getEmpList();
}

 
 
 

■ EmpServiceTest.java

@Test
public void testGetEmpList() {
    Params params = new Params();
    params.setAge(1);
    List<EmpVO> empList = profitService.getEmpList(params);

    int i = 0;
    EmpVO empVO = null;
    Iterator<EmpVO> iterator = empList.iterator();
    while( iterator.hasNext() ) {
        empVO = iterator.next();
        System.out.println( "[mirinae.maru] EmpVO["+i+"] : " + empVO.getAge() +"\t"+ empVO.getName() );
        i++;
    }

    assertTrue("결과가 있습니다.", empList.size()>0);
}

 
 
 

■ 실행 결과

[mirinae.maru] EmpVO[0] : 2장유신
[mirinae.maru] EmpVO[1] : 3최유신
[mirinae.maru] EmpVO[2] : 4신유신
[mirinae.maru] EmpVO[3] : 5박유신
[mirinae.maru] EmpVO[4] : 6조유신
[mirinae.maru] EmpVO[5] : 7이유신
[mirinae.maru] EmpVO[6] : 8송유신

 
 
 

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다