- 我们必须至少发表两个声明
- 我们必须考虑性能
- 我们必须考虑比赛条件
- 我们必须在[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/
合并愉快!
翻译自: https://www.javacodegeeks.com/2011/12/arcane-magic-with-sql2003-merge.html