那你表a的数据有没有经历过UPDATE操作呢? 如果都没有update操作过的痕迹的话,那有可能是你的sql有问题,这是我加入了错误处理代码的存储过程,你看看能不能找出问题 CREATE OR REPLACE PROCEDURE SP_A (htxh number,htfphm varchar2,sl1 number,sl2 number, fyje number,zbh varchar2,fgs varchar2)IS c int default 0; BEGIN select count(*) into c from a where htfphm_=htfphm and fgs_=fgs and htxh_=htxh and zbh_=zbh; if c<=0 then insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_) values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh); commit; else update a set sl1_=sl1_+sl1,sl2_=sl2_+sl2,fyje_=fyje_+fyje where htfphm_=htfphm and fgs_=fgs and htxh_=htxh and zbh_=zbh; commit;
end if; exception when others then rollback; err_code:=sqlcode; err_msg:=sqlerrm; insert into db_error values(err_code,err_msg,sysdate); commit; END;
A是我新建的表,空表一开始执行插入语句我把条件语句都去掉了,只执行一条insert语句结果表里面还是空的,如: CREATE OR REPLACE PROCEDURE SP_A (htxh number,htfphm varchar2,sl1 number,sl2 number, fyje number,zbh varchar2,fgs varchar2)IS c int default 0; BEGIN insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_) values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh); commit; END; 这样都不能向表里插入记录传过来的fgs等参数是有数值的,为什么?
反对用这种方法进行插入或更新,效率太低。 update a set sl1_=sl1_+sl1,sl2_=sl2_+sl2,fyje_=fyje_+fyje where htfphm_=htfphm and fgs_=fgs and htxh_=htxh ; IF SQL%NOTFOUND THEN insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_) values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh); End If ;commit;如果是在Oracle 9i里。用Merge语句效果会好的多。 Merge into a using (select htfphm , fgs , zbh ..... from dual) t1 on(a.htfphm_=t1.htfphm and a.fgs_=t1.fgs and a.htxh_=t1.htxh ) WHEN MATCHED THEN UPDATE ...... WHEN NOT MATCHED THEN INSERT ......
如果都没有update操作过的痕迹的话,那有可能是你的sql有问题,这是我加入了错误处理代码的存储过程,你看看能不能找出问题
CREATE OR REPLACE PROCEDURE SP_A (htxh
number,htfphm varchar2,sl1 number,sl2 number,
fyje number,zbh varchar2,fgs varchar2)IS
c int default 0;
BEGIN
select count(*) into c from a where
htfphm_=htfphm and fgs_=fgs and htxh_=htxh and zbh_=zbh;
if c<=0 then
insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_)
values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh);
commit;
else
update a set sl1_=sl1_+sl1,sl2_=sl2_+sl2,fyje_=fyje_+fyje
where htfphm_=htfphm and fgs_=fgs and htxh_=htxh and zbh_=zbh;
commit;
end if;
exception
when others then rollback;
err_code:=sqlcode;
err_msg:=sqlerrm;
insert into db_error
values(err_code,err_msg,sysdate);
commit;
END;
CREATE OR REPLACE PROCEDURE SP_A (htxh
number,htfphm varchar2,sl1 number,sl2 number,
fyje number,zbh varchar2,fgs varchar2)IS
c int default 0;
BEGIN
insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_)
values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh);
commit;
END;
这样都不能向表里插入记录传过来的fgs等参数是有数值的,为什么?
update a set sl1_=sl1_+sl1,sl2_=sl2_+sl2,fyje_=fyje_+fyje
where htfphm_=htfphm and fgs_=fgs and htxh_=htxh ;
IF SQL%NOTFOUND THEN
insert into a(fgs_,zbh_,fyje_,sl2_,sl1_,htfphm_,htxh_)
values(fgs,zbh,fyje,sl2,sl1,htfphm,htxh);
End If ;commit;如果是在Oracle 9i里。用Merge语句效果会好的多。
Merge into a
using (select htfphm , fgs , zbh ..... from dual) t1
on(a.htfphm_=t1.htfphm and a.fgs_=t1.fgs and a.htxh_=t1.htxh )
WHEN MATCHED THEN UPDATE ......
WHEN NOT MATCHED THEN INSERT ......