我要评论前言
在数据处理和数据库操作中，我们常常会遇到需要将两个数据集进行合并，并根据匹配情况执行不同操作的场景。oracle 数据库中的 merge into 语句正是为这种需求而设计的。
一、代码模板
merge into 语句的基本语法结构如下：
merge into 目标表名 t
using 源表名 s
on (条件表达式)
when matched then
update set t.列名1 = s.列名1 [, t.列名2 = s.列名2 ...]
[where 条件表达式]
when not matched then
insert (t.列名1 [, t.列名2 ...])
values (s.列名1 [, s.列名2 ...])
[where 条件表达式];merge into：指定要合并的目标表。using：指定源表或子查询。on：指定目标表和源表之间的匹配条件。when matched then：当目标表和源表中的记录匹配时，执行更新操作。when not matched then：当目标表中没有与源表匹配的记录时，执行插入操作。
二、使用场景
merge into 语句适用于以下场景：
1. 数据整合
当需要将两个表的数据进行整合时，merge into 可以根据匹配条件，将源表中的数据更新到目标表中，或者将不匹配的数据插入到目标表中。
2. 数据同步
在数据仓库或数据同步场景中，merge into 可以用来同步两个数据源。例如，将生产数据库中的数据同步到数据仓库中，同时更新已存在的记录。
3. 数据初始化
在初始化数据时，如果目标表中已经存在部分数据，可以使用 merge into 来避免重复插入，同时更新已存在的数据。
三、复杂案例
案例 1：多表关联更新与插入
假设我们有以下三个表：employees、departments 和 new_employees_departments。new_employees_departments 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employees_departments (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employees_departments (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employees_departments (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 同步员工和部门信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employees_departments ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary
where e.salary <> ned.salary or e.email <> ned.email
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id);
-- 使用 merge into 同步部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id
from new_employees_departments ned
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id
where d.department_name <> ned.department_name or d.location_id <> ned.location_id
when not matched then
insert (department_id, department_name, location_id)
values (ned.department_id, ned.department_name, ned.location_id);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 2：使用子查询作为源数据
假设我们有 employees 表和 employee_updates 表。employee_updates 表中存储了员工的更新信息，但这些信息需要经过一定的处理才能应用到 employees 表中。我们需要根据 employee_updates 表中的数据更新 employees 表，如果员工不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建员工更新表
create table employee_updates (
update_id number(6) primary key,
employee_id number(6),
new_name varchar2(50),
new_email varchar2(50),
new_salary number(8,2),
update_date date
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into employee_updates (update_id, employee_id, new_name, new_email, new_salary, update_date)
values (1, 100, 'alice smith', 'alice.smith@example.com', 5500, sysdate);
insert into employee_updates (update_id, employee_id, new_name, new_email, new_salary, update_date)
values (2, 101, 'bob johnson', 'bob.johnson@example.com', 6000, sysdate);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
eu.employee_id,
eu.new_name,
eu.new_email,
eu.new_salary,
eu.update_date
from employee_updates eu
where eu.update_date = (
select max(update_date)
from employee_updates
where employee_id = eu.employee_id
)
) eu
on (e.employee_id = eu.employee_id)
when matched then
update set
e.name = eu.new_name,
e.email = eu.new_email,
e.salary = eu.new_salary
where e.name <> eu.new_name or e.email <> eu.new_email or e.salary <> eu.new_salary
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (eu.employee_id, eu.new_name, eu.new_email, '555-5678', sysdate, 'sa_rep', eu.new_salary, 0.2, 102, 20);
-- 查询合并后的结果
select * from employees;案例 3：条件判断更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资时，才进行更新；如果员工不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary
where ne.salary > e.salary or e.email <> ne.email
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, ne.job_id, ne.salary, ne.commission_pct, ne.manager_id, ne.department_id);
-- 查询合并后的结果
select * from employees;案例 4：跨表更新与插入
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 同步员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary
where e.salary <> ned.salary or e.email <> ned.email
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id);
-- 使用 merge into 同步部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id
where d.department_name <> ned.department_name or d.location_id <> ned.location_id
when not matched then
insert (department_id, department_name, location_id)
values (ned.department_id, ned.department_name, ned.location_id);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 5：使用聚合函数和条件判断
假设我们有 employees 表和 employee_salaries 表。employee_salaries 表中存储了员工的多次工资调整记录。我们需要根据最新的工资调整记录更新 employees 表中的工资信息。如果员工不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建员工工资调整表
create table employee_salaries (
salary_id number(6) primary key,
employee_id number(6),
new_salary number(8,2),
effective_date date
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into employee_salaries (salary_id, employee_id, new_salary, effective_date)
values (1, 100, 5500, sysdate - 10);
insert into employee_salaries (salary_id, employee_id, new_salary, effective_date)
values (2, 100, 5800, sysdate - 5);
insert into employee_salaries (salary_id, employee_id, new_salary, effective_date)
values (3, 101, 6000, sysdate);
-- 使用 merge into 和聚合函数更新员工工资信息
merge into employees e
using (
select
es.employee_id,
max(es.new_salary) as latest_salary
from employee_salaries es
where es.effective_date = (
select max(effective_date)
from employee_salaries
where employee_id = es.employee_id
)
group by es.employee_id
) es
on (e.employee_id = es.employee_id)
when matched then
update set
e.salary = es.latest_salary
where e.salary <> es.latest_salary
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (es.employee_id, 'new employee', 'new@example.com', '555-5678', sysdate, 'sa_rep', es.latest_salary, 0.2, 102, 20);
-- 查询合并后的结果
select * from employees;案例 6：多条件匹配更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据多个条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和多条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary
where ne.salary > e.salary and e.email <> ne.email
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, ne.job_id, ne.salary, ne.commission_pct, ne.manager_id, ne.department_id);
-- 查询合并后的结果
select * from employees;案例 7：使用子查询和聚合函数
假设我们有 employees 表和 employee_performance 表。employee_performance 表中存储了员工的绩效评分记录。我们需要根据员工的平均绩效评分来更新 employees 表中的绩效奖金比例。如果员工不存在，则插入新记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建员工绩效表
create table employee_performance (
performance_id number(6) primary key,
employee_id number(6),
performance_score number(3),
evaluation_date date
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into employee_performance (performance_id, employee_id, performance_score, evaluation_date)
values (1, 100, 90, sysdate - 30);
insert into employee_performance (performance_id, employee_id, performance_score, evaluation_date)
values (2, 100, 95, sysdate - 15);
insert into employee_performance (performance_id, employee_id, performance_score, evaluation_date)
values (3, 101, 85, sysdate - 10);
-- 使用 merge into 和子查询更新员工绩效奖金
merge into employees e
using (
select
ep.employee_id,
avg(ep.performance_score) as avg_performance_score
from employee_performance ep
group by ep.employee_id
) ep
on (e.employee_id = ep.employee_id)
when matched then
update set
e.performance_bonus = case
when ep.avg_performance_score >= 90 then 0.1
when ep.avg_performance_score between 80 and 89 then 0.07
when ep.avg_performance_score between 70 and 79 then 0.05
else 0.03
end
where e.performance_bonus <> (
case
when ep.avg_performance_score >= 90 then 0.1
when ep.avg_performance_score between 80 and 89 then 0.07
when ep.avg_performance_score between 70 and 79 then 0.05
else 0.03
end
)
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ep.employee_id, 'new employee', 'new@example.com', '555-5678', sysdate, 'sa_rep', 6000, 0.2, 102, 20, case
when ep.avg_performance_score >= 90 then 0.1
when ep.avg_performance_score between 80 and 89 then 0.07
when ep.avg_performance_score between 70 and 79 then 0.05
else 0.03
end);
-- 查询合并后的结果
select * from employees;案例 8：跨表更新与条件判断
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary
where (e.salary <> ned.salary and ned.salary > 5000) or (e.email <> ned.email and ned.location_id = 1001)
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id);
-- 使用 merge into 和条件判断更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id
where (d.department_name <> ned.department_name and ned.location_id = 1001) or (d.location_id <> ned.location_id and ned.salary > 5000)
when not matched then
insert (department_id, department_name, location_id)
values (ned.department_id, ned.department_name, ned.location_id);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 9：使用子查询和多表关联
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
d.department_name,
d.location_id
from new_employee_department ned
left join departments d on ned.department_id = d.department_id
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary,
e.department_id = ned.department_id
where e.salary <> ned.salary or e.email <> ned.email or e.department_id <> ned.department_id
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id);
-- 使用 merge into 和子查询更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id,
count(e.employee_id) as employee_count
from new_employee_department ned
left join employees e on ned.employee_id = e.employee_id
group by ned.department_id, ned.department_name, ned.location_id
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id,
d.manager_id = case
when ned.employee_count > 0 then 101
else d.manager_id
end
where d.department_name <> ned.department_name or d.location_id <> ned.location_id
when not matched then
insert (department_id, department_name, location_id, manager_id)
values (ned.department_id, ned.department_name, ned.location_id, case
when ned.employee_count > 0 then 101
else null
end);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 10：使用子查询和条件判断更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。同时，我们还需要根据员工的工资来设置其绩效奖金比例。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary,
e.performance_bonus = case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
where (ne.salary > e.salary and e.email <> ne.email) or (e.performance_bonus <> (
case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, ne.job_id, ne.salary, ne.commission_pct, ne.manager_id, ne.department_id, case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 查询合并后的结果
select * from employees;案例 11：使用子查询和多表关联更新与插入
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary,
e.department_id = ned.department_id
where (e.salary <> ned.salary and ned.salary > 5000) or (e.email <> ned.email and ned.location_id = 1001) or (e.department_id <> ned.department_id and ned.department_name = 'sales')
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id);
-- 使用 merge into 和子查询更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id,
count(e.employee_id) as employee_count
from new_employee_department ned
left join employees e on ned.employee_id = e.employee_id
group by ned.department_id, ned.department_name, ned.location_id
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id,
d.manager_id = case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
where (d.department_name <> ned.department_name and ned.location_id = 1001) or (d.location_id <> ned.location_id and ned.salary > 5000) or (d.manager_id <> (
case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
))
when not matched then
insert (department_id, department_name, location_id, manager_id)
values (ned.department_id, ned.department_name, ned.location_id, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 12：使用子查询和条件判断更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。同时，我们还需要根据员工的工资来设置其绩效奖金比例，并根据部门 id 来设置其经理 id。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary,
e.manager_id = case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end,
e.performance_bonus = case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
where (ne.salary > e.salary and e.email <> ne.email) or (e.manager_id <> (
case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end
)) or (e.performance_bonus <> (
case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, ne.job_id, ne.salary, ne.commission_pct, case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else null
end, ne.department_id, case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 查询合并后的结果
select * from employees;案例 13：使用子查询和多表关联更新与插入
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录，并根据员工的工资来设置其绩效奖金比例。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary,
e.department_id = ned.department_id,
e.performance_bonus = case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end
where (e.salary <> ned.salary and ned.salary > 5000) or (e.email <> ned.email and ned.location_id = 1001) or (e.department_id <> ned.department_id and ned.department_name = 'sales') or (e.performance_bonus <> (
case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, ned.manager_id, ned.department_id, case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 使用 merge into 和子查询更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id,
count(e.employee_id) as employee_count
from new_employee_department ned
left join employees e on ned.employee_id = e.employee_id
group by ned.department_id, ned.department_name, ned.location_id
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id,
d.manager_id = case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
where (d.department_name <> ned.department_name and ned.location_id = 1001) or (d.location_id <> ned.location_id and ned.salary > 5000) or (d.manager_id <> (
case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
))
when not matched then
insert (department_id, department_name, location_id, manager_id)
values (ned.department_id, ned.department_name, ned.location_id, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 14：使用子查询和条件判断更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。同时，我们还需要根据员工的工资来设置其绩效奖金比例，并根据部门 id 来设置其经理 id。此外，我们还需要根据员工的工资和部门 id 来决定是否更新其工作职位。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary,
e.manager_id = case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end,
e.performance_bonus = case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end,
e.job_id = case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end
where (ne.salary > e.salary and e.email <> ne.email) or (e.manager_id <> (
case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end
)) or (e.performance_bonus <> (
case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
)) or (e.job_id <> (
case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else ne.job_id
end, ne.salary, ne.commission_pct, case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else null
end, ne.department_id, case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 查询合并后的结果
select * from employees;案例 15：使用子查询和多表关联更新与插入
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录，并根据员工的工资来设置其绩效奖金比例，根据部门的地点来设置其经理 id。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary,
e.department_id = ned.department_id,
e.performance_bonus = case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end,
e.manager_id = case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else e.manager_id
end
where (e.salary <> ned.salary and ned.salary > 5000) or (e.email <> ned.email and ned.location_id = 1001) or (e.department_id <> ned.department_id and ned.department_name = 'sales') or (e.performance_bonus <> (
case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end
)) or (e.manager_id <> (
case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else e.manager_id
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, ned.job_id, ned.salary, ned.commission_pct, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end, ned.department_id, case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 使用 merge into 和子查询更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id,
count(e.employee_id) as employee_count
from new_employee_department ned
left join employees e on ned.employee_id = e.employee_id
group by ned.department_id, ned.department_name, ned.location_id
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id,
d.manager_id = case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
where (d.department_name <> ned.department_name and ned.location_id = 1001) or (d.location_id <> ned.location_id and ned.salary > 5000) or (d.manager_id <> (
case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
))
when not matched then
insert (department_id, department_name, location_id, manager_id)
values (ned.department_id, ned.department_name, ned.location_id, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end);
-- 查询合并后的结果
select * from employees;
select * from departments;案例 16：使用子查询和条件判断更新与插入
假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。同时，我们还需要根据员工的工资来设置其绩效奖金比例，并根据部门 id 来设置其经理 id。此外，我们还需要根据员工的工资和部门 id 来决定是否更新其工作职位，并根据员工的工资来设置其佣金比例。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary,
e.manager_id = case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end,
e.performance_bonus = case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end,
e.job_id = case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end,
e.commission_pct = case
when ne.salary >= 6000 then 0.2
when ne.salary between 5000 and 5999 then 0.15
when ne.salary between 4000 and 4999 then 0.1
else 0.05
end
where (ne.salary > e.salary and e.email <> ne.email) or (e.manager_id <> (
case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end
)) or (e.performance_bonus <> (
case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
)) or (e.job_id <> (
case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end
)) or (e.commission_pct <> (
case
when ne.salary >= 6000 then 0.2
when ne.salary between 5000 and 5999 then 0.15
when ne.salary between 4000 and 4999 then 0.1
else 0.05
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ne.employee_id, ne.name, ne.email, ne.phone_number, ne.hire_date, case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else ne.job_id
end, ne.salary, case
when ne.salary >= 6000 then 0.2
when ne.salary between 5000 and 5999 then 0.15
when ne.salary between 4000 and 4999 then 0.1
else 0.05
end, case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else null
end, ne.department_id, case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 查询合并后的结果
select * from employees;案例 17：使用子查询和多表关联更新与插入
假设我们有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存储了新的员工及其部门信息。我们需要将这些新数据同步到 employees 和 departments 表中。如果员工已存在，则更新其信息；如果部门已存在，则更新部门信息；如果员工或部门不存在，则插入新记录。同时，我们还需要根据员工的工资和部门的地点来决定是否更新或插入记录，并根据员工的工资来设置其绩效奖金比例，根据部门的地点来设置其经理 id。此外，我们还需要根据员工的工资和部门 id 来决定是否更新其工作职位，并根据员工的工资来设置其佣金比例。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建部门表
create table departments (
department_id number(4) primary key,
department_name varchar2(30) not null,
manager_id number(6),
location_id number(4)
);
-- 创建新员工部门表
create table new_employee_department (
employee_id number(6),
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
department_name varchar2(30) not null,
location_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into departments (department_id, department_name, manager_id, location_id)
values (10, 'it', 101, 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10, 'information technology', 1001);
insert into new_employee_department (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, department_name, location_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20, 'sales', 1002);
-- 使用 merge into 和子查询更新员工信息
merge into employees e
using (
select
ned.employee_id,
ned.name,
ned.email,
ned.phone_number,
ned.hire_date,
ned.job_id,
ned.salary,
ned.commission_pct,
ned.manager_id,
ned.department_id,
ned.department_name,
ned.location_id
from new_employee_department ned
) ned
on (e.employee_id = ned.employee_id)
when matched then
update set
e.email = ned.email,
e.salary = ned.salary,
e.department_id = ned.department_id,
e.performance_bonus = case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end,
e.manager_id = case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else e.manager_id
end,
e.job_id = case
when ned.salary >= 7000 and ned.department_id = 20 then 'sa_mgr'
else e.job_id
end,
e.commission_pct = case
when ned.salary >= 6000 then 0.2
when ned.salary between 5000 and 5999 then 0.15
when ned.salary between 4000 and 4999 then 0.1
else 0.05
end
where (e.salary <> ned.salary and ned.salary > 5000) or (e.email <> ned.email and ned.location_id = 1001) or (e.department_id <> ned.department_id and ned.department_name = 'sales') or (e.performance_bonus <> (
case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end
)) or (e.manager_id <> (
case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else e.manager_id
end
)) or (e.job_id <> (
case
when ned.salary >= 7000 and ned.department_id = 20 then 'sa_mgr'
else e.job_id
end
)) or (e.commission_pct <> (
case
when ned.salary >= 6000 then 0.2
when ned.salary between 5000 and 5999 then 0.15
when ned.salary between 4000 and 4999 then 0.1
else 0.05
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ned.employee_id, ned.name, ned.email, ned.phone_number, ned.hire_date, case
when ned.salary >= 7000 and ned.department_id = 20 then 'sa_mgr'
else ned.job_id
end, ned.salary, case
when ned.salary >= 6000 then 0.2
when ned.salary between 5000 and 5999 then 0.15
when ned.salary between 4000 and 4999 then 0.1
else 0.05
end, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end, ned.department_id, case
when ned.salary >= 6000 then 0.1
when ned.salary between 5000 and 5999 then 0.07
when ned.salary between 4000 and 4999 then 0.05
else 0.03
end);
-- 使用 merge into 和子查询更新部门信息
merge into departments d
using (
select
ned.department_id,
ned.department_name,
ned.location_id,
count(e.employee_id) as employee_count
from new_employee_department ned
left join employees e on ned.employee_id = e.employee_id
group by ned.department_id, ned.department_name, ned.location_id
) ned
on (d.department_id = ned.department_id)
when matched then
update set
d.department_name = ned.department_name,
d.location_id = ned.location_id,
d.manager_id = case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
where (d.department_name <> ned.department_name and ned.location_id = 1001) or (d.location_id <> ned.location_id and ned.salary > 5000) or (d.manager_id <> (
case
when ned.employee_count > 0 and ned.location_id = 1001 then 101
when ned.employee_count > 0 and ned.location_id = 1002 then 102
else d.manager_id
end
))
when not matched then
insert (department_id, department_name, location_id, manager_id)
values (ned.department_id, ned.department_name, ned.location_id, case
when ned.location_id = 1001 then 101
when ned.location_id = 1002 then 102
else null
end);
-- 查询合并后的结果
select * from employees;
select * from departments;假设我们有 employees 表和 new_employees 表。new_employees 表中存储了新的员工信息，但我们需要根据一定的条件来决定是否更新或插入记录。例如，只有当新员工的工资高于目标表中现有员工的工资且电子邮件不同时，才进行更新；如果员工不存在，则插入新记录。同时，我们还需要根据员工的工资来设置其绩效奖金比例，并根据部门 id 来设置其经理 id。此外，我们还需要根据员工的工资和部门 id 来决定是否更新其工作职位，并根据员工的工资来设置其佣金比例。最后，我们还需要根据员工的工资来设置其电话号码。
-- 创建员工表
create table employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
performance_bonus number(2,2)
);
-- 创建新员工表
create table new_employees (
employee_id number(6) primary key,
name varchar2(50) not null,
email varchar2(50),
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4)
);
-- 插入初始数据
insert into employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (100, 'alice', 'alice@example.com', '555-1234', sysdate - 100, 'it_prog', 5000, 0.1, 101, 10, 0.05);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (100, 'alice', 'alice_new@example.com', '555-1234', sysdate - 100, 'it_prog', 5500, 0.1, 101, 10);
insert into new_employees (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values (101, 'bob', 'bob@example.com', '555-5678', sysdate - 50, 'sa_rep', 6000, 0.2, 102, 20);
-- 使用 merge into 和条件判断更新员工信息
merge into employees e
using new_employees ne
on (e.employee_id = ne.employee_id)
when matched then
update set
e.email = ne.email,
e.salary = ne.salary,
e.manager_id = case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end,
e.performance_bonus = case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end,
e.job_id = case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end,
e.commission_pct = case
when ne.salary >= 6000 then 0.2
when ne.salary between 5000 and 5999 then 0.15
when ne.salary between 4000 and 4999 then 0.1
else 0.05
end,
e.phone_number = case
when ne.salary >= 6000 then '555-5678'
when ne.salary between 5000 and 5999 then '555-4321'
else e.phone_number
end
where (ne.salary > e.salary and e.email <> ne.email) or (e.manager_id <> (
case
when ne.department_id = 10 then 101
when ne.department_id = 20 then 102
else e.manager_id
end
)) or (e.performance_bonus <> (
case
when ne.salary >= 6000 then 0.1
when ne.salary between 5000 and 5999 then 0.07
when ne.salary between 4000 and 4999 then 0.05
else 0.03
end
)) or (e.job_id <> (
case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else e.job_id
end
)) or (e.commission_pct <> (
case
when ne.salary >= 6000 then 0.2
when ne.salary between 5000 and 5999 then 0.15
when ne.salary between 4000 and 4999 then 0.1
else 0.05
end
)) or (e.phone_number <> (
case
when ne.salary >= 6000 then '555-5678'
when ne.salary between 5000 and 5999 then '555-4321'
else e.phone_number
end
))
when not matched then
insert (employee_id, name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, performance_bonus)
values (ne.employee_id, ne.name, ne.email, case
when ne.salary >= 6000 then '555-5678'
when ne.salary between 5000 and 5999 then '555-4321'
else ne.phone_number
end, ne.hire_date, case
when ne.salary >= 7000 and ne.department_id = 20 then 'sa_mgr'
else ne.job_id
end, ne.salary, case
when ne四、总结
merge into 语句是 oracle 数据库中一个强大的工具，用于将两个表的数据进行合并操作。通过指定匹配条件，可以灵活地对目标表进行更新或插入操作。在实际应用中，merge into 语句广泛应用于数据整合、数据同步和数据初始化等场景。好的，以下为你提供一些更复杂的 merge into 语句案例，这些案例涵盖了多表关联、子查询、条件判断等多种复杂场景。
到此这篇关于oracle高级语法篇之merge into语句复杂案例的文章就介绍到这了,更多相关oracle merge into语句内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论