Ref Cusor 사용하기

Ref Cusor 사용하기

JSP등의 프로그램에서 오라클의 Stored Procedure를 호출하여 커서(레코드셋, SELECT등의 결과셋)를 되돌려 받기 위해 ref cursor를 사용 하는데 아래의 예를 통해 이해 하자구요~

먼저 오라클의 SCOTT/TIGER로 로그인을 하신 다음 아래처럼 패키지를 하나 생성 합
니다.

SQL> CREATE or REPLACE PACKAGE TYPES
  2  AS
  3  Type cursorType IS Ref Cursor;
  4  end;
  5  /

패키지가 생성되었습니다.

.
아래는 테스트용 Function 입니다. EMP 테이블의 데이터 중 인자로 넘기는 부서에 해당 하는 데이터만 리턴 합니다…

SQL> CREATE OR REPLACE FUNCTION emptest(v_deptno in number) RETURN TYPES.CURSORTYPE
  2  AS
  3     test_cursor TYPES.CURSORTYPE;
  4     sql_string Varchar2(500);
  5  BEGIN
  6     sql_string := 'Select * from scott.Emp where deptno = :deptno' ;
  7     Open test_cursor FOR sql_string USING v_deptno;
  8     RETURN test_cursor;
  9     CLOSE test_Cursor;
10  END;
11  /

함수가 생성되었습니다.

한가지 더,,, 위 함수를 다음과 같이 USING문을 이용하지 않고도 가능 합니다.

CREATE OR REPLACE FUNCTION emptest(v_deptno in number) RETURN TYPES.CURSORTYPE
AS
   test_cursor TYPES.CURSORTYPE;
   sql_string Varchar2(500);
BEGIN
   sql_string := 'Select * from scott.Emp where deptno = ' || v_deptno;
   Open test_cursor FOR sql_string;
   RETURN test_cursor;
   CLOSE test_Cursor;
END;
/


자 그럼 이젠 JSP 프로그램을 간단히 만들어 TEST 해 봅시다…

//test.jsp

<%@ page language="java" contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*,oracle.jdbc.driver.* " %>
<%
    Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        String driver_name = "oracle.jdbc.driver.OracleDriver"; //오라클 드라이버
        String url = "jdbc:oracle:thin:@localhost:1521:wink";   //호스트
        String user = "scott"; //계정
        String pwd = "tiger";  //비밀번호
        String query = "";

        try {
                Class.forName(driver_name); //jdbc 드라이버연결
                conn =  DriverManager.getConnection(url,user,pwd); //Connection인수 입력
        
                String proc_call = "{? = call emptest(?)}";
        
                // create callable statement
                cstmt = conn.prepareCall(proc_call);

                // key here is to register the output parameter
                // of type cursor, execute, then cast it as a ResultSet.
                cstmt.registerOutParameter(1, OracleTypes.CURSOR);
                cstmt.setInt(2, 10);  //10번 부서의 데이터만 얻기 위해

                cstmt.executeQuery();
                rs = (ResultSet)cstmt.getObject(1);

                while(rs.next()) {
                        out.println(rs.getString("ename") + "<br>");
                }
        }
        catch(Throwable e)
        {
                out.println(e);
        }
        finally {
                try {
                        rs.close();
                        cstmt.close();
                        conn.close();
                }
                catch(Exception e) {}
        }
%>

전 DocumentRoot에 두고 http://localhost/test.jsp 라고 실행을 했습니다.

[결과]
CLARK
KING
MILLER

기타 참고 :
http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/

http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php