Oracle数据库MergeInto-如何使用Sequence而不浪费1、问题背景：该问题是在做DataStage时引出来的一个问题，必须使用MergeInto的时候会浪费很多NextVal。www.2cto.com2、测试前提：创建脚本。Sql代码...SyntaxHighli
Oracle
数据库Merge Into-如何使用Sequence而不“浪费”
1、问题背景：该问题是在做Data Stage时引出来的一个问题，必须使用Merge Into的时候会浪费很多NextVal。 www.2cto.com
2、测试前提：创建脚本。
Sql代码
-- 创建源表
create table sourceTest(
id int,
name varchar2(100),
seq_value int,
t_stamp date
);
-- 创建目标表
create table targetTest(
id int,
name varchar2(100),
seq_value int,
t_stamp date
);
-- 创建sequence
create sequence seq_test
minvalue 1
maxvalue 999999
increment by 1
cache 40
nocycle;
-- 添加测试数据
insert into sourceTest(id,name,t_Stamp) values(null,null,sysdate);
insert into sourceTest(id,name,t_Stamp) values(1,&＃39;one&＃39;,sysdate - 1);
insert into sourceTest(id,name,t_Stamp) values(2,&＃39;two&＃39;,sysdate - 2);
insert into sourceTest(id,name,t_Stamp) values(3,null,sysdate - 3);
insert into sourceTest(id,name,t_Stamp) values(null,&＃39;four&＃39;,sysdate + 1);
commit;
3、测试
Sql代码
merge into targetTest t
using (select id, name,seq_value,t_stamp from sourceTest) s
on (s.id = t.id and s.name = t.name)
when matched then
update set t.seq_value = s.seq_value, t.t_stamp = sysdate
when not matched then
insert (id, name, seq_value,t_stamp) values (s.id, s.name,test.seq_test.nextval,s.t_stamp);
第一次执行，结果：
ID NAME SEQ_VALUE T_STAMP
1 one 1 2012-11-17 下午 03:11:46
2 2 2012-11-18 下午 03:11:46
3 3 3 2012-11-15 下午 03:11:46
4 four 4 2012-11-19 下午 03:11:46
5 2 two 5 2012-11-16 下午 03:11:46
第二次执行，结果：
ID NAME SEQ_VALUE T_STAMP
1 1 one 2012-11-18 下午 03:20:28
2 2 2012-11-18 下午 03:11:46
3 3 3 2012-11-15 下午 03:11:46
4 four 4 2012-11-19 下午 03:11:46
5 2 two 2012-11-18 下午 03:20:28
6 8 2012-11-18 下午 03:11:46
7 3 9 2012-11-15 下午 03:11:46
8 four 10 2012-11-19 下午 03:11:46
从上面的结果，我们可以非常清晰的看到6和7那里去了？--浪费掉了。
4、解决。创建一个函数来获取相应的值。
Sql代码
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 &＃39;select &＃39; || f_schema || &＃39;.&＃39;||f_sequence_name||&＃39;.nextval from dual&＃39; into v_nextval;
return v_nextval;
exception
when others then
raise_application_error(sqlcode,sqlerrm);
end;
将3测试中的代码调整为：test.seq_test.nextval ==> test.get_sequence_nextval(&＃39;test&＃39;,&＃39;seq_test&＃39;)
再次调用，我们可以看到三条记录11、12、13已经紧跟着10了。--不浪费了。
ID NAME SEQ_VALUE T_STAMP
1 1 one 2012-11-18 下午 03:28:35
2 2 2012-11-18 下午 03:11:46
3 3 3 2012-11-15 下午 03:11:46
4 four 4 2012-11-19 下午 03:11:46
5 2 two 2012-11-18 下午 03:28:35
6 8 2012-11-18 下午 03:11:46
7 3 9 2012-11-15 下午 03:11:46
8 four 10 2012-11-19 下午 03:11:46
9 11 2012-11-18 下午 03:11:46
10 3 12 2012-11-15 下午 03:11:46
11 four 13 2012-11-19 下午 03:11:46
5、结论及分析：
使用Merge Into调用Sequence 类似于预编译，会直接将值赋组合相应的调用，而不管该matched 或 not matched是否成功执行。而使用一个function进行封闭以后，因为预编译时无法知道该值，所以不会进行预处理，因此也不会浪费sequence的值。但是，使用function来讲，会带来另外一个问题，性能因为无端的增加了一下调用。
所以，根据自己的实际情况，选择适合自己的才是最好的。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 
