JAVA와 ORACLE REF CURSOR 예제

  • CallableStatement
    • DBMS들에 대한 표준방법으로 저장 프로시저(Stored Procedure)를 호출하는 방법을 제공
  • CURSOR
    • 특정 SQL문을 처리한 결과를 담고있는 영역으로 일종의 포인터
    • 커서를 사용하여 처리된 SQL문의 결과 집합에 접근
    • 묵시적 CURSOR와 명시적 CURSOR가 있음
  • REF CURSOR
    • 묵시적 CURSOR와 명시적 CURSOR는 CURSOR가 선언이 될 때 수행 될 SQL을 미리 지정하고 실행하는 방법을 지원
    • REF CURSOR는 CURSOR를 선언하는 시점에는 SQL을 지정하지 않고 CURSOR가 Open이 되는 시점에 SQL 문장을 지정

패키지 헤더

CREATE OR REPLACE PACKAGE CURSOR_PKG
AS
	TYPE T_CURSOR IS REF CURSOR;
	PROCEDURE SP_JOB_HISTORY(
        IN_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
        OUT_CURSOR		IN OUT T_CURSOR
    );
END CURSOR_PKG;
/

패키지 바디

CREATE OR REPLACE PACKAGE BODY CURSOR_PKG
AS
PROCEDURE SP_JOB_HISTORY
(
    IN_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    OUT_CURSOR		IN OUT T_CURSOR
)
IS
BEGIN
	OPEN OUT_CURSOR FOR
	SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
	JO.JOB_TITLE AS TITLE,
	JH.START_DATE AS SDATE,
	JH.END_DATE AS EDATE
	FROM EMPLOYEES E JOIN JOB_HISTORY JH
		ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID
		JOIN JOBS JO
        ON JH.JOB_ID = JO.JOB_ID
    WHERE E.EMPLOYEE_ID = IN_EMPLOYEE_ID;
END SP_JOB_HISTORY;
END CURSOR_PKG;
/

JAVA에서 REF CURSOR 활용

package com.jremind.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.jremind.model.Employee;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class HrDao {
    // 싱글톤으로 메모리 할당
	private static HrDao dao = new HrDao();
	private Connection conn;
	private ResultSet rSet;
	private PreparedStatement pStmt;
	private CallableStatement cstmt;
    private OracleCallableStatement ocstmt;
	
	private final String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private final String username = "c##hr";
	private final String password = "1234";
	
	private HrDao() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
    // getInstance() 메서드로 dao 인스턴스를 반환
	public static HrDao getInstance() {
		return dao;
	}
	
    // Oracle 데이터베이스 connection
	private void getConnection() {
		try {
			conn = DriverManager.getConnection(url, username, password);
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// Oracle 데이터베이스 연결 종료
	private void closeConnection() {
		try {
			if(rSet != null)
				rSet.close();
			if(pStmt != null)
				pStmt.close();
			if(cstmt != null)
				cstmt.close();
			if(ocstmt != null)
				ocstmt.close();
			if(conn != null)
				conn.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public List<Employee> getHistoryEmployees(int id){
		List<Employee> employees = new ArrayList<Employee>();
		
		getConnection();
						
		try {
            // Connection의 prepareCall 메서드로 CallableStatement 객체를 생성
            // prepareCall 메서드로 데이터베이스 저장 프로시저를 호출
			cstmt = conn.prepareCall("BEGIN CURSOR_PKG.SP_JOB_HISTORY(?,?); END;");
            // 프로시저의 in Parameter로 사원 번호(id)를 사용
			cstmt.setInt(1, id);
            // REF CURSOR OUTPUT PARAMETER를 OracleTypes.CURSOR으로 등록 
			cstmt.registerOutParameter(2, OracleTypes.CURSOR);
			
            // CallableStatement를 실행
			cstmt.executeQuery();
			
            // getCursor() 메서드를 사용하기 위해 OracleCallableStatement Object로 변환
            // CallableStatement는 getCursor() 메서드가 정의되어 있지 않기 때문
			ocstmt = (OracleCallableStatement)cstmt;
            // ResultSet에 결과 데이터를 담은 Cursor를 저장
			rSet = ocstmt.getCursor(2);
			Employee employee;

			while(rSet.next()) {
                // 미리 만들어놓은 Employee 모델에 데이터를 담음
				employee = new Employee();
				employee.setFullName(rSet.getString(1));
				employee.setTitle(rSet.getString(2));
				employee.setHireDate(rSet.getString(3));
				employee.setEndDate(rSet.getString(4));

				employees.add(employee);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection();		
		}
		
		return employees;
	}
}