Java调用Oracle、mysql带out参数的存储过程

create or replace package pkg_sxxtj as
 type sxxtj_out_values is ref cursor;
 procedure auditing_result(startDate in date,endDate in date,out_list out sxxtj_out_values);
end pkg_sxxtj;
****************************************************************************

 

create or replace package body pkg_sxxtj as
procedure auditing_result(startDate in date,endDate in date,out_list out sxxtj_out_values)
is
sqlStr varchar2(900);
begin
     if startDate is null and endDate is null then
     open out_list for
        select * from (select t.node_id as node_id, count(t.node_id) as total_count,
               sum(case when t.operate_status='0' then 1 else 0 end) as sh_count,
               sum(case when t.operate_status='1' then 1 else 0 end) as sy_count,
               sum(case when t.over_time > t.operate_date then 1 else 0 end) as js_count,
               sum(case when t.over_time < t.operate_date then 1 else 0 end) as cs_count
          from card_business_node t where t.node_id not in (0007, 0011) group by t.node_id
        union
        select s.sh_node_id as node_id, count(s.sh_node_id) as total_count,
               sum(case when s.operate_status='0' then 1 else 0 end) as sh_count,
               sum(case when s.operate_status='1' then 1 else 0 end) as sy_count,
               sum(case when s.over_time > s.operate_date then 1 else 0 end) as js_count,
               sum(case when s.over_time < s.operate_date then 1 else 0 end) as cs_count from card_business_sh_node s group by s.sh_node_id)  res;

  end if;
     end auditing_result;
     end pkg_sxxtj;

*************************************************************************

// 下面是java 源码，从oracle存储过程读取记录集
// 注意一定要用oracle的jdbc API ( 可以从oracle网站下载 )
public void test(){
  //Session session=getSession();
  Connection con=null;
  OracleCallableStatement cs=null;
  OracleResultSet rs=null;
  SimpleDateFormat sdf=new SimpleDateFormat("",Locale.SIMPLIFIED_CHINESE);
  sdf.applyPattern("yyyy年MM月dd日 HH时mm分ss秒");
  java.sql.Date sd=null;
   con=getSession().connection();
   try {
    cs=(OracleCallableStatement) con.prepareCall("{call pkg_sxxtj.auditing_result(?,?,?)}");
    cs.setDate(1, sd);
    cs.setDate(2, sd);
    cs.registerOutParameter(3,OracleTypes.CURSOR);
    //cs.registerOutParameter(3,java.sql.Types.OTHER);
    cs.execute();
    rs=(OracleResultSet) cs.getCursor(3);
    while(rs.next()){
     log.info("===================环节号："+String.valueOf(rs.getString(1)));
     log.info("||统计数："+String.valueOf(rs.getInt(2)));
    }
    
 
    
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }finally{
      try {
       if(cs!=null){
       cs.close();
       }
      } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
      }
      try {
       if(con!=null){
        con.close();
        }
      } catch (SQLException e) {
       // TODO: handle exception
       e.printStackTrace();
      }
   }
 }

 

二、mysql的调用

 

 

java调用mysql存储过程：
        String procedure = "{call nextid (?)}"; // 不能写为{?= call nextid (?)}
        CallableStatement cstmt = conn.prepareCall(procedure);
        cstmt.setString(1, "cprounit");

        cstmt.execute();
        ResultSet rs = cstmt.getResultSet();
        if (rs.next()) {
            result = rs.getInt(1);
        }

java调用myql函数的方法有两种：
1.     String procedure = "{?=call test.get_next_value (?)}";
       
        CallableStatement cstmt = conn.prepareCall(procedure);
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.setString(2, "user_id");

        cstmt.execute();
        result = cstmt.getInt(1);

2.     String procedure = "select test.get_next_value (?)";        
        CallableStatement cstmt = conn.prepareCall(procedure);
        cstmt.setString(1, "user_id");
        cstmt.execute();
        ResultSet rs = cstmt.getResultSet();
        if (rs.next()) {
            result = rs.getInt(1);
        }