JAVA + Oracle儲存過程返回查詢結果集

SQL> desc aa
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(2)

SQL> select * from aa;

ID
--
1
2
3
4
5
6
7
8
9
10
11

11 rows selected.

---------------
建立PACKAGE
---------------
create or replace package pkg_test
as
type pagination is ref cursor;
procedure page_test
(
p_count in out pls_integer,
p_page in out pagination,
p_beg in pls_integer,
p_end in pls_integer,
p_id in varchar2
);
end;
/

create or replace package body pkg_test
as
procedure page_test
(
p_count in out pls_integer,
p_page in out pagination,
p_beg in pls_integer,
p_end in pls_integer,
p_id in varchar2
)
is
sql_count varchar2(1000) := '';
sql_page varchar2(30000) := '';
begin
sql_count := 'select count(*) from aa';
sql_page := 'select id from aa where rownum<=10';
execute immediate sql_count into p_count;
open p_page for sql_page;
end;
end;
/

---------
test.java
---------

import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

public class test
{
public static void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:資料庫SID","使用者名稱","密碼");
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("begin pkg_test.page_test(?,?,?,?,?); end;");
cstmt.registerOutParameter(1,OracleTypes.VARCHAR); //返回記錄數
cstmt.registerOutParameter(2,OracleTypes.CURSOR); //返回結果集
cstmt.setInt(3,1); //起始記錄號
cstmt.setInt(4,10); //結束記錄號
cstmt.setString(5,"10"); //WHERE 條件
cstmt.execute();
ResultSet rs = (ResultSet)cstmt.getObject(2);
while (rs.next())
{
System.out.println(rs.getString(1));
}
}
}