OracleCallableStatement
==============================================
From:http://forums.oracle.com/forums/thread.jspa?messageID=1698889
PURPOSE
-------
dynamic SQL 과 흡사하게 JDBC program 에서 SQL 을 실행할때
REF Cursors 를 사용하는 방법을 알아 봅니다.
Explanation
-----------
PreparedStatement
-----------------
JDBC 는 PreparedStatement 를 이용해 dynamic SQL 을 실행하기 위한
APIs 를 제공합니다.
예제 :
PreparedStatement pstmt;
pstmt=conn.prepareStatement("select empno from emp where deptno=?");
pstmt.setInt(1,10);
ResultSet c1;
c1=pstmt.executeQuery();
pstmt.setInt(1,10)
while (c1.next ())
{System.out.println (c1.getInt(1));}
REF CURSOR
----------
JDBC 에서 dynamic SQL 를 실행하는 다른 방법이 있습니다.
이 방법은 Oracle8i 이상에서 실행가능합니다.
다음의 경우 REF CURSOR 를 resturn 하는 PL/SQL procedure 를 사용했습니다.
REF CURSOR 는 cursor 에 대한 pointer 입니다.
Stored procedures 는 REF CURSOR category 의
cursor 변수나 user-defined types 을 반환합니다.
이 결과는 database cursor 나 JDBC result set 과 같습니다.
REF CURSOR 는 근본적으로는 query 의 결과를 캡슐로 싸고 있습니다.
REF CURSOR 사용 예제
---------------------
create or replace package test_ref_cursor as
type gc is REF CURSOR;
procedure test_ref_cursor(v1 OUT gc, v2 in varchar2);
end;
/
create or replace package body test_ref_cursor as
procedure test_ref_cursor(v1 OUT gc, v2 in varchar2) as
begin
open v1 for v2;
end;
end;
/
장점
----
o Code 재사용
동일한 package procedure 를 java program 이나 그밖의
program 에서 사용할 수 있습니다.
o Load Balancing.
Example
-------
JDBC Program 에서 REF CURSOR 를 사용하는 방법
---------------------------------------------
1. stored procedure 를 호출하기 위해 JDBC callable statement 를 사용합니다.
CallableStatement ocstmt;
cstmt = conn.prepareCall("begin test_ref_cursor.test_ref_cursor(?,?); end;");
2. CallableStatement 를 위한 REF CURSOR output parameter 를 OracleTypes.CURSOR
로 등록합니다.
주의 : OracleTypes class 는 oracle.jdbc.driver 아래에 있습니다.
따라서 oracle.jdbc.driver.OracleTypes 를 import 하셔야 합니다.
ocstmt.registerOutParameter (1, OracleTypes.CURSOR);
3. CallableStatement 를 실행합니다.
ocstmt.execute ();
4. getCursor() method 를 사용하기 위해 CallableStatement 를
OracleCallableStatement object 로 바꿉니다.
getCursor() method 는 오라클에서 표준 JDBC API 를 확장해서 만든 것입니다.
이 method 는 REF CURSOR 를 ResultSet object 로 return 합니다.
OracleCallableStatement tstmt;
tstmt = (OracleCallableStatement)ocstmt;
참고로 OracleCallableStatement의 선언문을 API문서에서 찾아보면 다음과 같이 CallableStatement인터페이스를 구현하고 있기 때문에 CallableStatement의 레퍼런스를 OracleCallableStatement형 변수에 담을 수 있는 것입니다.
- public interface OracleCallableStatement
- extends java.sql.CallableStatement, OraclePreparedStatement
- extends java.sql.CallableStatement, OraclePreparedStatement
conn.prepareCall(스토어드 프로시저) 메소드가 리턴하는 것은 표면적으로는 CallableStatement의 레퍼런스로 나타나지만 내부적으로 CallableStatement의 자식인 OracleCallableStatement의 레퍼런스가 부모형인 CallableStatement형으로 캐스팅된 상태로 리턴되는 것이며, 부모형으로 캐스팅된 레퍼런스는 다시 자식형으로 형변환했을 때 자식대에서 추가된 메소드를 사용할 수가 있는 것이다.
위의 선언문을 보면 OracleCallableStatement는 CallableStatement의 자식이므로 부모형인 CallableStatement형으로 형변환이 가능하다. 이렇게 부모형으로 형변환된 상태로 리턴된 후에 다시 자식형인 OracleCallableStatement형으로 캐스팅하면 부모한테는 없는 메소드를 실행할 수 있게 되는 것이다.
이 원리를 이용하여 다음과 같은 예제를 작성했으므로 이해에 도움이 되었으면 좋겠다.
package test;
/* 부모형 변수가 자식객체의 레퍼런스를 저장하고 있을 때는 다시 자식형으로 형변환이 가능하므로
* 다음과 같은 코드가 가능하며, 자바 API에서도 종종 사용되고 있다.
* 예를 들어, oracle.jdbc.OracleCallableStatement인터페이스는 java.sql.CallableStatement를 구현하고 있다.
* 그러므로 OracleCallableStatement은 CallableStatement의 자식인 셈이다.
* 또 이러한 예는 AWT에서도 보이며 getGraphics() 메소드가 리턴하는 것은 Graphics형으로 보이지만 내부적으로는 Graphics 를 상속한 Graphics2D의 레퍼런스가 부모형인 Graphics형으로 리턴되는 것이며, 보다 향상된 기능을 사용하기 위해서는 원래의 자식형인 Graphics2D형으로 캐스팅하여 사용하는 것이다.
*/
/* 부모 인터페이스 */
interface SuperInterface {
void superMethod();
}
/* 인터페이스를 구현한 자식 클래스 */
class SubClass implements SuperInterface {
private SubClass(){}
@Override
public void superMethod() {
System.out.println("superMethod()");
}
/* 자식대에서 추가된 메소드 */
public void subMethod(){
System.out.println("subMethod()");
}
/* 자식객체를 생성하여 부모형으로 리턴하므로 자식대에서 추가된 메소드는 비활성화(hidden)된다*/
public static SuperInterface getInstance(){
return new SubClass();
}
}
public class Main{
public static void main(String[] args){
/* 자식객체가 생성되어 부모인터페이스 형으로 리턴된다.
* 그러므로 자식대에서 추가된 메소드는 비활성화된 상태이다
* */
SuperInterface si = SubClass.getInstance();
/* 원래 자식형으로 다시 캐스팅하면 자식대에서 추가된 메소드를 사용할 수 있다*/
SubClass sc = (SubClass) si;
sc.subMethod();
}
}
5. OracleCallableStatement 의 getCursor() method 를 사용해서 REF CURSOR 를
JDBC ResultSet variable 에 저장합니다.
ResultSet cursor;
cursor = tstmt.getCursor (1);
6. 위에서 생성한 resultset을 사용합니다.
Example
-------
다음 예제에서는 위에서 언급한 package procedure 에서 data 를 추출합니다.
procedure 는 input argument 로 실행할 select 문장을 받습니다.
package procedure 는 실행할 select 문장을 위해 REF CUSROR 를 생성합니다.
이 REF CUSROR 는 JDBC program 안에서 ResultSet 에 담기게 됩니다.
import java.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
/* Could also use import oracle.jdbc.driver.*; instead of the last two above imports */
public class REFtest {
public static void main(String[] args) {
REFtest vTest = new REFtest();
vTest.call_stmt();
}
void call_stmt()
{
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@krint-5.kr.oracle.com:1525:ora920", "scott", "tiger");
CallableStatement ocstmt;
ocstmt = conn.prepareCall
("begin test_ref_cursor.test_ref_cursor(?,?); end;");
ocstmt.setString(2,"select ename from emp");
ocstmt.registerOutParameter (1, OracleTypes.CURSOR);
ocstmt.execute ();
OracleCallableStatement tstmt;
tstmt = (OracleCallableStatement)ocstmt;
ResultSet cursor;
cursor = tstmt.getCursor (1);
// Use the cursor like a normal ResultSet
while (cursor.next ())
{System.out.println (cursor.getString (1));}
}
catch(Exception e)
{
}
}
}