MERGE语句，它可以在一个SQL语句中对一张表或一个视图同时做更新或插入操作，避免了多个INSERT、UPDATE和DELETE语句。它可以从一个或多个数据源中选择数据对目标表做更新或插入操作，你可以指定在何种条件下更新或插入目标表或视图。不可以对同一行做多次更新操作。例如：实现用B表来更新A表数据，如果A表中没有，则把B表的数据插入A表。
11gR2语法如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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
WHEN MATCHED THEN
UPDATE SET column = {
expr
| DEFAULT }
[, column = {
expr
| DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
#merge_insert_clause
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({
expr
| DEFAULT }
[, {
expr
| DEFAULT } ]...
)
[ where_clause ]
#error_logging_clause
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
|
使用例子说明MERGE语句的作用：
创建测试表emp_m
|
1
2
3
4
5
6
7
8
9
10
11
12
|
scott@TEST>
create
table
emp_m
as
select
*
from
emp
where
comm
is
not
null
;
Table
created.
scott@TEST>
select
*
from
emp_m;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
|
例子1：插入和更新操作
现在测试表emp_m中只有comm不为空的数据，现在要把在emp表而不在emp_m表中的数据插入到emp_m表中，并把emp_m表中现有的数据的comm增加10。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
scott@TEST>MERGE
INTO
EMP_M M
2 USING EMP N
3
ON
(M.EMPNO = N.EMPNO)
4
WHEN
NOT
MATCHED
THEN
5
INSERT
6 (M.EMPNO, M.ENAME, M.JOB, M.MGR, M.HIREDATE, M.SAL, M.COMM, M.DEPTNO)
7
VALUES
8 (N.EMPNO, N.ENAME, N.JOB, N.MGR, N.HIREDATE, N.SAL, N.COMM, N.DEPTNO)
9
WHEN
MATCHED
THEN
10
UPDATE
SET
M.COMM = M.COMM + 10;
14
rows
merged.
scott@TEST>
select
*
from
emp_m;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 310 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 510 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1410 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 10 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14
rows
selected.
|
例子2：实现删除操作
从emp_m表中删除表emp中DEPTNO=30的记录。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
scott@TEST>MERGE
INTO
EMP_M M
2 USING (
SELECT
EMPNO
FROM
EMP
WHERE
DEPTNO = 30) N
3
ON
(M.EMPNO = N.EMPNO)
4
WHEN
MATCHED
THEN
5
UPDATE
SET
M.COMM =
DEFAULT
6
DELETE
WHERE
M.EMPNO = N.EMPNO;
6
rows
merged.
scott@TEST>
select
*
from
emp_m;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
|
使用注意事项：
-
不能指定一个在它上面定义了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
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1922868，如需转载请自行联系原作者
![【数据库】解决 oracle: SQL 错误 [900] [42000]: ORA-00900: 无效 SQL 语句](https://ucc.alicdn.com/pic/developer-ecology/f790f8e9ea28458da1cf5cbb69a7d12a.jpg?x-oss-process=image/format,webp/resize,h_160,m_lfit)
