MERGE 预览

DB2 for i SQL MERGE 语句简介

DB2 for i 7.1 包括了对 MERGE SQL 语句的支持。本文概括介绍了 MERGE 并通过一些示例来展示如何使用它简化应用程序。

Karl Hanson (kchanson@us.ibm.com), 高级软件工程师, IBM

Karl HansonKarl 已经在 IBM 罗切斯特开发实验室工作了 30 多年。他接触过 System/38、OS/400 和 IBM i 的各种软件组件。在过去的 12 年间,他帮助开发了 DB2 for i SQL。



2011 年 8 月 24 日

简介

所有树木修剪工和家居装修工都知道,工具永远不嫌多。当然,有时即使工具不多您也能完成工作,但是合适的工具确实可以让您更轻松、更安全、更迅速地完成工作。编程也不例外。作为 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 后缀是 关联名(correlation-name)。使用它们来限定列引用有助于提高可读性,还能避免歧义。

对比上述语句与完成大致相同功能的以下 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 更改。
  • 一个数据“源”,称为 table-reference,再加上 search-condition 就可以决定在目标上可执行哪些行操作。这不限于单一表;它可以是一个涉及到多个表和视图的复杂查询。
  • 一个对比,称为 search-condition,常用来对比源 table-reference 内的一列与目标内的一列,以控制目标行的操作。
  • 一个或多个 matching-condition,用来指定目标上的 INSERT、UPDATE 或 DELETE 行操作。

INTO 子句

该子句用来确认 MERGE 目标,目标可以是一个允许对其底层行进行更改的表或视图。要求至少有一个 INTO 子句,且它必须确认一个现有的表或视图。请求者必须具有插入、更新或删除目标行的权限。

USING 子句

用来指定源数据作为 table-reference。在 MERGE 内使用的 table-reference 的一些常见类型有:

  • 一个表或视图,如上述示例中所示的:
    USING sales AS src
  • 一个 SELECT 语句,比如:
    USING ( SELECT qty FROM sales ) AS src
  • 一个表函数,比如:
    USING ( TABLE( salesfunc(1) ) ) AS src (col1, col2)

不管 USING 子句内的 table-reference 是何类型,它都代表的是 0 – N 行的逻辑结果集。

ON 子句

ON 子句包含一个比较(称为 search-condition),用来控制源 table-reference 行影响目标行的方式,正如 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 子句都包含一个匹配条件匹配条件 至少需要一个 MATCHED 或 NOT MATCHED,后跟 THEN,再其后是一个行操作。对于每个匹配条件,所允许的行操作有:

    MATCHED : UPDATE or DELETE
    NOT MATCHED : INSERT

最简单形式的匹配条件是:

    WHEN MATCHED THEN <operation> 
    WHEN NOT MATCHED THEN <operation>

这些简单的形式通常就已经足够。不过,也可以用 “AND search-condition” 扩展来做更为具体和精确的选择:

    WHEN MATCHED AND search-condition THEN <operation>
    WHEN NOT MATCHED AND search-condition THEN <operation>

AND 后面的 search-condition 增加和扩充了 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,现有行可能会被更新或删除。对于 NOT MATCHED,可能会插入一个新行。

只有目标表内的行会受影响,所以与独立的 INSERT、UPDATE 和 DELETE 语句不同,WHEN 子句内的 INSERT、UPDATE 和 DELETE 操作的语法不包含目标表名。正如上述第一个 MERGE 示例中所展示的那样。

对于 INSERT,VALUES 子句提供了这个新行的列值。如果没有为目标列提供一个或更多的值,则赋空值或默认值的通常规则适用。特殊值 NULL 或 DEFAULT 可以被显式地编码到 VALUES 子句内,与独立的 INSERT 语句无异。同样地,如果使用的是主机变量或参数标记,那么就可以像独立的 INSERT 语句那样提供 NULL 值。

还可以指定列名来表明插入到哪些列。在上述的第一个例子中,MERGE 之上的 INSERT 可以是:

WHEN NOT MATCHED THEN
      INSERT(SKU,QTY) VALUES(M.SKU, M.QTY)

其中的 UPDATE 修改了目标表内的一个已有行。SET 子句为该行提供了所有的新列值。类似于独立的 UPDATE 语句,每个要修改的列的语法是 “column-name = < value >”。参见 DB2 for i SQL Reference 内的 MERGE 语句 部分,获得有关 MERGE 内 SET 子句的详细信息和选项。

DELETE 的作用是删除目标表内的一个已有行。在 MERGE 内不允许这个行操作有任何其他的子句。

INSERT 和 UPDATE 列的可能值

VALUES 和 SET 子句为 MERGE 目标行指定新列值。通常列值来自 USING table-reference 结果集行,如上述的示例中所示:

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 支持三个选项:

  1. ATOMIC(默认)
  2. NOT ATOMIC STOP ON SQLEXCEPTION
  3. NOT ATOMIC CONTINUE ON SQLEXCEPTION

对于大多数的 MERGE 应用程序,应该使用 ATOMIC。当在 commitment control 下运行时,这意味着如果检测到任何的错误条件,MERGE 所做的全部更改都将复原。

另外的两个选项多用于一些罕见的情况。在使用 NOT ATOMIC STOP ON SQLEXCEPTION 时,如果在某个行操作期间发生了错误,那么 MERGE 就会结束,但对所有之前(成功)的行操作所做的更改仍然有效。在使用 NOT ATOMIC CONTINUE ON SQLEXCEPTION 时,如果在某个行操作期间发生了错误,那么 MERGE 将继续处理源中剩余的行,这就意味着可以容许任意数量的行操作错误发生。

多个 WHEN 子句的选择

很多 MERGE 语句都有一个 WHEN MATCHED 子句和一个 WHEN NOT MATCHED 子句,但实际上可以指定更多的 WHEN MATCHED 或 WHEN NOT MATCHED 子句。在这种情况下,您可能会想知道为某个源 table-reference 行该选择哪个 WHEN。这就有些类似于 SQL 例程内的 CASE 控制语句或 C 编程语言内的 switch 语句。

假设一个 MERGE 语句有几个 WHEN 子句:

  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 table-reference 结果集的一行时,逻辑上每个 WHEN 子句都会依照其被编写的顺序被评估。这时应选择第一个被评估为全真的子句。所以如果 ON 子句内的条件为真(即,PARTNO 列值相等),那么所有 WHEN MATCHED 子句都是被选择的对象。逻辑上应选择第一个被评估为真的 WHEN MATCHED,所有其他的则均被忽略。在这个例子中,如果输入 COST 列的值为 101,则第一个 WHEN MATCHED 子句的 AND search-condition 为真,所以会为该 WHEN 子句运行 UPDATE。对于多个 WHEN NOT MATCHED 子句,这一原则不变。

MERGE 也可以被编码成这样:即来自 USING table-reference 结果集的一个或多个行均不具备任何 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 表包含了有关从供货商处收到的新库存的信息。INVENTORY 表可以用这一信息进行更新,方法是使用一个如下所示的 MERGE 语句:

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 )

当这个语句运行时,来自 USING 结果集的每一行(来自 NEW_RECEIVED 表)都会与目标 INVENTORY 表内的行对比,使用二者的 PART_NO 列。如果找到了匹配的 INVENTORY 行,则会以 NEW_RECEIVED 表的 QTY_RCVD 值为增量增加 QTY_ONHAND 列。如果没有找到匹配的 INVENTORY 行,则会插入一个来自 NEW_RECEIVED 表的新行,且新行的列值来自 USING 结果集。请注意 DATE_ADDED 新行的列值是通过 CURRENT DATE 专用寄存器提供的。


一个极好的实际例子

我们考虑这样一个例子,有一个图书俱乐部,会员在这里阅读图书并定期讨论这本书。图书会员常常会讨论未来的候选图书并从中选择接下来要阅读的图书。使用一个简单的电子表格来记录之前阅读过的书籍以及过去建议过却尚未阅读的书籍的书名和作者。这个列表会定期更新,加入已阅读的和建议过的书籍。这个例子展示了如何使用 MERGE 语句来将这个图书列表存储在 DB2 for i 表内并加以管理。目标如下:

  1. 最小化书名和作者的数据输入
  2. 避免重复书目
  3. 简化对图书列表的更新

创建图书列表数据库

如下述的 SQL DDL (Data Definition Language) 所示,用于此图书列表的对象有:

  1. Book registry table — 用于书名和作者
  2. Book log table — 用来记录活动
  3. Meeting log table — 用来记录来自最近这次图书俱乐部聚会的读书活动

对于每一本新书,都会向图书登记表内插入一个包含该书书名和作者的新行。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. 图书列表数据库
图书列表数据库的图示

图书列表数据

以下所举是此图书登记表和聚会记录表内的行的例子。每当有新书(行)插入到图书登记表时会生成一个惟一的 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 ) ;
  1. 如果需要,USING 内的查询还可以更复杂些,比如包括一个 WHERE 子句。列值(选择列表)可以获取自其他表、用户定义或内置的函数、专用寄存器等。
  2. UPDATE SET 和 INSERT VALUES 内使用的列值获取自 USING 结果集的其他列值。
  3. 在 WHEN MATCHED 和 WHEN NOT MATCHED 子句上使用了额外的条件来决定应该执行哪个 UPDATE 或 INSERT。

结束语

如您所见,MERGE 语句对您的 SQL 工具和技术是一个很有价值的补充。它乍看起来似乎有些复杂,但尝试使用后,您会发现它比其他解决方案更为简单和方便。

参考资料

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


这是您第一次登陆到 developerWorks,已经自动为您创建了您的概要文件。 选择您概要文件中可以公开的信息的信息(如姓名、国家/地区,以及公司),这些信息同时也会与您所发布的内容相关联。 您可以随时更新您的 IBM 账号。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=IBM i, Information Management
ArticleID=753594
ArticleTitle=MERGE 预览
publish-date=08242011