导航：首页 > 数据库 >

Oracle MERGE语句

发表于：2025-01-23 作者：千家信息网编辑

千家信息网最后更新 2025年01月23日，MERGE语句，它可以在一个SQL语句中对一张表或一个视图同时做更新或插入操作，避免了多个INSERT、UPDATE和DELETE语句。它可以从一个或多个数据源中选择数据对目标表做更新或插入操作，你可

千家信息网最后更新 2025年01月23日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   WHEN MATCHED THENUPDATE SET column = { expr | DEFAULT }           [, column = { expr | DEFAULT } ]...[ where_clause ][ DELETE where_clause ]#merge_insert_clauseWHEN NOT MATCHED THENINSERT [ (column [, column ]...) ]VALUES ({ expr | DEFAULT }          [, { expr | DEFAULT } ]...       )[ where_clause ]#error_logging_clauseLOG ERRORS   [ INTO [schema.] table ]  [ (simple_expression) ]  [ REJECT LIMIT { integer | UNLIMITED } ]

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

创建测试表emp_m

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。

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                    1014 rows selected.

例子2：实现删除操作

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

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

使用注意事项：