Oracle MERGE语句

MERGE语句，它可以在一个SQL语句中对一张表或一个视图同时做更新或插入操作，避免了多个INSERT、UPDATE和DELETE语句。它可以从一个或多个数据源中选择数据对目标表做更新或插入操作，你可以指定在何种条件下更新或插入目标表或视图。不可以对同一行做多次更新操作。例如：实现用B表来更新A表数据，如果A表中没有，则把B表的数据插入A表。

11gR2语法如下：

MERGE[hint]
INTO[schema.]{table|view}[t_alias]
USING{[schema.]{table|view}
|subquery
}[t_alias]
ON(condition)
[merge_update_clause]
[merge_insert_clause]
[error_logging_clause];

#merge_update_clause
WHENMATCHEDTHEN
UPDATESETcolumn={expr|DEFAULT}
[,column={expr|DEFAULT}]...
[where_clause]
[DELETEwhere_clause]

#merge_insert_clause
WHENNOTMATCHEDTHEN
INSERT[(column[,column]...)]
VALUES({expr|DEFAULT}
[,{expr|DEFAULT}]...
)
[where_clause]

#error_logging_clause
LOGERRORS
[INTO[schema.]table]
[(simple_expression)]
[REJECTLIMIT{integer|UNLIMITED}]

使用例子说明MERGE语句的作用：

创建测试表emp_m

scott@TEST>createtableemp_masselect*fromempwherecommisnotnull;

Tablecreated.

scott@TEST>select*fromemp_m;

EMPNOENAME			JOB				MGRHIREDATE		SALCOMMDEPTNO
------------------------------------------------------------------------------------------------------------------------------
7499ALLEN			SALESMAN			76981981-02-2000:00:00	1600300	30
7521WARD			SALESMAN			76981981-02-2200:00:00	1250500	30
7654MARTIN			SALESMAN			76981981-09-2800:00:00	12501400	30
7844TURNER			SALESMAN			76981981-09-0800:00:00	1500	0	30

例子1：插入和更新操作

现在测试表emp_m中只有comm不为空的数据，现在要把在emp表而不在emp_m表中的数据插入到emp_m表中，并把emp_m表中现有的数据的comm增加10。

scott@TEST>MERGEINTOEMP_MM
2USINGEMPN
3ON(M.EMPNO=N.EMPNO)
4WHENNOTMATCHEDTHEN
5INSERT
6(M.EMPNO,M.ENAME,M.JOB,M.MGR,M.HIREDATE,M.SAL,M.COMM,M.DEPTNO)
7VALUES
8(N.EMPNO,N.ENAME,N.JOB,N.MGR,N.HIREDATE,N.SAL,N.COMM,N.DEPTNO)
9WHENMATCHEDTHEN
10UPDATESETM.COMM=M.COMM+10;

14rowsmerged.

scott@TEST>select*fromemp_m;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
------------------------------------------------------------------------------------------------------------------------------
7499ALLENSALESMAN76981981-02-2000:00:00160031030
7521WARDSALESMAN76981981-02-2200:00:00125051030
7654MARTINSALESMAN76981981-09-2800:00:001250141030
7844TURNERSALESMAN76981981-09-0800:00:0015001030
7369SMITHCLERK79021980-12-1700:00:0080020
7566JONESMANAGER78391981-04-0200:00:00297520
7698BLAKEMANAGER78391981-05-0100:00:00285030
7782CLARKMANAGER78391981-06-0900:00:00245010
7788SCOTTANALYST75661987-04-1900:00:00300020
7839KINGPRESIDENT1981-11-1700:00:00500010
7876ADAMSCLERK77881987-05-2300:00:00110020
7900JAMESCLERK76981981-12-0300:00:0095030
7902FORDANALYST75661981-12-0300:00:00300020
7934MILLERCLERK77821982-01-2300:00:00130010

14rowsselected.

例子2：实现删除操作

从emp_m表中删除表emp中DEPTNO=30的记录。

scott@TEST>MERGEINTOEMP_MM
2USING(SELECTEMPNOFROMEMPWHEREDEPTNO=30)N
3ON(M.EMPNO=N.EMPNO)
4WHENMATCHEDTHEN
5UPDATESETM.COMM=DEFAULT
6DELETEWHEREM.EMPNO=N.EMPNO;

6rowsmerged.

scott@TEST>select*fromemp_m;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
------------------------------------------------------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1700:00:0080020
7566JONESMANAGER78391981-04-0200:00:00297520
7782CLARKMANAGER78391981-06-0900:00:00245010
7788SCOTTANALYST75661987-04-1900:00:00300020
7839KINGPRESIDENT1981-11-1700:00:00500010
7876ADAMSCLERK77881987-05-2300:00:00110020
7902FORDANALYST75661981-12-0300:00:00300020
7934MILLERCLERK77821982-01-2300:00:00130010

使用注意事项：

• 不能指定一个在它上面定义了INSTEAD OF触发器的视图为目标视图。

• USING子名指定源数据，可以是表、视图或子查询

• UPDATE子句中的where条件可以指定目标表的条件也可以指定源表的条件

• UPDATE子句中不能更新ON条件中的列，更新表时不能指定DEFAULT

• UPDATE子句中指定DELETE WHERE用于删除数据。

• INSERT子句中，如果INSERT后省略列名，VALUES后的列的个数必须与目标表列个数一致

• INSERT子句中，可以使用WHERE条件限制插入的数据

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!