merge语句使用_使用SQL：2003 MERGE语句的奥术魔术

merge语句使用

时不时地，由于以下任何原因，我们不得不将INSERT与UPDATE区分开来感到尴尬：

• 我们必须至少发表两个声明
• 我们必须考虑性能
• 我们必须考虑比赛条件
• 我们必须在[UPDATE; 如果UPDATE_COUNT = 0 THEN INSERT]和[INSERT; 如果例外然后更新]
• 我们必须对每个更新/插入的记录执行一次这些语句

总而言之，这是错误和挫败感的重要根源。 同时，使用SQL MERGE语句可能是如此简单！

MERGE的典型情况

在许多其他用例中，当处理多对多关系时，MERGE语句可能会派上用场。 假设我们有以下架构：

CREATE TABLE documents (

  id NUMBER(7) NOT NULL,

  CONSTRAINT docu_id PRIMARY KEY (id)

);



CREATE TABLE persons (

  id NUMBER(7) NOT NULL,

  CONSTRAINT pers_id PRIMARY KEY (id)

);



CREATE TABLE document_person (

  docu_id NUMBER(7) NOT NULL,

  pers_id NUMBER(7) NOT NULL,

  flag NUMBER(1) NULL,



  CONSTRAINT docu_pers_pk PRIMARY KEY (docu_id, pers_id),

  CONSTRAINT docu_pers_fk_docu 

    FOREIGN KEY (docu_id) REFERENCES documents(id),

  CONSTRAINT docu_pers_fk_pers 

    FOREIGN KEY (pers_id) REFERENCES persons(id)

);

上表用于建模哪个人已阅读（flag = 1）/已删除（flag = 2）哪个文档。 为简单起见，通常将“ document_person”实体与“ documents”外部联接，以便“ document-person”记录的存在或不存在可能具有相同的语义：“ flag IS NULL”表示未读文档。
现在，当您要将文档标记为已读时，必须决定是插入一个新的“ document_person”，还是更新现有的“ document_person”。 与删除相同。 与将所有文档标记为已读或删除所有文档相同。

改用MERGE

您可以一口气做到这一切！ 假设您要插入/更新一条记录，以将一个文档标记为已读：

-- The target table

MERGE INTO document_person dst



-- The data source. In this case, just a dummy record

USING (

  SELECT :docu_id as docu_id, 

         :pers_id as pers_id, 

         :flag    as flag

  FROM DUAL

) src



-- The merge condition (if true, then update, else insert)

ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)



-- The update action

WHEN MATCHED THEN UPDATE SET

  dst.flag = src.flag



-- The insert action

WHEN NOT MATCHED THEN INSERT (

  dst.docu_id,

  dst.pers_id,

  dst.flag

)

VALUES (

  src.docu_id,

  src.pers_id,

  src.flag

)

这看起来很相似，但是比MySQL的INSERT .. ON DUPLICATE KEY UPDATE语句冗长得多，这更加简洁。

发挥到极致

但是，您可以走得更远！ 如前所述，您可能还希望将给定人员的所有文档标记为已读。 合并没问题。 如果指定：docu_id，则以下语句与上一条相同。 如果将其保留为空，它将仅将所有文档标记为：flag：

MERGE INTO document_person dst



-- The data source is now all "documents" (or just :docu_id) left outer

-- joined with the "document_person" mapping

USING (

  SELECT d.id     as docu_id, 

         :pers_id as pers_id, 

         :flag    as flag

  FROM documents d

  LEFT OUTER JOIN document_person d_p 

  ON d.id = d_p.docu_id AND d_p.pers_id = :pers_id

  -- If :docu_id is set, select only that document

  WHERE (:docu_id IS NOT NULL AND d.id = :docu_id)

  -- Otherwise, select all documents

     OR (:docu_id IS NULL)

) src



-- If the mapping already exists, update. Else, insert

ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)



-- The rest stays the same

WHEN MATCHED THEN UPDATE SET

  dst.flag = src.flag

WHEN NOT MATCHED THEN INSERT (

  dst.docu_id,

  dst.pers_id,

  dst.flag

)

VALUES (

  src.docu_id,

  src.pers_id,

  src.flag

)

jOOQ中的MERGE支持

jOOQ也完全支持MERGE。 有关更多详细信息，请参见手册（滚动至底部）：
http://www.jooq.org/manual/JOOQ/Query/
合并愉快！

参考：我们的JCG合作伙伴 Lukas Eder在JAVA，SQL和JOOQ博客上使用SQL：2003 MERGE语句 编写了 Arcane magic 。

相关文章 ：

• Java中的数据库架构导航
• ORM问题
• SQL或NOSQL：这是问题吗？
• 什么是NoSQL？
• 按汇总分组/多维数据集

翻译自: https://www.javacodegeeks.com/2011/12/arcane-magic-with-sql2003-merge.html

merge语句使用