MERGE 使用例子

--MERGE <Usage Case>

--Prepare the temp table to test
DROP TABLE wl_merge_tb1;
DROP TABLE wl_merge_tb2;
CREATE TABLE wl_merge_tb1 (tb_id NUMBER, tb_name VARCHAR2 (10));
CREATE TABLE wl_merge_tb2 (tb_id NUMBER, tb_name VARCHAR2 (10));

--Prepare the temp data to test
--The target table data
INSERT INTO wl_merge_tb1 VALUES (1,'a1');
INSERT INTO wl_merge_tb1 VALUES (2,'a2');
INSERT INTO wl_merge_tb1 VALUES (3,'a3');
INSERT INTO wl_merge_tb1 VALUES (4,'a4');
INSERT INTO wl_merge_tb1 VALUES (5,'a5');
--The source table data
INSERT INTO wl_merge_tb2 VALUES (1,'b1');
INSERT INTO wl_merge_tb2 VALUES (2,'b2');
INSERT INTO wl_merge_tb2 VALUES (3,'b3');
INSERT INTO wl_merge_tb2 VALUES (4,'b4');
INSERT INTO wl_merge_tb2 VALUES (6,'b6');
COMMIT;
--The MERGE clause
MERGE INTO wl_merge_tb1 t1
--Declare the data source
USING ( SELECT t.tb_id
             , t.tb_name
        FROM wl_merge_tb2 t
      ) t2
   --Set the relationship between the source and target table
   ON ( t1.tb_id = t2.tb_id ) 
 --The matched data of source and target table
 WHEN MATCHED THEN
     --Update the target table use the source table data
     UPDATE SET t1.tb_name = 'update'||t2.tb_name
      --Set the action execute condition
      WHERE ( t1.tb_id < 5 )
     --Delete the record of the matched data under the 'WHERE' condition
     DELETE WHERE ( t1.tb_id > 2 )
 --The not matched data in source data table
 WHEN NOT MATCHED THEN
     --Insert the source record to the target table,alse you can set 'WHERE' condition
     INSERT ( t1.tb_id, t1.tb_name )
     VALUES ( t2.tb_id, 'insert' || t2.tb_name );
COMMIT;
    
SELECT * FROM wl_merge_tb1;
SELECT * FROM wl_merge_tb2;

--Target table data
1    updateb1
2    updateb2
5    a5
6    insertb6

--Source table data
1    b1
2    b2
3    b3
4    b4
6    b6