java 调用Oracle 存储过程 Jdbc/Hibernate

99ezxxj553

　　oracle 创建存储过程 两个参数 两个返回值

create or replace procedure PP_test(
oiyear    in number,
oimonth    in number,s_save out varchar2,s_update out varchar2) is
nottzset_exception exception;
eb exception;
begin
//查询插入的多少记录
select count(*) into s_save from pj_tempajbf temp  where temp.remark='save' and iyear=oiyear;
//查询更新的多少记录
 select count(*) into s_update from pj_tempajbf temp where temp.remark='update' and imonth=oimonth;

exception
when others then
rollback; /**/ /*异常处理，操作都不会被执行*/
return;
end PP_test;
 　　
　　java 使用jdbc调用存储过程

//调用存储过程
try {
Class.forName(driver);
conn = DriverManager.getConnection(url);
String sql="{ call PP_test(?,?) }";
CallableStatement call=conn.prepareCall(sql);
//储存过程为两个参数
call.setLong(3, 2011);
call.setLong(4, 8);
String[] s=new String[2];
call.registerOutParameter(5,java.sql.Types.VARCHAR);  
call.registerOutParameter(6,java.sql.Types.VARCHAR);  
call.execute();
//返回值
s[0]=call.getString(5);
s[1]=call.getString(6);
log.debug("执行成功:" + sql);
} catch (SQLException ex) {
log.error("数据库执行出错"+ex.getMessage());
throw ex;
}finally{
closeConnection(conn,pstmt,rs);
}


　　使用hibernate 基本一样

tx   =   session.beginTransaction();      
Connection   conn=session.connection();      
String sql="{ call PP_test(?,?) }";
CallableStatement call=conn.prepareCall(sql);
//储存过程为两个参数
call.setLong(3, 2011);
call.setLong(4, 8);
String[] s=new String[2];
call.registerOutParameter(5,java.sql.Types.VARCHAR);  
call.registerOutParameter(6,java.sql.Types.VARCHAR);  
call.executeUpdate();      
tx.commit();