你的位置:首页 > 数据库

[数据库]转:Oracle中merge into的使用


  最近项目上使用Oracle的Merge,所以找来一下资料学习了解。

  该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. ORACLE 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。

  特点:

  1、insert 和update是可选的 ;  

  2、UPDATE 和INSERT 后面可以跟WHERE 子句 ;

  3、在ON条件中可以使用常量来insert 所有的行到目标表中,不需要连接到源表和目标表 ;

  4、UPDATE 子句后面可以跟delete 来去除一些不需要的行。

  举例:

 1  create table PRODUCTS   2   (   3   PRODUCT_ID INTEGER,   4   PRODUCT_NAME VARCHAR2(60),   5   CATEGORY VARCHAR2(60)   6   );   7   8   insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');   9   insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');   10   insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');   11   insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');   12   insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');   13   commit;   14   15   create table NEWPRODUCTS   16   (   17   PRODUCT_ID INTEGER,   18   PRODUCT_NAME VARCHAR2(60),   19   CATEGORY VARCHAR2(60)   20   );   21   22   insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');   23   insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');   24   insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');   25   insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');   26   commit;   27 1,可省略的update 或者insert 28    MERGE INTO products p   29   2 USING newproducts np   30   3 ON (p.product_id = np.product_id)   31   4 WHEN MATCHED THEN  32   5 UPDATE  33   6 SET p.product_name = np.product_name,   34   7 p.category = np.category; 35  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category. 36  37 2,当条件不满足的时候把newproducts表中的数据INSERT 到表products中。 38  39   MERGE INTO products p   40   USING newproducts np   41   ON (p.product_id = np.product_id)   42   WHEN NOT MATCHED THEN  43   INSERT  44   VALUES (np.product_id, np.product_name,   45    np.category);   46 3,带条件的insert 和update 47  48 MERGE INTO products p   49  USING newproducts np   50  ON (p.product_id = np.product_id)   51  WHEN MATCHED THEN  52  UPDATE  53  SET p.product_name = np.product_name   54  WHERE p.category = np.category; 55 insert 和update 都带有where 字句 56  57  58  59 MERGE INTO products p   60  USING newproducts np   61   ON (p.product_id = np.product_id)   62   WHEN MATCHED THEN  63   UPDATE  64   SET p.product_name = np.product_name,   65   p.category = np.category   66   WHERE p.category = 'DVD'  67   WHEN NOT MATCHED THEN  68   INSERT  69   VALUES (np.product_id, np.product_name, np.category)   70   WHERE np.category != 'BOOKS'  71 4,无条件的insert 72  73 MERGE INTO products p   74  USING newproducts np   75  ON (1=0)   76  WHEN NOT MATCHED THEN  77  INSERT  78  VALUES (np.product_id, np.product_name, np.category)   79  WHERE np.category = 'BOOKS'  80 5,delete 子句 81  82 1 merge into products p 83  2 using newproducts np 84  3 on(p.product_id = np.product_id) 85  4 when matched then 86  5 update 87  6 set p.product_name = np.product_name 88  7 delete where category = 'macle1_cate'; 89  90 select * 91  92 from products; 93  94  PRODUCT_ID PRODUCT_NAME     CATEGORY 95 --------------------------------------- -------------------- -------------------- 96                  1502 macle22       macle2_cate 97                  1503 macle3        macle2_cate 98                  1504 macle         macle1_cate 99                  1505 macle5        macle5_cate100 101 1504 中的macle1_cate 满足delete where,但是不满足 on 中的条件,所以没有被删除。!!!!!!

  在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。

  Merge格式:

1 MERGE INTO table_name alias1 2 USING (table|view|sub_query) alias23 ON (join condition) 4 WHEN MATCHED THEN 5   UPDATE table_name 6   SET col1 = col_val1, 7     col2   = col2_val 8 WHEN NOT MATCHED THEN 9   INSERT (column_list) VALUES (column_values);

  用中文来解释Merge语法,就是:

  在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。