Oracle Database TIP
REF CURSOR를 사용해 프로시저에서 여러행을 반환하는 예제 17 7 35,251

by 김정식 REF CURSOR REF_TYPE JDBC [2003.10.13]


 
/**  *
 * @(#)RefCursor.java  
 *
 *
 * Oracle REF Cursor를 사용해 프로시저에서 여러행을 반환하는 예제
 *
 * 
 * -- 패키지가 헤더 생성
 * CREATE OR REPLACE PACKAGE ref_cursor_pkg AS
 *  TYPE ref_type IS REF CURSOR;
 *  PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql in VARCHAR2);
 * END;
 * 
 * 
 * -- 패키지 본문 생성
 * CREATE OR REPLACE PACKAGE BODY ref_cursor_pkg AS
 *  PROCEDURE ref_cursor_pro(v_result OUT  ref_type, v_sql IN VARCHAR2)  AS
 *  begin
 *      OPEN v_result FOR v_sql;
 *  END;
 * END;
 *
 *
 * REF CURSOR는 오라클 PL/SQL에서 여러 레코드의 쿼리 결과를 가져올때 편하게 사용 할 수 있습니다.
 * 이 방법은 Oracle8i 이상에서 실행가능합니다. 
 *
 * 아래 예제는 특정 SQL문을 PL/SQL In Parameter로 입력 받아서 쿼리 결과를 Out파라미터로 
 * 반환하는 예제 입니다.
 * Java Source를 실행하기 전에 위에 패키지 헤더와 본문을 SQL*Plus에서 먼저 실행해 주세요
 *
 */
  
 
import java.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
  
 
public class RefCursor {
  
  public static void main(String[] args) {
    RefCursor vTest = new RefCursor();
    vTest.prepareCall();
 
  }
 
  public void prepareCall(){

    Connection conn = null
    CallableStatement cstmt = null;
    OracleCallableStatement ocstmt = null;

    try {

       DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
       conn = DriverManager.getConnection 
              ("jdbc:oracle:thin:@127.0.0.1:1521:ORACLE", "scott", "tiger");
            
       //Stored Procedure 를 호출하기 위해 JDBC Callable Statement를 사용 합니다
       cstmt =  conn.prepareCall("BEGIN ref_cursor_pkg.ref_cursor_pro(?,?); END;");
            
       //프로시져의 In Parameter로 SELECT문장을 넘깁니다.
       cstmt.setString(2,"SELECT empno, ename FROM emp");
            
       //CallableStatement를 위한 REF CURSOR OUTPUT PARAMETER를
       //OracleTypes.CURSOR로 등록합니다.
       cstmt.registerOutParameter (1, OracleTypes.CURSOR);
            
       //CallableStatement를 실행합니다.
       cstmt.execute ();
            
       //getCursor() method를 사용하기 위해 CallableStatement를 
       //OracleCallableStatement object로 바꿉니다.
       ocstmt = (OracleCallableStatement)cstmt;
            
       //OracleCallableStatement 의 getCursor() method를 사용해서 REF CURSOR를 
       //JDBC ResultSet variable 에 저장합니다.
       ResultSet cursor =  ocstmt.getCursor (1);
            
       //쿼리결과 empno, ename 출력
       while (cursor.next ()) {
         System.out.print (cursor.getString (1)+"          ");
         System.out.println (cursor.getString (2));
       }
            
     }catch(Exception e){
            
     }finally{ 
       if(ocstmt != null) try{ocstmt.close();} catch(Exception e) {} 
       if(cstmt!= null) try{cstmt.close();} catch(Exception e) {} 
       if(conn!= null) try{ conn.close();} catch(Exception e) {} 

    }    
  }
}
 
 
 
/**
 *  샐행
 *  java -classpath .;C:\classes12.zip  RefCursor
 *
 * 7369          SMITH 
 * 7521          WARD  
 * 7566          JONES 
 * 7654          MARTIN
 * 7698          BLAKE 
 * 7782          CLARK 
 * 7788          SCOTT 
 * 7839          KING  
 * 7844          TURNER
 * 7876          ADAMS 
 * 7902          FORD  
 * 7934          MILLER
 */
 
  
    

- 강좌 URL : http://www.gurubee.net/lecture/1843

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by guest [2006.09.12 18:52:43]
딱 제가 원하는 셈플인대요..
cstmt.excute(); 까지는 수행이 돼었습니다.
그런대.
ocstmt = (OracleCallableStatement)cstmt; 이부분에서 에러가 발생하였습니다.
참고로.. 오라클 9i의 ojdbc.jar를 라이브러리에 추가한 상태 입니다.

exception 메시지 : com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement
이렇게 나오는대. 이건 무엇인지.. ㅠㅠ

by z [2006.12.11 10:21:21]
안되요..ㅠ.ㅠ

RefCursor.java:58: unreported exception java.sql.SQLException; must be caught or
declared to be thrown
ocstmt.close();
^
RefCursor.java:59: unreported exception java.sql.SQLException; must be caught or
declared to be thrown
cstmt.close();
^
RefCursor.java:60: unreported exception java.sql.SQLException; must be caught or
declared to be thrown
conn.close();
^
3 errors
라고만 나와요..ㅠ.ㅠ

by jim [2007.04.17 03:29:53]
while(){}
뒷문장에 conn.close(); 삽입합니다.
finally()문장을 삭제해보세요

by T_R [2008.06.04 16:23:34]
v_result 이값이 나오는거 같은뎅;; 아닌가 몰겠네영 OUT은 그거 하나 밖에 없는거 같아서...아니라면 죄송하구요

by tt [2010.03.26 16:24:38]
ocstmt.close();
cstmt.close();
conn.close();

위의 세개 객체를 close() 해줄때 try{}catch(Exception e){} 로 Exception 상황을 잡아 줘야 합니다. 그리고 finally 구문은 삭제 하지 마시길~


}finally{
if(ocstmt != null) try{ocstmt.close();} catch(Exception e) {}
if(cstmt!= null) try{cstmt.close();} catch(Exception e) {}
if(conn!= null) try{ conn.close();} catch(Exception e) {}
}

이렇게 변경해보세요


by 도라지요 [2014.03.04 16:23:03]

패키지에서만 되나요?


by 백종현 [2017.04.17 23:27:49]

잘됩니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입