Oracle写merge语录,Oracle数据库中的merge语句

在Oracle数据库的使用中，向表中插入数据时，经常有这样的需求：如果待插入的记录表中已经存在，就用新记录的值更新原记录；如果不存在，就插入新记录。这时候，就需要用merge语句。通过merge语句能够避免自己手写好多if判断，程序简洁，更好维护。

merge语句的语法

MERGE INTO target_table

USING source_table

ON search_condition

WHEN MATCHED THEN

UPDATE SET col1 = value1, col2 = value2,...

WHERE

[DELETE WHERE ]

WHEN NOT MATCHED THEN

INSERT (col1,col2,...)

values(value1,value2,...)

WHERE ;

这个语句的执行过程：

对于source_table中的每一条记录，结合target_table判断该记录是否满足search_condition：如果满足，执行MATCHED部分的update语句；如果不满足，执行NOT MATCHED部分的insert语句。

其中的upate和insert语句都可以指定where条件，来控制只更新或者插入满足条件的记录。

此外，update语句的部分还可以指定一个可选的带where条件的delete语句。它用来删除这样的记录：符合search_condition，而且在执行update操作之后，各个字段的值满足delete where语句中的条件。

例子

简单的merge

-- create tables

CREATE TABLE members (

member_id NUMBER PRIMARY KEY,

first_name VARCHAR2(50) NOT NULL,

last_name VARCHAR2(50) NOT NULL,

rank VARCHAR2(20)

);

CREATE TABLE member_staging AS

SELECT * FROM members;

The following INSERT statements insert sample data into the members and member_staging tables:

-- insert into members table

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum');

INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze');

-- insert into member_staging table

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver');

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze');

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold');

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');

INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

When updating data from the members table to member_staging table, we should perform the following actions:

-- merge the data

MERGE INTO member_staging x

USING (SELECT member_id, first_name, last_name, rank FROM members) y

ON (x.member_id = y.member_id)

WHEN MATCHED THEN

UPDATE SET x.first_name = y.first_name,

x.last_name = y.last_name,

x.rank = y.rank

WHERE x.first_name <> y.first_name OR

x.last_name <> y.last_name OR

x.rank <> y.rank

WHEN NOT MATCHED THEN

INSERT(x.member_id, x.first_name, x.last_name, x.rank)

VALUES(y.member_id, y.first_name, y.last_name, y.rank);

这个merge语句的功能如下图：

image.png

将members表中的记录合并到member_staging表。对于某条在members表中的记录，如果在member_staging表中能找到member_id相同的记录，就执行带条件的更新语句：用members表中记录的值作为新值更新到member_staging表中的对应记录。对于某条在members表中的记录，如果在member_staging表中找不到member_id相同的记录，就将该记录插入到member_staging表中。

oracle官网的例子

Merging into a Table: Example

The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

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);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS

----------- ----------

153 100

154 100

155 100

156 100

158 100

159 100

160 100

161 100

163 100

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*.01)

WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

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

这个例子的功能就是计算奖金，原来是想只要有销售记录的员工每人发100。后来人力决定，修改奖金计算的规则，只有薪水8000以下的员工能拿到奖金，并设置了新的奖金金额。通过上面的一个merge语句就实现了对奖金表数据的修正。

参考链接