完整例子 JAVA + Oracle存储过程返回查询结果集 =========================================================== 作者: atgc(http://atgc.itpub.net) 发表于: 2007.06.09 18:10 分类: 技术文章 出处: http://atgc.itpub.net/post/22412/293784 --------------------------------------------------------------- SQL> desc aa Name Null? Type ----------------------------------------- -------- ---------------------------- ID VARCHAR2(2)SQL> select * from aa;ID -- 1 2 3 4 5 6 7 8 9 10 1111 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)); } } }
JAVA + Oracle存储过程返回查询结果集
===========================================================
作者: atgc(http://atgc.itpub.net)
发表于: 2007.06.09 18:10
分类: 技术文章
出处: http://atgc.itpub.net/post/22412/293784
--------------------------------------------------------------- SQL> desc aa
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(2)SQL> select * from aa;ID
--
1
2
3
4
5
6
7
8
9
10
1111 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));
}
}
}