Oracle基础——merge into用法详解

WHY?

    想在Oracle中用一条SQL语句直接进行insert/update操作。

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。相当于MSSQL中的if exists(......)update table else insert table

用法

    MERGE [INTO [schema .] table [t_alias]
    USING [schema .] { table | view | subquery } [t_alias]
    ON ( condition )
    WHEN MATCHED THEN merge_update_clause
    WHEN NOT MATCHED THEN merge_insert_clause;

实例

    创建测试数据表：
　　　　create table tj_test(id number,name varchar2(20),age number);
　　　　向表中插入数据：
　　　　insert into tj_test values (1,'jan',23);
　　　　insert into tj_test values (2,'kk',22);
　　　　insert into tj_test values (3,'joe',27);
　　　　select * from tj_test;

              查询结果如下：
　　　　1 jan 23
　　　　2 kk 22
　　　　3 joe 27
　　　　创建另一新表
　　　　create table tj_test1 as select * from tj_test where 1=0
　　　　插入一条数据
　　　　insert into tj_test1 values (1,'jlk',23);
　　　　select * from tj_test1
　　　　查询结果如下：
　　　　1 jkl 23 --注意，这里的的NAME字段中的值是jkl

        使用MERGE,实现有则更新，无则插入，sql语句如下：
　　　　merge into tj_test1 tt1
　　　　using tj_test tt
　　　　on (tt1.id=tt.id)
　　　　when matched then
　　　　update set
　　　　tt1.name=tt.name,
　　　　tt1.age=tt.age
　　　　when not matched then
　　　　insert values(
　　　　tt.id,
　　　　tt.name,
　　　　tt.age)
　　　　查询tj_test1表(对比原来表中的数据，更新了ID＝1 ROW中字段NAME，同时多出两条新数据)
　　　　select * from tj_test1

        改变行数据如下：
　　　　1 jan 23 --这里的原有jkl值被更新
　　　　3 joe 27 --原来表中没有的插入
　　　　2 kk 22 --原来表中没有的插入
　　　　如果存在就更新，不存在就插入
　　　　9i已经支持了，是Merge，但是只支持select子查询，
　　　　如果是单条数据记录，可以写作select …… from dual的子查询。
　　　　语法为：
　　　　MERGE INTO table
　　　　USING data_source
　　　　ON (condition)
　　　　WHEN MATCHED THEN update_clause
　　　　WHEN NOT MATCHED THEN insert_clause;

        如:
　　　　MERGE INTO course c
　　　　USING (SELECT course_name, period,
　　　　course_hours
　　　　FROM course_updates) cu
　　　　ON (c.course_name = cu.course_name
　　　　AND c.period = cu.period)
　　　　WHEN MATCHED THEN
　　　　UPDATE
　　　　SET c.course_hours = cu.course_hours
　　　　WHEN NOT MATCHED THEN
　　　　INSERT (c.course_name, c.period,
　　　　c.course_hours)
　　　　VALUES (cu.course_name, cu.period,
　　　　cu.course_hours);