Oracle Merge 语言使用

Ne0inhk

— 6 min read
Oracle Merge 语言使用

动机：

想在Oracle中用一条SQL语句直接进行Insert/Update的操作。

说明：

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

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

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

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

语法:

MERGEHINTINTOSCHEMA . TABLE T_ALIAS

USINGSCHEMA . {TABLE |VIEW |SUBQUERY } T_ALIAS

ON (CONDITION)

WHENMATCHEDTHEN MERGE_UPDATE_CLAUSE

WHENNOTMATCHEDTHEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

联想:
mergeinto是特有的功能，相当于在 MSSQL中的

ifexists(...)

updatetable

else

Insertinto table.

mergeinto语法不仅没有if exists语法啰嗦，而且比if exists 还要高效很多，常用来在oracle之间同步数据库表。

例子：

1、 创建测试表及数据

[c-sharp]

  1. DROP TABLE PRODUCTS;
  2. DROP TABLE NEWPRODUCTS;
  3. create table PRODUCTS
  4. (
  5. PRODUCT_ID INTEGER,
  6. PRODUCT_NAME VARCHAR2(60),
  7. CATEGORY VARCHAR2(60)
  8. );
  9. insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
  10. insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
  11. insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
  12. insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
  13. insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
  14. commit;
  15. create table NEWPRODUCTS
  16. (
  17. PRODUCT_ID INTEGER,
  18. PRODUCT_NAME VARCHAR2(60),
  19. CATEGORY VARCHAR2(60)
  20. );
  21. insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
  22. insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
  23. insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
  24. insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
  25. commit;

DROP TABLE PRODUCTS;DROP TABLE NEWPRODUCTS;create table PRODUCTS(PRODUCT_ID INTEGER,PRODUCT_NAME VARCHAR2(60),CATEGORY VARCHAR2(60));insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');commit;create table NEWPRODUCTS(PRODUCT_ID INTEGER,PRODUCT_NAME VARCHAR2(60),CATEGORY VARCHAR2(60));insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');commit;

2、 匹配更新

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  4. WHEN MATCHED THEN
  5. UPDATE
  6. SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  7. P.CATEGORY     = NP.CATEGORY;
  9. SELECT * FROM PRODUCTS;
  10. SELECT * FROM NEWPRODUCTS;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN  UPDATE     SET P.PRODUCT_NAME = NP.PRODUCT_NAME,         P.CATEGORY     = NP.CATEGORY;SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

3、 不匹配插入

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  4. WHEN NOT MATCHED THEN
  5. INSERT
  6. (PRODUCT_ID
  7. ,PRODUCT_NAME
  8. ,CATEGORY)
  9. VALUES
  10. (NP.PRODUCT_ID
  11. ,NP.PRODUCT_NAME
  12. ,NP.CATEGORY);
  14. SELECT * FROM PRODUCTS;
  15. SELECT * FROM NEWPRODUCTS;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (NP.PRODUCT_ID    ,NP.PRODUCT_NAME    ,NP.CATEGORY);SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

4、 匹配带where/on更新

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  4. WHEN MATCHED THEN
  5. UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;
  7. MERGE INTO PRODUCTS P
  8. USING NEWPRODUCTS NP
  9. ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)
  10. WHEN MATCHED THEN
  11. UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;
  13. SELECT * FROM PRODUCTS;
  14. SELECT * FROM NEWPRODUCTS;
  16. SELECT *
  17. FROM PRODUCTS A
  18. INNER JOIN NEWPRODUCTS B
  19. ON A.PRODUCT_ID = B.PRODUCT_ID
  20. AND A.CATEGORY = B.CATEGORY;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)WHEN MATCHED THEN  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;SELECT *  FROM PRODUCTS A INNER JOIN NEWPRODUCTS B    ON A.PRODUCT_ID = B.PRODUCT_ID   AND A.CATEGORY = B.CATEGORY;

5、 匹配带where更新、插入

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  4. WHEN MATCHED THEN
  5. UPDATE
  6. SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  7. P.CATEGORY     = NP.CATEGORY
  8. WHERE P.CATEGORY = 'DVD'
  9. WHEN NOT MATCHED THEN
  10. INSERT
  11. (PRODUCT_ID
  12. ,PRODUCT_NAME
  13. ,CATEGORY)
  14. VALUES
  15. (NP.PRODUCT_ID
  16. ,NP.PRODUCT_NAME
  17. ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';
  19. SELECT * FROM PRODUCTS;
  20. SELECT * FROM NEWPRODUCTS;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN  UPDATE     SET P.PRODUCT_NAME = NP.PRODUCT_NAME,         P.CATEGORY     = NP.CATEGORY   WHERE P.CATEGORY = 'DVD'WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (NP.PRODUCT_ID    ,NP.PRODUCT_NAME    ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

6、 ON常量表达式

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (1 = 0)
  4. WHEN NOT MATCHED THEN
  5. INSERT
  6. (PRODUCT_ID
  7. ,PRODUCT_NAME
  8. ,CATEGORY)
  9. VALUES
  10. (NP.PRODUCT_ID
  11. ,NP.PRODUCT_NAME
  12. ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';
  14. SELECT * FROM PRODUCTS;
  15. SELECT * FROM NEWPRODUCTS;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (1 = 0)WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (NP.PRODUCT_ID    ,NP.PRODUCT_NAME    ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

7、 匹配删除、不匹配插入

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING NEWPRODUCTS NP
  3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  4. WHEN MATCHED THEN
  5. UPDATE
  6. SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  7. P.CATEGORY     = NP.CATEGORY DELETE
  8. WHERE (P.CATEGORY = 'ELECTRNCS')
  9. WHEN NOT MATCHED THEN
  10. INSERT
  11. (PRODUCT_ID
  12. ,PRODUCT_NAME
  13. ,CATEGORY)
  14. VALUES
  15. (NP.PRODUCT_ID
  16. ,NP.PRODUCT_NAME
  17. ,NP.CATEGORY);
  19. SELECT * FROM PRODUCTS;
  20. SELECT * FROM NEWPRODUCTS;

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN  UPDATE     SET P.PRODUCT_NAME = NP.PRODUCT_NAME,         P.CATEGORY     = NP.CATEGORY DELETE   WHERE (P.CATEGORY = 'ELECTRNCS')WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (NP.PRODUCT_ID    ,NP.PRODUCT_NAME    ,NP.CATEGORY);SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

8、 源表为子查询(自联接)

[c-sharp]

  1. MERGE INTO PRODUCTS P
  2. USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B
  3. ON (B.CO <> 0)
  4. WHEN MATCHED THEN
  5. UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501
  6. WHEN NOT MATCHED THEN
  7. INSERT
  8. (PRODUCT_ID
  9. ,PRODUCT_NAME
  10. ,CATEGORY)
  11. VALUES
  12. (1501
  13. ,'KEBO'
  14. ,'NBA');
  16. MERGE INTO PRODUCTS P
  17. USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B
  18. ON (B.CO <> 0)
  19. WHEN MATCHED THEN
  20. UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508
  21. WHEN NOT MATCHED THEN
  22. INSERT
  23. (PRODUCT_ID
  24. ,PRODUCT_NAME
  25. ,CATEGORY)
  26. VALUES
  27. (1508
  28. ,'KEBO'
  29. ,'NBA');
  31. SELECT * FROM PRODUCTS;

MERGE INTO PRODUCTS PUSING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) BON (B.CO <> 0)WHEN MATCHED THEN  UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (1501    ,'KEBO'    ,'NBA');MERGE INTO PRODUCTS PUSING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) BON (B.CO <> 0)WHEN MATCHED THEN  UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508WHEN NOT MATCHED THEN  INSERT    (PRODUCT_ID    ,PRODUCT_NAME    ,CATEGORY)  VALUES    (1508    ,'KEBO'    ,'NBA');SELECT * FROM PRODUCTS;

优点：

— 避免了分开更新

— 提高性能并易于使用

— 在数据仓库应用中十分有用

— 使用merge比传统的先判断再选择插入或更新快很多

需要注意的地方：

1、从语法条件上看（ON (join condition) ），merge into也是一个危险的语法。如果不慎重使用，会将源表全部覆盖到目标表，既危险又浪费效率，违背了增量同步的原则。所以在设计表结构时，一般每条记录都有“更新时间”的字段，用目标表“最大更新时间”判断源表数据是否有更新和新增的信息。

2、更新的字段，不允许有关联条件的字段（join condition）。比如条件是 A.ID=B.ID，那么使用“SET A.ID=B.ID”将报出一个莫名其妙的提示错误。

Read more

深入理解 Proxy 和 Object.defineProperty

在JavaScript中，对象是一种核心的数据结构，而对对象的操作也是开发中经常遇到的任务。在这个过程中，我们经常会使用到两个重要的特性：Proxy和Object.defineProperty。这两者都允许我们在对象上进行拦截和自定义操作，但它们在实现方式、应用场景和灵活性等方面存在一些显著的区别。本文将深入比较Proxy和Object.defineProperty，包括它们的基本概念、使用示例以及适用场景，以帮助读者更好地理解和运用这两个特性。 1. Object.defineProperty 1.1 基本概念 Object.defineProperty 是 ECMAScript 5 引入的一个方法，用于直接在对象上定义新属性或修改已有属性。它的基本语法如下： javascript 代码解读复制代码Object.defineProperty(obj, prop, descriptor); 其中，obj是目标对象，prop是要定义或修改的属性名，descriptor是一个描述符对象，用于定义属性的特性。 1.2 使用示例 javascript 代码解读复制代码//

By Ne0inhk

Proxy 和 Object.defineProperty 的区别

Proxy 和 Object.defineProperty 是 JavaScript 中两个不同的特性，它们的作用也不完全相同。 Object.defineProperty 允许你在一个对象上定义一个新属性或者修改一个已有属性。通过这个方法你可以精确地定义属性的特征，比如它是否可写、可枚举、可配置等。该方法的使用场景通常是需要在一个对象上创建一个属性，然后控制这个属性的行为。 Proxy 也可以用来代理一个对象，但是相比于 Object.defineProperty，它提供了更加强大的功能。使用 Proxy 可以截获并重定义对象的基本操作，比如访问属性、赋值、函数调用等等。在这些操作被执行之前，可以通过拦截器函数对这些操作进行拦截和修改。因此，通过 Proxy，你可以完全重写一个对象的默认行为。该方法的使用场景通常是需要对一个对象的行为进行定制化，或者需要在对象上添加额外的功能。 对比 以下是 Proxy 和 Object.defineProperty 的一些区别对比： 方面ProxyObject.defineProperty语法使用 new Proxy(target,

By Ne0inhk