当前位置:Gxlcms > 数据库问题 > Oracle MERGE语句

Oracle MERGE语句

时间:2021-07-01 10:21:17 帮助过:14人阅读

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

11gR2语法如下:

  1. MERGE [ hint ]   
  2.    INTO [ schema. ] { table | view } [ t_alias ]   
  3.    USING { [ schema. ] { table | view }
  4.          | subquery 
  5.          } [ t_alias ]   
  6.    ON ( condition )
  7.    [ merge_update_clause ]
  8.    [ merge_insert_clause ]
  9.    [ error_logging_clause ] ;
  10.    
  11. #merge_update_clause   
  12. WHEN MATCHED THEN
  13. UPDATE SET column = { expr | DEFAULT }
  14.            [, column = { expr | DEFAULT } ]...
  15. [ where_clause ]
  16. [ DELETE where_clause ]
  17. #merge_insert_clause
  18. WHEN NOT MATCHED THEN
  19. INSERT [ (column [, column ]...) ]
  20. VALUES ({ expr | DEFAULT }
  21.           [, { expr | DEFAULT } ]...
  22.        )
  23. [ where_clause ]
  24. #error_logging_clause
  25. LOG ERRORS 
  26.   [ INTO [schema.] table ]
  27.   [ (simple_expression) ]
  28.   [ REJECT LIMIT { integer | UNLIMITED } ]

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

创建测试表emp_m

  1. scott@TEST>create table emp_m as select * from emp where comm is not null;
  2. Table created.
  3. scott@TEST>select * from emp_m;
  4.      EMPNO ENAME
  5.   JOB
  6.      MGR HIREDATE
  7.     SAL       COMM     DEPTNO
  8. ---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
  9.       7499 ALLEN
  10.   SALESMAN
  11.     7698 1981-02-20 00:00:00
  12.    1600        300
  13.    30
  14.       7521 WARD 
  15.   SALESMAN
  16.     7698 1981-02-22 00:00:00
  17.    1250        500
  18.    30
  19.       7654 MARTIN
  20.   SALESMAN
  21.     7698 1981-09-28 00:00:00
  22.    1250       1400
  23.    30
  24.       7844 TURNER
  25.   SALESMAN
  26.     7698 1981-09-08 00:00:00
  27.    1500 
  28.  0
  29.    30

例子1:插入和更新操作

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

  1. scott@TEST>MERGE INTO EMP_M M
  2.   2  USING EMP N
  3.   3  ON (M.EMPNO = N.EMPNO)
  4.   4  WHEN NOT MATCHED THEN
  5.   5    INSERT
  6.   6      (M.EMPNO, M.ENAME, M.JOB, M.MGR, M.HIREDATE, M.SAL, M.COMM, M.DEPTNO)
  7.   7    VALUES
  8.   8      (N.EMPNO, N.ENAME, N.JOB, N.MGR, N.HIREDATE, N.SAL, N.COMM, N.DEPTNO)
  9.   9  WHEN MATCHED THEN
  10.  10    UPDATE SET M.COMM = M.COMM + 10;
  11. 14 rows merged.
  12. scott@TEST>select * from emp_m;
  13.      EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
  14. ---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
  15.       7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        310         30
  16.       7521 WARD                           SALESMAN                          7698 1981-02-22 00:00:00       1250        510         30
  17.       7654 MARTIN                         SALESMAN                          7698 1981-09-28 00:00:00       1250       1410         30
  18.       7844 TURNER                         SALESMAN                          7698 1981-09-08 00:00:00       1500         10         30
  19.       7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
  20.       7566 JONES                          MANAGER                           7839 1981-04-02 00:00:00       2975                    20
  21.       7698 BLAKE                          MANAGER                           7839 1981-05-01 00:00:00       2850                    30
  22.       7782 CLARK                          MANAGER                           7839 1981-06-09 00:00:00       2450                    10
  23.       7788 SCOTT                          ANALYST                           7566 1987-04-19 00:00:00       3000                    20
  24.       7839 KING                           PRESIDENT                              1981-11-17 00:00:00       5000                    10
  25.       7876 ADAMS                          CLERK                             7788 1987-05-23 00:00:00       1100                    20
  26.       7900 JAMES                          CLERK                             7698 1981-12-03 00:00:00        950                    30
  27.       7902 FORD                           ANALYST                           7566 1981-12-03 00:00:00       3000                    20
  28.       7934 MILLER                         CLERK                             7782 1982-01-23 00:00:00       1300                    10
  29. 14 rows selected.

例子2:实现删除操作

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

  1. scott@TEST>MERGE INTO EMP_M M
  2.   2  USING (SELECT EMPNO FROM EMP WHERE DEPTNO = 30) N
  3.   3  ON (M.EMPNO = N.EMPNO)
  4.   4  WHEN MATCHED THEN
  5.   5    UPDATE SET M.COMM = DEFAULT 
  6.   6    DELETE WHERE M.EMPNO = N.EMPNO;
  7. 6 rows merged.
  8. scott@TEST>select * from emp_m;
  9.      EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
  10. ---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
  11.       7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
  12.       7566 JONES                          MANAGER                           7839 1981-04-02 00:00:00       2975                    20
  13.       7782 CLARK                          MANAGER                           7839 1981-06-09 00:00:00       2450                    10
  14.       7788 SCOTT                          ANALYST                           7566 1987-04-19 00:00:00       3000                    20
  15.       7839 KING                           PRESIDENT                              1981-11-17 00:00:00       5000                    10
  16.       7876 ADAMS                          CLERK                             7788 1987-05-23 00:00:00       1100                    20
  17.       7902 FORD                           ANALYST                           7566 1981-12-03 00:00:00       3000                    20
  18.       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

本文出自 “DBA Fighting!” 博客,请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1922868

Oracle MERGE语句

标签:oracle merge

人气教程排行