SQL基础（十二）MERGE语句

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。[@more@]

通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，
连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

■语法
MERGE [hint] INTO [schema .] { table | view } [t_alias]
USING [schema .] { table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN UPDATE SET {column={expr|DEFAULT},} where_clause
DELETE where_clause
WHEN NOT MATCHED THEN INSERT (column,) VALUES ({expr|DEFAULT},) where_clause
LOG ERRORS INTO [schema.]table (simple_expression) REJECT LIMIT {integer|UNLIMITED};

where_clause:WHERE condition

■例：
SQL>CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

SQL>INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);

SQL>SELECT * FROM bonuses;

EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100

SQL>MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
WHERE (S.salary <= 8000);

EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 180
160 175
161 170
179 620
173 610
165 680
166 640
164 720
172 730
167 620
171 740

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/174388/viewspace-913706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/174388/viewspace-913706/