[DB][Oracle]如何用一个SQL文实现Insert/Update/Delete和--MERGE命令详解

如何用一个SQL文实现Insert/Update/Delete和--MERGE命令

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

1 、 UPDATE 或 INSERT 子句是可 选 的

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

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

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

首先 创 建示例表：

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;

1 、可省略的 UPDATE 或 INSERT 子句

在 Oracle 9i, MERGE 语 句要求你必 须 同 时 指定 INSERT 和 UPDATE 子句 . 而在 Oracle 10g, 你可以省略 UPDATE 或 INSERT 子句中的一个 . 下面的例子根据表 NEWPRODUCTS 的 PRODUCT_ID 字段是否匹配来 updates 表 PRODUCTS 的信息 :

SQL> 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;       3 rows merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1502 OLYMPUS CAMERA ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER TOYS     SQL>     SQL> ROLLBACK;     Rollback complete.     SQL>

在上面例子中 , MERGE 语 句影响到是 产 品 id 为 1502, 1601 和 1666 的行 . 它 们 的 产 品名字和 种 类 被更新 为 表 newproducts 中的 值 . 下面例子省略 UPDATE 子句 , 把表 NEWPRODUCTS 中新的 PRODUCT_ID 插入到表 PRODUCTS 中 , 对 于在两个表中能 够 匹配上 PRODUCT_ID 的数据不作任何 处 理 . 从 这 个例子你能看到 PRODUCT_ID=1700 的行被插入到表 PRODUCTS 中 .

SQL> MERGE INTO products p     2 USING newproducts np     3 ON (p.product_id = np.product_id)     4 WHEN NOT MATCHED THEN     5 INSERT     6 VALUES (np.product_id, np.product_name,     7 np.category);       1 row merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1502 OLYMPUS IS50 ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER DVD     1700 WAIT INTERFACE BOOKS

 

2 、 带 条件的 Updates 和 Inserts 子句

你能 够 添加 WHERE 子句到 UPDATE 或 INSERT 子句中去 , 来跳 过 update 或 insert 操作 对 某些行的 处 理 . 下面例子根据表 NEWPRODUCTS 来更新表 PRODUCTS 数据 , 但必 须 字段 CATEGORY 也得同 时 匹配上 :

SQL> 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 WHERE p.category = np.category;       2 rows merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1502 OLYMPUS CAMERA ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER DVD     SQL>     SQL> rollback;

在 这 个例子中 , 产 品 ID 为 1502,1601 和 1666 匹配 ON 条件但是 1666 的 category 不匹配 . 因此 MERGE 命令只更新两行数据 . 下面例子展示了在 Updates 和 Inserts 子句都使用 WHERE 子句 :

SQL> 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 VALUES (np.product_id, np.product_name, np.category)     12 WHERE np.category != 'BOOKS'     SQL> /       1 row merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1502 OLYMPUS IS50 ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER TOYS       SQL>

注意由于有 WHERE 子句 INSERT 没有插入所有不匹配 ON 条件的行到表 PRODUCTS.

3 、无条件的 Inserts

你能 够 不用 连 接源表和目 标 表就把源表的数据插入到目 标 表中 . 这对 于你想插入所有行到目 标 表 时 是非常有用的 . Oracle 10g 现 在支持在 ON 条件中使用常量 过滤谓词 . 举 个常量 过滤谓词 例子 ON (1=0). 下面例子从源表插入行到表 PRODUCTS, 不 检查这 些行是否在表 PRODUCTS 中存在 :

SQL> MERGE INTO products p     2 USING newproducts np     3 ON (1=0)     4 WHEN NOT MATCHED THEN     5 INSERT     6 VALUES (np.product_id, np.product_name, np.category)     7 WHERE np.category = 'BOOKS'     SQL> /       1 row merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1502 OLYMPUS IS50 ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER DVD     1700 WAIT INTERFACE BOOKS     6 rows selected.     SQL>

4 、新增加的 DELETE 子句

Oracle 10g 中的 MERGE 提供了在 执 行数据操作 时 清除行的 选项 . 你能 够 在 WHEN MATCHED THEN UPDATE 子句中包含 DELETE 子句 . DELETE 子句必 须 有一个 WHERE 条件来 删 除匹配某些条件的行 . 匹配 DELETE WHERE 条件但不匹配 ON 条件的行不会被从表中 删 除 .

下面例子 验证 DELETE 子句 . 我 们 从表 NEWPRODUCTS 中合并行到表 PRODUCTS 中 , 但 删 除 category 为 ELECTRNCS 的行 .

SQL> 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 DELETE WHERE (p.category = 'ELECTRNCS')     9 WHEN NOT MATCHED THEN     10 INSERT     11 VALUES (np.product_id, np.product_name, np.category)     SQL> /       4 rows merged.       SQL> SELECT * FROM products;       PRODUCT_ID PRODUCT_NAME CATEGORY     ---------- -------------------- ----------     1501 VIVITAR 35MM ELECTRNCS     1600 PLAY GYM TOYS     1601 LAMAZE TOYS     1666 HARRY POTTER TOYS     1700 WAIT INTERFACE BOOKS     SQL>

产 品 ID 为 1502 的行从表 PRODUCTS 中被 删 除 , 因 为 它同 时 匹配 ON 条件和 DELETE WHERE 条件 . 产 品 ID 为 1501 的行匹配 DELETE WHERE 条件但不匹配 ON 条件 , 所以它没有被 删 除 . 产 品 ID 为 1700 的行不匹配 ON 条件 , 所以被插入表 PRODUCTS. 产 品 ID 为 1601 和 1666 的行匹配 ON 条件但不匹配 DELETE WHERE 条件 , 所以被更新 为 表 NEWPRODUCTS 中的 值 .