java调用oracle函数存储过程

来源：互联网发布：ftp 21端口不通编辑：程序博客网时间：2024/05/12 02:23

package com.dada.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

public class Service {

//测试调用oracle的函数
public int fun_add() {
System.out.print("调用到了此方法");

int flag = 0;

Connection conn = DbManager.getConnection();
ResultSet rs = null;
CallableStatement pstmt = null;

try {
//调用存储过程
pstmt = conn.prepareCall("{?=call pkg_test.fadd(?,?)}");

pstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.INTEGER);
pstmt.setInt(2, 8);
pstmt.setInt(3, 9);

pstmt.execute();
flag=pstmt.getInt(1);

System.out.print("存储过程计算的结果是："+flag);

} catch (Exception e) {
// e.printStackTrace();
} finally {
try {

DbManager.closeAll(rs, pstmt, conn);

} catch (Exception e) {
}
}
return flag;
}

//测试调用oracle的存储过程
public int pro_add() {
System.out.print("调用到了此方法");

int flag = 0;

Connection conn = DbManager.getConnection();
ResultSet rs = null;
CallableStatement pstmt = null;

try {
//调用存储过程
pstmt = conn.prepareCall("{ call pkg_test.padd(?,?,?)}");

pstmt.setInt(1, 1);
pstmt.setInt(2, 5);
pstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);

pstmt.execute();

flag = (int) pstmt.getInt(3);
System.out.print("存储过程计算的结果是："+flag);

} catch (Exception e) {
// e.printStackTrace();
} finally {
try {

DbManager.closeAll(rs, pstmt, conn);

} catch (Exception e) {
}
}
return flag;
}

// 测试调用oracle的存储过程
public void pro_add1() {

  Connection conn = init_oracle();
  ResultSet rs = null;
  CallableStatement pstmt = null;

  try {
   // 调用存储过程-获取ResultSet值
   pstmt = conn
     .prepareCall("{ call pkg_query.query_repeat(?,?,?,?,?,?)}");
   // query_hour (v_date varchar2,beginTime in number,endTime in
   // number,mac_addr varchar2,pkg_id number,r OUT query_result)

   pstmt.setString(1, "2012-11-20");
   pstmt.setInt(2, 0);
   pstmt.setInt(3, 20);
   pstmt.setString(4, "01-20-00-00-05");
   pstmt.setInt(5, 1758);
   pstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
   pstmt.executeQuery();
   rs = (ResultSet) pstmt.getObject(6);

   while (rs.next()) {
    int dat_id = rs.getInt(1);
    System.out.println(dat_id);
   }

  } catch (Exception e) {
   // e.printStackTrace();
  }
}

}