java 调用Oracle 存储过程 Jdbc/Hibernate

来源：互联网发布：中国基尼系数数据编辑：程序博客网时间：2024/04/29 12:28

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

#Oracle

0 0