Oracle Database Merge Into-how to use Sequence without "waste"

Oracle Database Merge Into-how to use Sequence instead of "waste" 1. Problem Background: this problem is a problem introduced during Data Stage, the use of Merge Into is a waste of NextVal. Www.2cto.com 2. Test prerequisites: Create a script. SQL code -- create source table create table sourceTest (id int, name varchar2 (100), seq_value int, t_stamp date); -- create target table create table targetTest (id int, name varchar2 (100), seq_value int, t_stamp date); -- create sequence seq_test minvalue 1 maxvalue 999999 increment by 1 cache 40 nocycle; -- add test data insert into sourceTest (id, name, t_Stamp) values (null, null, sysdate); insert into sourceTest (id, name, t_S Tamp) values (1, 'one', sysdate-1); insert into sourceTest (id, name, t_Stamp) values (2, 'two', sysdate-2 ); insert into sourceTest (id, name, t_Stamp) values (3, null, sysdate-3); insert into sourceTest (id, name, t_Stamp) values (null, 'four ', sysdate + 1); commit; 3. Test the SQL code 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 N 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); first execution result: id name SEQ_VALUE T_STAMP1 one 1 462 2: 464 four 4 2012-11-19 03:11:46 2nd execution result: id name SEQ_VALUE T_STAMP1 1 one 2 012-11-18 pm 03: 20: 282 2 2012-11-18 PM 03: 11: 463 3 3 2012-11-15 PM 03: 11: 464 four 4 2012-11-19 PM 03: 11: 465 2 two 2012-11-18 PM 03: 20: 286 8 2012-11-18 PM 03: 11: 467 3 9 2012-11-15 PM 03: 11: 468 four 10 2012-11-19 03:11:46 from the above results, we can clearly see that 6 and 7 have gone there? -- Wasted. 4. solution. Create a function to obtain the corresponding value. SQL code 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; adjusted the code in test 3 to test. seq_test.nextval ==> test. get_sequence_nextv Al ('test', 'seq _ test') is called again. We can see that three records 11, 12, and 13 have followed 10. -- No waste. Id name SEQ_VALUE T_STAMP1 1 one 2012-11-18 PM 352 2 2012-11-18 PM 03: 11: 463 3 3 2012-11-15 PM 03: 11: 464 four 4 2012-11-19 PM 03: 11: 465 2 two 2012-11-18 8 2012-11-18 PM 03: 11: 467 3 9 2012-11-15 PM 03: 11: 468 four 10 2012-11-19 PM 03: 11: 469 11 2012-11-18: 4610 3 12 03:11:46 PM 03: 11: 4611 four 13 PM 5. Conclusion and analysis: Using Merge Into to call Sequence is similar to pre-compilation, and values are directly assigned to corresponding calls, while Whether or not the matched or not matched is successfully executed. After a function is used for blocking, the value cannot be known during pre-compilation, so preprocessing is not performed and the sequence value is not wasted. However, using function brings about another problem, because the performance increases the number of calls without reason. Therefore, the best choice is based on your actual situation.