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;  

问题解决，序列没跳号现象！