c3p0 Oracle执行存储结束后,执行临时表(数据通过存储游标插入临时表),查询结果集是0条,如何解决执行存储后自动释放临时表数据?

Death_time 发布于 2016/12/23 14:57
阅读 174
收藏 0

这个是我自己另写的测试例子:

临时表:

create global temporary table IM_TX_TEST
(
  ID       NUMBER(20) default 0 not null,
  NAME     VARCHAR2(30) default ' ' not null,
  SFZH     VARCHAR2(20) default ' ' not null,
  PASSWORD VARCHAR2(30) default ' ' not null,
  TIME     DATE,
  BZ       VARCHAR2(40) default ' ' not null
)
存储:

CREATE OR REPLACE PROCEDURE p_im_tx_test(
  -- v_id varchar,
   v_name  varchar,
   v_sfzh varchar,
   v_password varchar,
   v_time  date,
   v_bz  varchar,
   v_isend smallint, --是否结束,1 结束 (最后一条传0,再传一条1,无其他数据项),0 未结束
   v_id in out decimal, --唯一id 批次号
   V_ret  in out smallint  --返回值  0成功,99 失败
)


as
v_id1 decimal(20);
v_name1  varchar(30);
v_sfzh1  varchar(20);
v_password1 varchar(20);
v_time1 varchar(30);
v_bz1  varchar(30);


cursor cur_fx is select id,name,sfzh,password,time,bz from im_tx_test
    where id=v_id order by id for update ;




begin
v_ret:=0;
if v_id=0 then
  v_id := f_newid;
end if;


if v_isend =0 then
  insert into im_tx_test(id,name,sfzh,password,time,bz)
  values(f_newid,v_name,v_sfzh,v_password,v_time,v_bz);
commit;
end if;


if v_isend=1 then
    open cur_fx;
    loop
    fetch cur_fx into v_id1,v_name1,v_sfzh1,v_password1,v_time1,v_bz1;
    exit when cur_fx%notfound;


    insert into im_tx_test(id,name,sfzh,password,time,bz)
    values(v_id1,v_name1,v_sfzh1,v_password1,v_time1,v_bz1);


    end loop;
end  if ;


commit;


end;


java  代码:

public static int getResultSetByProcedure(Object obj) throws ParseException{
UserDto[] txlist=(UserDto[])obj;
 CallableStatement cst = null;
 ResultSet rs = null;
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
 long id=0;
 int  ret = 99;
 try {
conn = DBC3p0Builder.getConnection();
 String sCmd = "{call  p_im_tx_test(?,?,?,?,?,?,?,?)}";
  cst=conn.prepareCall(sCmd);
  System.out.println("---------conn----->"+conn);
  for(int i=0;i<txlist.length;i++){
  if(i==txlist.length){
  cst.setString(1, txlist[0].getName());
  cst.setString(2, txlist[0].getSfzh());
  cst.setString(3, txlist[0].getPassword());
  cst.setTimestamp(4, txlist[0].getTime());
  cst.setString(5, txlist[0].getBz());
  cst.setInt(6, 1);
  }else{
  cst.setString(1, txlist[i].getName());
  cst.setString(2, txlist[i].getSfzh());
  cst.setString(3, txlist[i].getPassword());
  cst.setTimestamp(4, txlist[i].getTime());
  cst.setString(5, txlist[i].getBz());
  cst.setInt(6, 0);
  }
  
  cst.registerOutParameter(7, java.sql.Types.DOUBLE);
  cst.registerOutParameter(8, java.sql.Types.INTEGER);
//执行存储过程
  cst.execute();
  ret = cst.getInt(8);
  }
  String sql ="select id bm,name mc,time rq from IM_TX_TEST";
 /* st =   conn.createStatement();
  rs = st.executeQuery(sql);*/
  
  ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
  
     //rs = ps.executeQuery();
  if(rs.next()){
  System.out.println("<-------------->"+(rs.getString("bm")+"<------------->"+rs.getString("mc")+"<------------->"+rs.getString("rq")+"<------------->"+rs.getString("a001")));
  }
 //return null;
 }catch (SQLException e) { 
 ret = 99;
System.out.println("执行sql出错  sql【】"+e.getMessage());
}
 
 return ret;
 }

c3p0的配置就按网上标准配置的

麻烦各位大神看一眼,解决下

从网上搜索来看:说是:连接池本身对于临时表的机制不同,com.mchange.v2.c3p0.ComboPooledDataSource应该是在存储过程里面创建临时表,存储过程执行完就自动将临时表销毁了


加载中
返回顶部
顶部