java 调用Oracle 存储过程 Jdbc/Hibernate

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