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();