java oracle存储过程返回数据集_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));

}

}

}[@more@]JAVA