正如任何遮阳树技工或家庭装修杂工所知,您永远不会拥有太多工具。 当然,有时您会遇到手头上不足的工具,但是正确的工具可以帮助您以更简单,更安全,更快捷的方式完成工作。 在编程中也是如此。 DB2 for i 7.1中的新增功能,MERGE语句是一种方便的工具,用于同步两个表中的数据。 但是,正如您稍后将学习的那样,它还可以做更多的事情。 您可能会认为MERGE可以完成与编写程序相同的操作,但是工作量更少,符号更简单。
以下是一个简单的示例。 假设两个表YEARSALES和MONTHSALES都有SKU和QTY列,其中SKU是唯一键。 目标是将数据从MONTHSALES合并到YEARSALES,因此该语句可能如下所示:
MERGE INTO yearsales y -- target
USING monthsales m -- source (table-reference)
ON ( m.sku = y.sku ) -- comparison
WHEN MATCHED THEN –- matching-condition 1
UPDATE SET y.qty = y.qty + m.qty –- row operation 1
WHEN NOT MATCHED THEN -- matching-condition 2
INSERT VALUES(m.sku, m.qty) –- row operation 2
该语句的解释如下:对于MONTHSALES中的每一行,请在YEARSALES中查找具有匹配SKU的行。 如果存在匹配的行,请使用MONTHSALES QTY列更新QTY列。 如果不存在匹配的行,则使用MONTHSALES行的SKU和QTY列值插入新行。
在此示例中,INTO目标上的Y
后缀和USING源上的M
后缀是相关名 。 使用这些来限定列引用可以提高可读性并可以避免歧义。
将此与下面的执行近似相同功能SQL过程示例进行比较。 无疑可以对其进行某种程度的简化,但不能达到上述MERGE语句的水平。
CREATE PROCEDURE MRGEXMPL
LANGUAGE SQL MODIFIES SQL DATA
MG: BEGIN ATOMIC
DECLARE tgt_sku INT ;
DECLARE end_tgt INT DEFAULT 0;
DECLARE tgt CURSOR for
SELECT sku,qty FROM yearsales ORDER BY sku
FOR UPDATE OF qty ;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_tgt = 1 ;
OPEN tgt ;
FETCH tgt INTO tgt_sku ;
SC: FOR src CURSOR FOR
SELECT sku as src_sku, qty as src_qty
FROM monthsales ORDER BY sku
DO
EQ_CHK: IF src_sku = tgt_sku THEN
WHILE src_sku = tgt_sku AND end_tgt = 0 DO
UPDATE yearsales SET qty = qty + src_qty
WHERE CURRENT OF tgt ;
FETCH tgt INTO tgt_sku ;
END WHILE ;
END IF ;
IF src_sku > tgt_sku THEN
WHILE src_sku > tgt_sku AND end_tgt = 0 DO
FETCH tgt INTO tgt_sku ;
END WHILE ;
IF src_sku = tgt_sku AND end_tgt = 0 THEN
GOTO EQ_CHK ;
END IF ;
END IF ;
IF src_sku < tgt_sku OR end_tgt = 1 THEN
INSERT INTO yearsales VALUES(src_sku,src_qty);
END IF ;
END FOR ;
CLOSE tgt ;
END MG ;
MERGE语句的剖析
MERGE语句包括:
- 一个“目标”表(或视图),可以具有通过MERGE更改的行。
- 数据“源”(称为表引用)与搜索条件一起确定对目标执行哪些行操作。 这不限于单个表。 它可能是一个涉及多个表和视图的复杂查询。
- 比较,称为搜索条件 ,通常将源表引用中的列与目标中的列进行比较,以控制目标行的操作。
- 一个或多个匹配条件 ,用于在目标上指定INSERT,UPDATE或DELETE行操作。
INTO子句
这标识了MERGE目标,该目标可以是允许更改基础行的表或视图。 一个INTO子句是必需的,它必须标识一个现有的表或视图。 请求者必须有权插入,更新或删除目标行。
使用子句
这将源数据指定为表引用 。 MERGE中使用的一些常见的表引用类型是:
- 单个表或视图,如上例所示:
USING sales AS src
- SELECT语句,例如:
USING ( SELECT qty FROM sales ) AS src
- 一个表函数,例如:
USING ( TABLE( salesfunc(1) ) ) AS src (col1, col2)
无论USING子句中的表引用是哪种类型,它都表示0-N行的逻辑结果集。
ON子句
ON子句包含一个比较(称为search-condition ),该比较控制源表引用行如何影响目标行,如WHEN子句中所指定。 常见的比较是源中的列与目标中的列之间的比较,例如:
ON ( src.partnbr = tgt.partnbr )
但是,既不需要在源中也可以在目标中引用列,并且可能需要更详尽的表达式,包括子查询。
对于每个源行,都会评估ON比较。 当其评估为真时,可以执行WHEN MATCHED子句。 如果为假,则可以执行WHEN NOT MATCHED子句。
WHEN MATCHED或WHEN NOT MATCHED子句也可以使用SIGNAL语句而不是执行行操作来返回错误。 如果源行不符合任何WHEN MATCHED或WHEN NOT MATCHED子句的条件,它将被忽略并且对目标没有影响。
WHEN子句
每个WHEN子句都包含一个matching-condition 。 最低要求的匹配条件是MATCHED或NOT MATCHED,然后是THEN,然后是一行操作。 每个匹配条件允许的行操作是:
MATCHED : UPDATE or DELETE
NOT MATCHED : INSERT
匹配条件的最简单形式为:
WHEN MATCHED THEN <operation>
WHEN NOT MATCHED THEN <operation>
这些简单的形式通常是足够的。 但是,“ AND 搜索条件 ”扩展名可用于更具体或更精细的选择:
WHEN MATCHED AND search-condition THEN <operation>
WHEN NOT MATCHED AND search-condition THEN <operation>
AND之后的搜索条件是ON子句中的比较的补充或补充。
尽管一个WHEN MATCHED子句和一个WHEN NOT MATCHED子句通常就足够了,但是允许任何数量的WHEN子句。 以下图书清单示例显示了两个以上的WHEN子句如何有用。
使用WHEN MATCHED或WHEN NOT MATCHED而不是执行行操作子句也可能会使用SIGNAL语句返回错误。 这有助于将一致性检查添加到MERGE,并在检查失败时结束MERGE。 在前面的示例中,说源行永远不应标识过时的SKU。 您可以如下所示扩充语句,以检测过时的SKU并返回错误。
WHEN MATCHED AND y.status = ‘O’ THEN –- if obsolete SKU
SIGNAL SQLSTATE VALUE ‘75002’ -- quit with specific error
SET MESSAGE_TEXT = ‘Obsolete SKU’
WHEN MATCHED THEN
UPDATE SET y.qty = y.qty + m.qty
WHEN子句中的行操作
每当执行WHEN子句操作时,目标表中的单行都会受到影响。 对于MATCHED情况,可以更新或删除现有行。 对于未匹配的情况,可以插入新行。
仅目标表中的行会受到影响,因此WHEN子句中的INSERT,UPDATE和DELETE操作的语法不包含目标表名,就像在独立INSERT,UPDATE或DELETE语句中一样。 这在上面的第一个MERGE示例中显示。
对于INSERT,VALUES子句为新行提供列值。 如果未为目标列提供一个或多个值,则适用分配空值或默认值的常规规则。 可以在VALUES子句中对特殊值NULL或DEFAULT进行显式编码,与在独立INSERT语句中相同。 同样,如果使用主机变量或参数标记,则可以提供与独立INSERT语句相同的NULL值。
也可以指定列名以指示要插入的列。 在上面的第一个示例中,上面的MERGE的INSERT可能是:
WHEN NOT MATCHED THEN
INSERT(SKU,QTY) VALUES(M.SKU, M.QTY)
UPDATE修改目标中的一个现有行。 SET子句为该行提供任何新的列值。 就像独立的UPDATE语句一样,要修改的每一列的语法都是“ column-name = < value >
”。 有关MERGE中 SET子句的详细信息和选项,请参阅《 DB2 for i SQL参考》中的MERGE语句部分。
删除仅删除目标中的一个现有行。 在MERGE中,此行操作不允许其他子句。
可能的INSERT和UPDATE列值
VALUES和SET子句为MERGE目标行中的列指定新值。 列值通常来自USING 表引用结果集行,例如上面的示例:
WHEN NOT MATCHED THEN INSERT VALUES(M.SKU, M.QTY)
但是可以通过其他方式提供列值,并且在给定的VALUES或SET子句中,每个列值都独立于所有其他列值。 这是一些替代示例。
- 文字:
INSERT VALUES( M.SKU, 100 )
- SQL内置函数:
INSERT VALUES( M.SKU, MAX(M.QTY,100) )
- SQL特殊寄存器:
INSERT (SKU,UTC_TIME) VALUES( M.SKU, CURRENT TIME – CURRENT TIMEZONE )
- SQL表达式:
INSERT (PROPERTY_ID,TIMES_SHOWN) VALUES( S.ID , ( SELECT COUNT(*) FROM SALES_EVENTS WHERE PROPERTY_ID = S.ID ) )
- 主机变量:
INSERT (PROPERTY_ID,TIMES_SHOWN) VALUES( S.ID , :Shown )
- 参数标记:
INSERT (PROPERTY_ID,TIMES_SHOWN) VALUES( S.ID , CAST(? AS INT) )
- 全局变量:
INSERT (PROPERTY_ID,TIMES_SHOWN) VALUES( S.ID , SHOWNGVAR )
就像独立的INSERT或UPDATE语句一样,提供的列值的数据类型必须与相应的列数据类型兼容。
原子度选项
MERGE支持三个选项:
-
ATOMIC
(默认) -
NOT ATOMIC STOP ON SQLEXCEPTION
-
NOT ATOMIC CONTINUE ON SQLEXCEPTION
对于大多数MERGE应用,应使用ATOMIC
。 在承诺控制下运行时,这意味着如果检测到任何错误情况,则MERGE所做的所有更改都将被撤消。
其他选项可能在特殊情况下很有用。 当使用NOT ATOMIC STOP ON SQLEXCEPTION
,如果在单个行操作期间发生错误,则MERGE会终止,但是所有先前(成功)行操作的更改仍然有效。 当NOT ATOMIC CONTINUE ON SQLEXCEPTION
使用NOT ATOMIC CONTINUE ON SQLEXCEPTION
,如果在单个行操作期间发生错误,则MERGE会继续处理所有剩余的源行,因此可以容忍任何数量的行操作错误。
选择多个WHEN子句
许多MERGE语句具有一个WHEN MATCHED子句和一个WHEN NOT MATCHED子句,但是可以指定更多的WHEN MATCHED或WHEN NOT MATCHED子句。 在那种情况下,您可能想知道为特定的源表引用行选择哪个WHEN? 它的工作方式类似于SQL例程中的CASE控制语句或C编程语言中的switch语句。
假定带有几个WHEN子句的MERGE语句:
MERGE INTO trgtbl AS t
USING ( SELECT partno, cost, … FROM srctbl ) AS s
ON ( s.partno = t.partno )
WHEN MATCHED AND c.cost > 100 THEN
UPDATE SET …
WHEN MATCHED THEN
UPDATE SET …
WHEN NOT MATCHED AND s.cost > 100 THEN
INSERT VALUES( … )
WHEN NOT MATCHED THEN
INSERT VALUES( … )
存在两个WHEN MATCHED子句和两个WHEN NOT MATCHED子句。 处理来自USING 表引用结果集中的一行时,逻辑上将按照每个WHEN子句的写入顺序对其求值。 选择第一个评估完全正确的评估。 因此,如果ON子句中的条件为true(即PARTNO列值相等),则所有WHEN MATCHED子句都是选择的候选对象。 逻辑上,选择第一个评估为true的WHEN MATCHED,然后忽略所有其他条件。 在此示例中,如果输入COST列的值为101,则第一个WHEN MATCHED子句的AND 搜索条件为true,因此将运行该WHEN子句的UPDATE。 对于多个WHEN NOT MATCHED子句也是如此。
可以对MERGE进行编码,以使USING 表引用结果集中的一个或多个行不符合任何 WHEN子句的条件。 所有此类行都将被忽略。 实际上,MERGE语句具有可选的ELSE IGNORE子句,可以在所有WHEN子句之后指定该子句。 ELSE IGNORE子句仅用于可读性–无论该子句是否存在,MERGE处理都是相同的。
一个简单的MERGE示例
假设存在两个表,我们需要将数据从一个合并到另一个。
零件库存表INVENTORY表具有以下列:
PART_NO INTEGER UNIQUE KEY
QTY_ONHAND INTEGER
DESCRIPTION VARCHAR(400)
DATE_ADDED DATE
收到的新零件NEW_RECEIVED表具有以下列:
PART_NO INTEGER UNIQUE KEY
QTY_RCVD INTEGER
DESCRIPTION VARCHAR(400)
NEW_RECEIVED表包含有关从供应商处收到的新库存的信息。 可以使用MERGE语句使用以下信息更新INVENTORY表:
MERGE INTO inventory AS i
USING ( SELECT part_no, qty_rcvd, description FROM new_received
WHERE qty_rcvd > 0 ) AS r
ON ( r.part_no = i.part_no)
WHEN MATCHED THEN
UPDATE SET qty_onhand = qty_onhand + r.qty_rcvd
WHEN NOT MATCHED THEN
INSERT VALUES( r.part_no, r.qty_rcvd, r.description,
CURRENT DATE )
运行此语句时,使用两者的PART_NO列将USING结果集中的每一行(来自NEW_RECEIVED表)与目标INVENTORY表中的行进行比较。 如果找到匹配的INVENTORY行,则将NEW_RECEIVED表中的QTY_ONHAND列增加QTY_RCVD的值。 如果找不到匹配的INVENTORY行,则使用NEW_RECEIVED表中USING结果集中的列值插入新行。 请注意,DATE_ADDED的新行的列值是通过CURRENT DATE特殊寄存器提供的。
一个不平凡的实际例子
考虑一个读书俱乐部,成员可以在那里读书并定期开会讨论这本书。 本书成员还讨论了将来的候选书籍,并选择下一个要阅读的书籍。 一个简单的电子表格用于记录过去阅读过的书籍的名称和作者,以及推荐但尚未阅读的书籍。 该列表会定期更新阅读或建议的书籍。 此示例说明如何将书单存储在DB2 for i表中以及如何使用MERGE语句进行管理。 目标是:
- 最小化书名和作者的数据输入
- 避免重复的书籍条目
- 简化图书清单的更新
创建图书清单数据库
如下面SQL DDL(数据定义语言)所示,用于图书清单的对象是:
- 图书注册表-用于标题和作者
- 书籍日志表-用于随时间累积活动
- 会议日志表-用于最近的读书俱乐部会议的读书活动
对于每本新书,将具有书名和作者的行插入到书注册表中。 BookID列是IDENTITY列,因此在插入每行书时会自动为每个书分配一个唯一的编号。
一旦书的行存在于注册表表中,就可以使用其唯一的BookID值将正在进行的活动累积在书日志表中,而不用重新输入书名和作者字符串。 会议日志表中的行也包含BookID,其想法是将会议日志数据合并为书籍日志表中的累积数据。 会议日志表和书籍日志表都使用链接到书籍注册表BookID列的参照完整性约束,以确保日志表中的每一行在注册表中都有对应的行。
要查看书籍活动的完整列表,可以使用SQL视图(未显示),该视图将注册表中的书籍标题和作者列与日志中的活动结合在一起。
下面是实现图书清单数据库SQL DDL。
create schema bookinf ;
create table bookinf.bookreg -- book register
( title char(60), -- cover title
author char(40), -- author
bookID integer as identity unique ) ;
create table bookinf.booklog -- book log
( bookID integer references bookinf.bookreg (bookID),
proposals smallint, -- no. times proposed as candidate
whenread date ) ; -- when book was actually read by club
create table bookinf.mtglog -- meeting log
( bookID integer references bookinf.bookreg (bookID),
status char(1)
whenread date ) ;
图1显示了这些对象及其关系。
图1.图书清单数据库
![图书清单数据库图](https://i-blog.csdnimg.cn/blog_migrate/a7b1fee84dea5fcc9926c4cafb6d197f.png)
图书清单数据
下面是书籍注册表表和会议日志表中的行的示例。 会议记录表中将在将新书(行)插入到书注册表中时生成的唯一BOOKID列值用于会议日志表中,以将书状态与注册表相关联。
BOOKREG Table:
TITLE AUTHOR BOOKID
In the Heart of the Sea Nathaniel Philbrick 1
The River of Doubt Candice Millard 2
Isaac's Storm Erik Larson 3
The Shipping News Annie Proulx 4
MTGLOG Table:
BOOKID STATUS WHENREAD
1 P <null>
2 P <null>
3 P <null>
4 R 2011-01-31
使用会议日志数据更新书日志
最后,下面的MERGE语句可用于将会议日志表中的数据合并到书籍日志表中,其中包含随时间累积的书籍信息。
MERGE INTO bookinf.booklog log
USING ( SELECT bookID, status, whenread FROM bookinf.mtglog ) mtg
ON mtg.bookID = log.bookID )
WHEN MATCHED AND mtg.status = 'R' THEN
UPDATE SET log.whenread = mtg.whenread
WHEN MATCHED AND mtg.status = 'P' THEN
UPDATE SET log.proposals = log.proposals + 1
WHEN NOT MATCHED AND mtg.status = 'P' THEN
INSERT ( bookID, mentions, proposals, whenread )
VALUES( mtg.bookID, 0, 1, null )
WHEN NOT MATCHED AND mtg.status = 'R' THEN
INSERT ( bookID, mentions, proposals, whenread )
VALUES( mtg.bookID, 0, 1, mtg.whenread ) ;
- 如果需要,USING中的查询可能会更复杂,包括WHERE子句。 列值(选择列表)可以从其他表,用户定义或内置函数,特殊寄存器等中派生。
- UPDATE SET和INSERT VALUES中使用的列值是从USING结果集中的其他列值派生的。
- 在WHEN MATCHED和WHEN NOT MATCHED子句上使用额外的条件来确定要执行哪个UPDATE或INSERT。
结论
如您所见,MERGE语句可以作为您SQL工具和技术的宝贵补充。 乍一看,它可能看起来很复杂,但是尝试之后,您会发现它比其他解决方案更简单,更方便。
翻译自: https://www.ibm.com/developerworks/ibmi/library/i-db2merge/index.html