第三种情况:返回列表.
由于Oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.要分两部分来写:
create or replace package tpackage as
type t_cursor is ref cursor;
procedure test_c(c_ref out t_cursor);
end;
create or replace package body tpackage as
procedure test_c(c_ref out t_cursor)is
begin
open c_ref for select*from test;
end test_c;
end tpackage;
Java调用代码:
package com.test;
import java.sql.*;
import java.io.*;
import java.sql.*;
public class TestProcB
{
public TestProcB(){
}
public static void main(String[]args)
{
Connection conn=null;
CallableStatement proc=null;
ResultSet rs=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
proc=conn.prepareCall("{?=call tpackage.test_b(?)}");
proc.registerOutParameter(1,OracleTypes.CURSOR);
proc.execute();
while(rs.next()){
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2));
}
}catch(Exceptione){
e.printStackTrace();
}finally{
try{
if(null!=rs){
rs.close();
if(null!=proc){
proc.close();
}
if(null!=conn){
conn.close();
}
}
}catch(Exceptionex){
}
}
}
}
Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
newHibernateCallback()...{
publicObjectdoInHibernate(Sessionsession)
throwsHibernateException,SQLException...{
CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();
returnnull;
}
});