本文章所提及的全部源码和测试用例已经上传到http://download.csdn.net/source/459115欢迎朋友们下载、测试、评判、指正,给出宝贵意见.谢谢!
对于在java中调用存储过程,我一直因为是一件比较头疼的事情,因为各个数据库的实现往往不相同,这样就很难保证同一套程序在sqlserver和oracle上都能执行.尽管两个数据库中的存储过程名字和参数已经基本相同,以前用spring封装过一个,用了几个抽象类做了简单的设计,但是总感觉不是很好,而且最新的spring貌似已经废弃那几个类了,看来是有问题,周末花了些时间重新写了一个比较简单赤裸的封装.代码虽然不多但是就不一一讲解了.这里先把存储过程发出来.
sqlsever版
/*
返回2个结果集,外加输出out参数
*/
create procedure p1
@outputParam int output,
@id int
as
select @outputParam = @id
select @outputParam + @outputParam
select @outputParam
/* 直接返回值 */
create procedure p2
@outputParam int output,
@id int
as
select @outputParam = @id
return @outputParam
/* 返回一个结果集 */
create procedure p3
@outputParam int output,
@id int
as
select @outputParam = @id
select @outputParam
/* 最简单的形式 */
create procedure p4
as
print ' helloword '
create procedure p1
@outputParam int output,
@id int
as
select @outputParam = @id
select @outputParam + @outputParam
select @outputParam
/* 直接返回值 */
create procedure p2
@outputParam int output,
@id int
as
select @outputParam = @id
return @outputParam
/* 返回一个结果集 */
create procedure p3
@outputParam int output,
@id int
as
select @outputParam = @id
select @outputParam
/* 最简单的形式 */
create procedure p4
as
print ' helloword '
对应的oracle版,oracle中存储过程好像不能直接返回值.至少我还不知道怎么返回.目前依靠out参数返回游标作为结果集。
CREATE
OR
REPLACE
PROCEDURE
p1(
outputParam OUT int ,
id IN int ,
CURSOR1 out sys_refcursor,
CURSOR2 out sys_refcursor
) AS
BEGIN
select id into p1.outputParam from dual;
OPEN CURSOR1 FOR
outputParam OUT int ,
id IN int ,
CURSOR1 out sys_refcursor,
CURSOR2 out sys_refcursor
) AS
BEGIN
select id into p1.outputParam from dual;
OPEN CURSOR1 FOR