Merge into引用序列的限制

遇到现象:
该PLSQL块服务运行正常,但表S_VipShopStock的主建出现严重跳号,运行第一次max(id)为600,下一次执行id为1200,经过重建序列并且把cache参数改为空也不能解决问题。

原服务(2秒执行一次):

begin
merge into mbs7_oms.XS_VipShopStock a using
(Select
c.stylecode,
c.warecode,
sum(b.amount) qty,
0,
c.websitecode,
33
From mbs7_oms.cg_batch a
Join mbs7_oms.cg_batchsub b
join mbs7_oms.xs_ware c
on b.warecode = c.warecode
and c.prodlinecode = '33' On a.batchcode = b.batchcode
Where b.amount > 0
And a.status In (0, 1)
And a.arrivaldate > Sysdate
group by c.stylecode, c.warecode, c.websitecode) b
on(a.warecode=b.warecode)
WHEN MATCHED THEN 
update set a.stockqty=b.qty where a.stockqty<>b.qty
WHEN NOT MATCHED THEN
INSERT values(SEQ_XS_VipShopStock.Nextval,b.stylecode,b.warecode,b.qty,0,b.stylecode,33);
commit;

查看原序列的定义:

create sequence SEQ_XS_VIPSHOPSTOCK
minvalue 1
maxvalue 1000000000000000
start with 1
increment by 1
cache 20

后来改成:

create sequence SEQ_XS_VIPSHOPSTOCK
minvalue 1
maxvalue 1000000000000000
start with 1
increment by 1
nocache
order;

问题依旧!

分析:
使用Merge Into调用Sequence 类似于预编译,会直接将值赋组合相应的调用,而不管该matched 或 not matched是否成功执行。而使用 一个function进行封闭以后,因为预编译时无法知道该值,所以不会进行预处理,因此也不会浪费sequence的值。但是,使用function来 讲,会带来另外一个问题,性能因为无端的增加了一下调用。所以,根据自己的实际情况,选择适合自己的才是最好的。

解决方案:
创建序列号传递函数:

create or replace function get_sequence_nextval(f_schema in varchar2, f_sequence_name in  varchar2) return number  is  
  v_nextval         number;  
begin  
  execute immediate 'select ' || f_schema || '.'||f_sequence_name||'.nextval from dual' into v_nextval;  
 return v_nextval;  
exception  
  when others then  
   raise_application_error(sqlcode,sqlerrm);  
end;  

更改服务引用函数:

begin
    merge into mbs7_oms.XS_VipShopStock a using
     (Select
           c.stylecode,
           c.warecode,
           sum(b.amount) qty,
           0,
           c.websitecode,
           33
      From mbs7_oms.cg_batch a
      Join mbs7_oms.cg_batchsub b
      join mbs7_oms.xs_ware c
        on b.warecode = c.warecode
       and c.prodlinecode = '33' On a.batchcode = b.batchcode
     Where b.amount > 0
       And a.status In (0, 1)
       And a.arrivaldate > Sysdate
     group by  c.stylecode, c.warecode, c.websitecode) b
     on(a.warecode=b.warecode)
     WHEN  MATCHED THEN
        update set a.stockqty=b.qty where a.stockqty<>b.qty
      WHEN  NOT MATCHED THEN
        INSERT  values(get_sequence_nextval('MBS7_OMS','SEQ_XS_VIPSHOPSTOCK'),b.stylecode,b.warecode,b.qty,0,b.websitecode,33);
    commit;  

问题解决,序列没跳号现象!


更多DBA案例更新,请关注我们CSDN博客!
https://topdbs.blog.csdn.net