oracle存储过程返回多个游标实例浪迹天涯BlogJavaWord文档下载推荐.docx

oracle存储过程返回多个游标实例浪迹天涯BlogJavaWord文档下载推荐.docx

《oracle存储过程返回多个游标实例浪迹天涯BlogJavaWord文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle存储过程返回多个游标实例浪迹天涯BlogJavaWord文档下载推荐.docx（6页珍藏版）》请在冰豆网上搜索。

p_cus_01OUTpro_package_test_001.Test_CURSOR01,

p_cus_02OUTpro_package_test_002.Test_CURSOR02

）

AS

--定义变量

vs_lxVARCHAR2

（1）;

--变量

vs_test1_idVARCHAR2（100）;

vs_test1_mcVARCHAR2（100）;

vs_test2_idVARCHAR2（100）;

vs_test2_mcVARCHAR2（100）;

--default_cSYS_REFCURSOR;

BEGIN

--用输入参数给变量赋初值。

vs_lx:

=in_lx;

--插入test1表。

OPENp_cus_01FORSelect

a.idAsid1,

a.mcAsmc1,

b.idAsid2,

b.mcAsmc2

Into

vs_test1_id,

vs_test1_mc,

vs_test2_id,

vs_test2_mc

Fromtest1a,test2bWherea.id=b.idAnda.lx=vs_lx;

--ifp_cus_01%rowcount=0then

--p_cus_01:

=default_c;

--endif;

OPENp_cus_02FORSelect

idAsid1,

mcAsmc1

vs_test1_mc

Fromtest2Wherelx=vs_lx;

--ifp_cus_02%rowcount=0then

--p_cus_02:

--错误处理部分。

OTHERS表示除了声明外的任意错误。

SQLERRM是系统内置变量保存了当前错误的详细信息。

Exception

WHENOTHERSThen

ROLLBACK;

Return;

Endpro_query_001;

java代码调用

<

imgid="

Codehighlighter1_80_1743_Open_Image"

onclick="

this.style.display='

none'

;

Codehighlighter1_80_1743_Open_Text.style.display='

Codehighlighter1_80_1743_Closed_Image.style.display='

inline'

Codehighlighter1_80_1743_Closed_Text.style.display='

"

src="

align="

top"

>

Codehighlighter1_80_1743_Closed_Image"

style="

display:

none;

Codehighlighter1_80_1743_Open_Image.style.display='

publicvoidProcQuery（StringprocString,String[]params）throwsException{

Sessionsession=null;

Connectionconn=null;

ResultSetrs1=null;

ResultSetrs2=null;

CallableStatementproc=null;

procString="

{callpro_query_001（?

?

）}"

params=newString[1];

params[0]="

0"

Codehighlighter1_349_1347_Open_Image"

Codehighlighter1_349_1347_Open_Text.style.display='

Codehighlighter1_349_1347_Closed_Image.style.display='

Codehighlighter1_349_1347_Closed_Text.style.display='

Codehighlighter1_349_1347_Closed_Image"

Codehighlighter1_349_1347_Open_Image.style.display='

try{

session=getHibernateTemplate（）.getSessionFactory（）

.openSession（）;

conn=session.connection（）;

proc=conn.prepareCall（procString）;

proc.setString（1,params[0]）;

//传入的参数

proc.registerOutParameter（2,oracle.jdbc.OracleTypes.CURSOR）;

proc.registerOutParameter（3,oracle.jdbc.OracleTypes.CURSOR）;

proc.execute（）;

rs1=（ResultSet）proc.getObject

（2）;

//返回第一个游标

rs2=（ResultSet）proc.getObject（3）;

//返回第二个游标

Codehighlighter1_877_1143_Open_Image"

Codehighlighter1_877_1143_Open_Text.style.display='

Codehighlighter1_877_1143_Closed_Image.style.display='

Codehighlighter1_877_1143_Closed_Text.style.display='

Codehighlighter1_877_1143_Closed_Image"

Codehighlighter1_877_1143_Open_Image.style.display='

while（rs1!

=null&

amp;

&

rs1.next（））{

System.out.println（"

gt;

+rs1.getString（"

id1"

））;

mc1"

id2"

mc2"

}

Codehighlighter1_1190_1324_Open_Image"

Codehighlighter1_1190_1324_Open_Text.style.display='

Codehighlighter1_1190_1324_Closed_Image.style.display='

Codehighlighter1_1190_1324_Closed_Text.style.display='

Codehighlighter1_1190_1324_Closed_Image"

Codehighlighter1_1190_1324_Open_Image.style.display='

while（rs2!

rs2.next（））{

}

Codehighlighter1_1372_1512_Open_Image"

Codehighlighter1_1372_1512_Open_Text.style.display='

Codehighlighter1_1372_1512_Closed_Image.style.display='

Codehighlighter1_1372_1512_Closed_Text.style.display='

Codehighlighter1_1372_1512_Closed_Image"

Codehighlighter1_1372_1512_Open_Image.style.display='

}catch（SQLExceptione）{

e.printStackTrace（）;

thrownewException（"

调

用存储过程的时候发生错误[sql="

+procString+"

]"

e）;

Codehighlighter1_1523_1735_Open_Image"

Codehighlighter1_1523_1735_Open_Text.style.display='

Codehighlighter1_1523_1735_Closed_Image.style.display='

Codehighlighter1_1523_1735_Closed_Text.style.display='

Codehighlighter1_1523_1735_Closed_Image"

Codehighlighter1_1523_1735_Open_Image.style.display='

}finally{

if（proc!

=null）

proc.close（）;

if（rs1!

rs1.close（）;

if（rs2!

rs2.close（）;

if（conn!

conn.close（）;