网站导航
> 技术文档 > Oracle高级语法篇-merge into语句

Oracle高级语法篇-merge into语句

网友: 技术文档 2025-09-03 05:47:03

MERGE INTO 语句详解

在数据处理和数据库操作中，我们常常会遇到需要将两个数据集进行合并，并根据匹配情况执行不同操作的场景。Oracle 数据库中的 MERGE INTO 语句正是为这种需求而设计的。

一、代码模板

MERGE INTO 语句的基本语法结构如下：

MERGE INTO 目标表名 tUSING 源表名 sON (条件表达式)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 eUSING ( 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) nedON (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.emailWHEN 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 dUSING ( SELECT ned.department_id, ned.department_name, ned.location_id FROM new_employees_departments ned) nedON (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_idWHEN 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 eUSING ( 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 )) euON (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_salaryWHEN 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 eUSING new_employees neON (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.emailWHEN 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 eUSING ( 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) nedON (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.emailWHEN 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 dUSING ( SELECT ned.department_id, ned.department_name, ned.location_id FROM new_employee_department ned) nedON (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_idWHEN 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 eUSING ( 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) esON (e.employee_id = es.employee_id)WHEN MATCHED THEN UPDATE SET e.salary = es.latest_salary WHERE e.salary  es.latest_salaryWHEN 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 eUSING new_employees neON (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.emailWHEN 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 eUSING ( SELECT ep.employee_id, AVG(ep.performance_score) AS avg_performance_score FROM employee_performance ep GROUP BY ep.employee_id) epON (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 eUSING ( 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) nedON (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 dUSING ( SELECT ned.department_id, ned.department_name, ned.location_id FROM new_employee_department ned) nedON (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 eUSING ( 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) nedON (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_idWHEN 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 dUSING ( 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) nedON (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_idWHEN 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 eUSING new_employees neON (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 eUSING ( 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) nedON (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 dUSING ( 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) nedON (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 eUSING new_employees neON (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 eUSING ( 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) nedON (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 dUSING ( 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) nedON (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 eUSING new_employees neON (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 eUSING ( 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) nedON (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 dUSING ( 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) nedON (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 eUSING new_employees neON (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 eUSING ( 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) nedON (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 dUSING ( 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) nedON (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 eUSING new_employees neON (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 语句案例，这些案例涵盖了多表关联、子查询、条件判断等多种复杂场景。

网络标签：

相关问题