MyBatis에서 Oracle Procedure 및 Anonymous Block 실행하기

스프링 프레임워크, MyBatis, Oracle 환경에서 Oracle 의 저장 프로시저(Stored Procedure)를 호출하는 방법을 알아보겠습니다. 또한, PL/SQL 익명 블럭(Anonymous Block)을 호출 하는 예제도 보겠습니다. 익명 블럭은 저장 프로시저와 거의 동일한 방법으로 호출하게 됩니다.

 

이번에 사용하는 예제는 "스프링 프레임웍에서 MyBatis, Oracle 사용하기" 에서 사용한 환경을 기본으로 하여 작성되었습니다.

 

예제의 전체 소스는 하단에 추가해 두었습니다.

 

먼저 테스트에 사용할 저장 프로시저를 하나 만들어 보겠습니다. Oracle 저장 프로시저는 세 가지 타입의 파라미터를 받습니다.

 

- IN : 저장 프로시저로 값을 보내는데만 사용됩니다. - IN OUT : 저장 프로시저로 값을 보내고, 값을 반환하는 두가지 기능을 모두 가집니다. - OUT : 저장 프로시저에서 값을 반환하는 데만 사용합니다.

 

아래 샘플 프로시저는 세개의 파라미터를 가지는데 각각은 다음과 같은 기능을 합니다.

 

- p_num IN INT : 글번호를 IN 파라미터를 받습니다. 입력 전용 입니다. - p_name IN OUT VARCHAR2 : 작성자명을 파라미터를 받아서 사용하고, 메세지를 출력하는데도 사용됩니다. - p_rc OUT SYS_REFCURSOR : 출력용 파라미터로 조회된 결과 반환을 위해 커서를 반환합니다.

 

CREATE OR REPLACE PROCEDURE test_proc (
	p_num   IN      INT,
	p_name  IN OUT  VARCHAR2,
	p_rc    OUT     SYS_REFCURSOR )
IS
BEGIN
    OPEN p_rc FOR
        SELECT NUM, NAME, TITLE, CONTENT, READ_COUNT, WRITE_DATE
          FROM TB_BOARD
         WHERE NUM >= p_num
           AND NAME = p_name
         ORDER BY NUM DESC;
    p_name := 'call test_proc....';
END;
/

 

MyBatis 를 사용하여 프로시저를 실행할 때, 파라미터로 값을 넘기고 받기위해 HashMap 또는 VO 객체를 사용할 수 있습니다. 각각에 대하여 다음 순서로 알아 보겠습니다.

 

1. 저장 프로시저 호출시 HashMap을 사용하여 데이터 주고 받기

2. 저장 프로시저 호출시 VO 객체를 사용하여 데이터 주고 받기

3. 익명 블럭 호출시 VO 객체를 사용하여 데이터 주고 받기

 

 

1. 저장 프로시저 호출시 HashMap을 사용하여 데이터 주고 받기

 

- DAO 인터페이스 입니다. BoardDAO.java 파일 입니다.

 

프로시저 호출을 위해 파라미터를 넘기지만 반환값을 리턴하지 않습니다. 그 이유는 OUT 파라미터로 보내질 값은 파라미터로 넘긴 HashMap 또는 VO 객체에 넣어지기 때문입니다.

 

package com.tistory.pentode.service.dao;

import java.util.List; import java.util.Map;
import com.tistory.pentode.vo.TransferVO;
import com.tistory.pentode.vo.BoardVO;

public interface BoardDAO {

	// HashMap을 이용한 예제
	void mapProcedureList(Map<string, object=""> map) throws Exception;

	// VO를 사용한 프로시저 호출 예제
	void procedureList(TransferVO container) throws Exception;

	// VO를 사용한 익명 블럭 호출 예제
	void anonymousBlockList(TransferVO container) throws Exception;

}

 

- sqlmap 내용 입니다. src/main/resources/sqlmap/Tb_Board_SQL.xml 파일 입니다.

 

프로시저 호출을 위해서 statementType을 CALLABLE로 설정합니다. HashMap을 파라미터로 사용할 것이므로 parameterType은 hashmap 으로 설정합니다.

 

첫번째 파라미터 #{num} 은  내부적으로는 map.get("num") 처럼  값을 찾게 됩니다. 두번재 입출력용 파라미터의 경우 mode를 INOUT으로 지정하고, jdbcType은 VARCHAR로  javaType은 String 으로 지정합니다.

 

세번째 OUT 파마리터는 커서를 반환해야 하므로 mode는 OUT ,  jdbcType는 CURSOR,  javaType은 ResultSet,  resultMap은 boardMap 으로 지정합니다. boardMap 은 상단의 <resultMap> 태그로 타입을 hashmap 으로 지정되어 있습니다.

 

이렇게 지정을 하면 조회된 데이터의 각 행의 정보를 하나의 HashMap 에 필드명을 키로 하여 담습니다. 여러행이 조회되므로 이 Map 들을 List 에 담아서 반환하게 됩니다.

 

<resultMap id="boardMap" type="hashmap" />
<select id="mapProcedureList" statementType="CALLABLE" parameterType="hashmap">
{
	CALL test_proc(
		#{num},
		#{name, mode=INOUT, jdbcType=VARCHAR, javaType=String},
		#{resultList, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=boardMap}
	)
}
</select>

  

- 서비스 객체 구현 부분 입니다. BoardServiceImpl.java 파일 입니다.

HashMap을 하나 만들어서 인자로 넘길 값들을 넣은 다음 DAO 를 호출 합니다. 결과도 이 map에 넣어지게 됩니다. 그러므로 DAO 로부터 반환되는것 없이  파라미터로 보냈던 객체를 반환합니다.

 

@Override
@Transactional
public Map mapProcedureList() throws Exception {
	Map map = new HashMap();
	map.put("num", new Integer(2));
	map.put("name", "홍길동");
	boardMapper.mapProcedureList(map);
	return map;
}

  

- 컨트롤러 부분 입니다. HemeController.java 파일 입니다.

 

반환된 Map 객체로부터 OUT 파라미터의 결과 리스트와 INOUT 파라미터의 name 을 꺼내 model 객체에 넣습니다.

 

@RequestMapping(value = "/mapProcedureList.do")
public String mapProcedureList(Model model) throws Exception {
	Map map = boardService.mapProcedureList();
	model.addAttribute("list", map.get("resultList"));
	model.addAttribute("name", map.get("name"));
	return "mapList";
}

 

- JSP 출력 부분입니다. mapList.jsp 파일 입니다.

리스트에는 Map 들어 있으므로 EL 을 사용하여 다음과 같이 출력합니다. ${item['키값']} 형태가 됩니다.

 

<h2>${name}</h2>

<c:forEach var="item" items="${list}">
	<tr>
		<td>${item['NUM']}</td>
		<td>${item['NAME']}</td>
		<td>${item['TITLE']}</td>
		<td>${item['READ_COUNT']}</td>
		<td>${item['WRITE_DATE']}</td>
	</tr>
</c:forEach>

 

- 실행 결과 입니다.

 

 

 

2. 저장 프로시저 호출시 VO 객체를 사용하여 데이터 주고 받기

 

- 값 전달용 VO 입니다. TransferVO.java 파일 입니다.

이 VO 에 num, name 을 담아서 보내고, 출력값도 이 객체로 받게 됩니다.

 

package com.tistory.pentode.vo;

import java.util.List;

public class TransferVO {

	private Integer num;
	private String name;
	private List resultList;

	public TransferVO() { }

	public void setResultList(List list) {
		this.resultList = list;
	}

	public List getResultList() {
		return this.resultList;
	}

	public void setNum(Integer num) {
		this.num = num;
	}

	public Integer getNum() {
		return num;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getName() {
		return this.name;
	}
}

 

- 마이바티스 설정 파일의 타입 앨리어스 부분 입니다. mybatis-config.xml 파일 입니다.

한행의 정보를 담을 BoardVO 와 파라미터로 사용할 TrasnferVO 를 별칭을 지정합니다. 나중에 간단히 alias 명으로 사용할 수 있습니다.

 

<typeAliases>
	<typeAlias alias="boardVO" type="com.tistory.pentode.vo.BoardVO"/>
	<typeAlias alias="transferVO" type="com.tistory.pentode.vo.TransferVO"/>
</typeAliases>

 

- sqlmap 부분 입니다. src/main/resources/sqlmap/Tb_Board_SQL.xml 파일 입니다.

 

조회된 결과는 각 행이 BoardVO 에, 이 VO는  List 에 담겨지게 됩니다. resultMap에서 조회시 데이터베이스 컴럼명과 vo 프로퍼티가 어떻게 매칭되는지 지정합니다.

 

parameterType 는 alias 해 두었던 transferVO 로 지정합니다. resultMap 은  <resultMap id="procVO" ...  로 지정한 procVO 를 사용합니다.

 

<resultMap id="procVO" type="com.tistory.pentode.vo.BoardVO">
	<result property="num" column="NUM"/>
	<result property="name" column="NAME"/>
	<result property="title" column="TITLE"/>
	<result property="content" column="CONTENT"/>
	<result property="readCount" column="READ_COUNT"/>
	<result property="writeDate" column="WRITE_DATE"/>
</resultMap>

<select id="procedureList" statementType="CALLABLE" parameterType="transferVO">
{ CALL test_proc(
	#{num},
	#{name, mode=INOUT, jdbcType=VARCHAR, javaType=String},
	#{resultList, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=procVO})
}
</select>

 

- 서비스 객체 구현 부분 입니다. BoardServiceImpl.java 파일 입니다.

 

이번에는 Map 이 아니라 VO 에 값을 담아 전달합니다. 역시 DAO의 반환값을 없고, OUT 결과는 파라미터로 보낸  VO 에 채워지게 됩니다.

 

@Override
@Transactional
public TransferVO procedureList() throws Exception {
	TransferVO container = new TransferVO();
	container.setNum(2);
	container.setName("홍길동");
	boardMapper.procedureList(container);
	return container;
}

 

- 컨트롤러 부분 입니다. HomeController.java 파일 입니다.

 

조회된 결과를 model 에 담아 보냅니다.

 

@RequestMapping(value = "/procedureList.do")
public String procedureList(Model model) throws Exception {
	TransferVO container = boardService.procedureList();
	model.addAttribute("list", container.getResultList());
	model.addAttribute("name", container.getName());
	return "procList";
}

 

- JSP 출력 부분 입니다. procList.jsp 파일 입니다.

 

List 에는 BoardVO 가 담겨져서 오므로 EL 을 ${item.num} 과 같이 사용하여 출력합니다.

 

<h2>${name}</h2>

<c:forEach var="item" items="${list}">
	<tr>
		<td>${item.num}</td>
		<td>${item.name}</td>
		<td>${item.title}</td>
		<td>${item.readCount}</td>
		<td>${item.writeDate}</td>
	</tr>
</c:forEach>

 

- 실행 결과 입니다.

 

 

 

3. 익명 블럭 호출시 VO 객체를 사용하여 데이터 주고 받기

 

- sqlmap 부분 입니다. Tb_Board_SQL.xml 파일 입니다.

 

익명 블럭 (Anonymous Block)은 프로시저 처럼 데이터 베이스 서버에 저장하지 않고, PL/SQL 프로그램을 할 수 있습니다. 이렇게 사용하는 경우는 잘 없겠지만 필요하다면 사용할 수 있겠습니다.

 

<select id="anonymousBlockList" statementType="CALLABLE" parameterType="transferVO">
{ CALL
	DECLARE
		p_num  INT;
		p_name VARCHAR2(20);
		p_rc   SYS_REFCURSOR;
	BEGIN
		p_num  := #{num};
		p_name := #{name};

		OPEN p_rc FOR
            SELECT NUM, NAME, TITLE, CONTENT, READ_COUNT, WRITE_DATE
              FROM TB_BOARD
             WHERE NUM >= p_num
               AND NAME = p_name
             ORDER BY NUM DESC;

		#{resultList, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=procVO} := p_rc;
		#{name, mode=OUT, jdbcType=VARCHAR, javaType=String} := 'call anonymous block....';
	END
}
</select>

 

- 서비스 구현 부분, 컨트롤러, JSP 부분은 프로시저 호출과 동일한 방법으로 사용 합니다.

 

- 실행결과 입니다.

 

 

이것으로 MyBatis 를 사용해서 Oracle의 Stored Procedure 와 Anonymous Block 을 호출하는 방법을 알아봤습니다.

 

※ 예제소스

mybatis_procedure.zip

다운로드