oracle存储过程java调用,请问错在哪里?

daishunchao 发布于 2012/04/20 13:07
阅读 450
收藏 1

依据roll_idrms_sort,从表中查询当前seq_id主键值,条件是box_status=0,如果有记录,取当前seq_id;如果没有,select SEQ_RMS_BOX.nextval  into newVal from dual;返回值为newVal(表示要生成一个新的seq_id)

 

我的存储过程是:

create or replace procedure Rms_Roll_getID(ROLL_ID  in NUMBER,
                                           RMS_SORT in VARCHAR2,
                                           newVal   out number) AS
begin
  select seq_id
    into newVal
    from RMS_BOX
   where BOX_STATUS = 0
     and ROLL_ID = ROLL_ID
     and RMS_SORT = RMS_SORT;
  IF newVal is null THEN
    select SEQ_RMS_BOX.nextval into newVal from dual;
  END IF;
end Rms_Roll_getID;

java调用是:

 

dbConn = this.getDbConn(request);
		    proc   =   dbConn.prepareCall( "{call Z6_NEW.Rms_Roll_getID(?,?,?)} "); 
		    proc.setInt(1,1);
		    proc.setString(2, "dai");
		    proc.registerOutParameter(3,java.sql.Types.INTEGER); 
            proc.execute();
           // int   a1=proc.getInt(3);
          //  System.out.println("a1"+a1);

但是提示ORA-01403: 未找到任何数据。本人对存储过程不熟悉,这个也是有点凑出来了。请多多指教。

加载中
0
sunny
sunny

存储过程的sql要扑捉异常啊!!!

0
sunny
sunny
你的if判断根本没有用
0
sunny
sunny
01 create or replace procedure Rms_Roll_getID(ROLL_ID  in NUMBER,
02                                            RMS_SORT in VARCHAR2,
03                                            newVal   out number) AS
04 begin
05   select seq_id
06     into newVal
07     from RMS_BOX
08    where BOX_STATUS = 0
09      and ROLL_ID = ROLL_ID
10      and RMS_SORT = RMS_SORT;
11   IF newVal is null THEN
12     select SEQ_RMS_BOX.nextval into newVal from dual;
13   END IF;
14    exception
15    when others then
          select SEQ_RMS_BOX.nextval into newVal from dual;
14 end Rms_Roll_getID;
0
狼狼A狗
狼狼A狗
是存储过程的问题,不是java调用的问题,如果select没记录返回,则要处理异常!
0
daishunchao
daishunchao
谢谢大家,我回好好去研究研究
返回顶部
顶部