2 tian602564031 tian602564031 于 2015.06.14 17:52 提问

并发调用oracle存储过程 5C

CREATE OR REPLACE PROCEDURE MAN_ACCOUNT_MRGREDATE_INSERT

(v_sellerid in varchar2,

v_professtype in varchar2,

v_plattype in varchar2,

v_margin in varchar2,

v_saleamount in varchar2,

v_priceamount in varchar2,

v_serviceCharge in varchar2,

v_creditDate in varchar2,

v_refundAmount in varchar2,

v_return_code out varchar2)

is

l_marginid man_act_margin_record.marginid%type;

l_count number;

--l_sellerid man_act_margin_record.sellerid%type;

begin

--v_return_code := '0000';

select count(*) into l_count from man_act_margin_record m

where m.credit_date = to_date(v_creditDate,'yyyy-MM-dd')

and m.sellerid = v_sellerid and m.professtype = v_professtype and m.plattype = v_plattype;

--记录已存在,直接返回入库成功

if l_count>0 then

delete from man_act_margin_record m

where m.credit_date = to_date(v_creditDate,'yyyy-MM-dd')

and m.sellerid = v_sellerid and m.professtype = v_professtype and m.plattype = v_plattype;

-- 生成主键

select 'M'||to_char(sysdate, 'yymmddhh24miss') ||

    trim(to_char(SEQ_MAN_ACT_MARGIN_RECORD.nextval, '000000')) 

into l_marginid

from dual;

insert into man_act_margin_record

(marginid, 

 sellerid, 

 professtype, 

 plattype, 

 margin, 

 saleamount, 

 priceamount, 

 service_charge, 

 credit_date, 

 create_date, 

 refund_amount) 

values

(l_marginid, 

 v_sellerid, 

 v_professtype, 

 v_plattype, 

 v_margin, 

 v_saleamount, 

 v_priceamount, 

 v_serviceCharge, 

 to_date(v_creditDate, 'yyyy-MM-dd'), 

sysdate, 

 v_refundAmount 

 ); 



 v_return_code := '0000'; 



else 



 -- 生成主键 

select 'M'||to_char(sysdate, 'yymmddhh24miss') ||

    trim(to_char(SEQ_MAN_ACT_MARGIN_RECORD.nextval, '000000')) 

into l_marginid

from dual;

insert into man_act_margin_record

(marginid, 

 sellerid, 

 professtype, 

 plattype, 

 margin, 

 saleamount, 

 priceamount, 

 service_charge, 

 credit_date, 

 create_date, 

 refund_amount) 

values

(l_marginid, 

 v_sellerid, 

 v_professtype, 

 v_plattype, 

 v_margin, 

 v_saleamount, 

 v_priceamount, 

 v_serviceCharge, 

 to_date(v_creditDate, 'yyyy-MM-dd'), 

sysdate, 

 v_refundAmount 

 ); 

v_return_code := '0000';

end if;

commit;

exception

when others then

v_return_code := '1111'; 

rollback;

raise;

end;

我在java代码中并发调用该存储过程,并用log4j打印调用日志,日志显示成功调用2566次,并成功插入到目标表中,但是在oracle实际查询时,数据量却少了100多条,而且每次执行程序,数据量少的数目都不一样。关键是,程序未报异常。

在我的程序中,嵌套两个多线程,第一个是生产者消费者模式(多线程),即同时生产出多个实例,消费者并发,在某个消费者中,又并发多个线程,去调用上面的存储过程。

通过日志初步判断,每个线程都进了存储过程,但是有些并没有执行完存储过程,并返回成功代码'0000',导致了上述的结果。

有oracle大牛,帮忙看看什么问题,不胜感激。

2个回答

weixin_29026511
weixin_29026511   2015.06.14 19:06

我看过了因该不会有问题

qq_16810521
qq_16810521   2015.06.15 15:06

看看是不是主键冲突导致没有插入进去,在Exception里面打印出错误,然后看看有没有报错误

Csdn user default icon
上传中...
上传图片
插入图片