Oracle高級(jí)語(yǔ)法篇之merge?into語(yǔ)句復(fù)雜案例
更新時(shí)間：2025年07月04日 09:56:38 作者：ssauuuuuu
Oracle的MERGE?INTO語(yǔ)句用于將數(shù)據(jù)集合并,根據(jù)匹配條件執(zhí)行更新或插入操作,下面這篇文章主要介紹了Oracle高級(jí)語(yǔ)法篇之merge?into語(yǔ)句復(fù)雜的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下
前言
在數(shù)據(jù)處理和數(shù)據(jù)庫(kù)操作中，我們常常會(huì)遇到需要將兩個(gè)數(shù)據(jù)集進(jìn)行合并，并根據(jù)匹配情況執(zhí)行不同操作的場(chǎng)景。Oracle 數(shù)據(jù)庫(kù)中的 MERGE INTO 語(yǔ)句正是為這種需求而設(shè)計(jì)的。
一、代碼模板
MERGE INTO 語(yǔ)句的基本語(yǔ)法結(jié)構(gòu)如下：
MERGE INTO 目標(biāo)表名 t
USING 源表名 s
ON (條件表達(dá)式)
WHEN MATCHED THEN
UPDATE SET t.列名1 = s.列名1 [, t.列名2 = s.列名2 ...]
[WHERE 條件表達(dá)式]
WHEN NOT MATCHED THEN
INSERT (t.列名1 [, t.列名2 ...])
VALUES (s.列名1 [, s.列名2 ...])
[WHERE 條件表達(dá)式];MERGE INTO：指定要合并的目標(biāo)表。USING：指定源表或子查詢。ON：指定目標(biāo)表和源表之間的匹配條件。WHEN MATCHED THEN：當(dāng)目標(biāo)表和源表中的記錄匹配時(shí)，執(zhí)行更新操作。WHEN NOT MATCHED THEN：當(dāng)目標(biāo)表中沒(méi)有與源表匹配的記錄時(shí)，執(zhí)行插入操作。
二、使用場(chǎng)景
MERGE INTO 語(yǔ)句適用于以下場(chǎng)景：
1. 數(shù)據(jù)整合
當(dāng)需要將兩個(gè)表的數(shù)據(jù)進(jìn)行整合時(shí)，MERGE INTO 可以根據(jù)匹配條件，將源表中的數(shù)據(jù)更新到目標(biāo)表中，或者將不匹配的數(shù)據(jù)插入到目標(biāo)表中。
2. 數(shù)據(jù)同步
在數(shù)據(jù)倉(cāng)庫(kù)或數(shù)據(jù)同步場(chǎng)景中，MERGE INTO 可以用來(lái)同步兩個(gè)數(shù)據(jù)源。例如，將生產(chǎn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)同步到數(shù)據(jù)倉(cāng)庫(kù)中，同時(shí)更新已存在的記錄。
3. 數(shù)據(jù)初始化
在初始化數(shù)據(jù)時(shí)，如果目標(biāo)表中已經(jīng)存在部分?jǐn)?shù)據(jù)，可以使用 MERGE INTO 來(lái)避免重復(fù)插入，同時(shí)更新已存在的數(shù)據(jù)。
三、復(fù)雜案例
案例 1：多表關(guān)聯(lián)更新與插入
假設(shè)我們有以下三個(gè)表：employees、departments 和 new_employees_departments。new_employees_departments 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 2：使用子查詢作為源數(shù)據(jù)
假設(shè)我們有 employees 表和 employee_updates 表。employee_updates 表中存儲(chǔ)了員工的更新信息，但這些信息需要經(jīng)過(guò)一定的處理才能應(yīng)用到 employees 表中。我們需要根據(jù) employee_updates 表中的數(shù)據(jù)更新 employees 表，如果員工不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建員工更新表
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
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 3：條件判斷更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 4：跨表更新與插入
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 5：使用聚合函數(shù)和條件判斷
假設(shè)我們有 employees 表和 employee_salaries 表。employee_salaries 表中存儲(chǔ)了員工的多次工資調(diào)整記錄。我們需要根據(jù)最新的工資調(diào)整記錄更新 employees 表中的工資信息。如果員工不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建員工工資調(diào)整表
CREATE TABLE employee_salaries (
salary_id NUMBER(6) PRIMARY KEY,
employee_id NUMBER(6),
new_salary NUMBER(8,2),
effective_date DATE
);
-- 插入初始數(shù)據(jù)
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 和聚合函數(shù)更新員工工資信息
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 6：多條件匹配更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)多個(gè)條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 7：使用子查詢和聚合函數(shù)
假設(shè)我們有 employees 表和 employee_performance 表。employee_performance 表中存儲(chǔ)了員工的績(jī)效評(píng)分記錄。我們需要根據(jù)員工的平均績(jī)效評(píng)分來(lái)更新 employees 表中的績(jī)效獎(jiǎng)金比例。如果員工不存在，則插入新記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建員工績(jī)效表
CREATE TABLE employee_performance (
performance_id NUMBER(6) PRIMARY KEY,
employee_id NUMBER(6),
performance_score NUMBER(3),
evaluation_date DATE
);
-- 插入初始數(shù)據(jù)
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 和子查詢更新員工績(jī)效獎(jiǎng)金
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 8：跨表更新與條件判斷
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 9：使用子查詢和多表關(guān)聯(lián)
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 10：使用子查詢和條件判斷更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 11：使用子查詢和多表關(guān)聯(lián)更新與插入
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 12：使用子查詢和條件判斷更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，并根據(jù)部門 ID 來(lái)設(shè)置其經(jīng)理 ID。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 13：使用子查詢和多表關(guān)聯(lián)更新與插入
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄，并根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 14：使用子查詢和條件判斷更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，并根據(jù)部門 ID 來(lái)設(shè)置其經(jīng)理 ID。此外，我們還需要根據(jù)員工的工資和部門 ID 來(lái)決定是否更新其工作職位。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 15：使用子查詢和多表關(guān)聯(lián)更新與插入
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄，并根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，根據(jù)部門的地點(diǎn)來(lái)設(shè)置其經(jīng)理 ID。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;案例 16：使用子查詢和條件判斷更新與插入
假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，并根據(jù)部門 ID 來(lái)設(shè)置其經(jīng)理 ID。此外，我們還需要根據(jù)員工的工資和部門 ID 來(lái)決定是否更新其工作職位，并根據(jù)員工的工資來(lái)設(shè)置其傭金比例。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;案例 17：使用子查詢和多表關(guān)聯(lián)更新與插入
假設(shè)我們有 employees 表、departments 表和 new_employee_department 表。new_employee_department 表中存儲(chǔ)了新的員工及其部門信息。我們需要將這些新數(shù)據(jù)同步到 employees 和 departments 表中。如果員工已存在，則更新其信息；如果部門已存在，則更新部門信息；如果員工或部門不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資和部門的地點(diǎn)來(lái)決定是否更新或插入記錄，并根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，根據(jù)部門的地點(diǎn)來(lái)設(shè)置其經(jīng)理 ID。此外，我們還需要根據(jù)員工的工資和部門 ID 來(lái)決定是否更新其工作職位，并根據(jù)員工的工資來(lái)設(shè)置其傭金比例。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建部門表
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
-- 創(chuàng)建新員工部門表
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)
);
-- 插入初始數(shù)據(jù)
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);
-- 查詢合并后的結(jié)果
SELECT * FROM employees;
SELECT * FROM departments;假設(shè)我們有 employees 表和 new_employees 表。new_employees 表中存儲(chǔ)了新的員工信息，但我們需要根據(jù)一定的條件來(lái)決定是否更新或插入記錄。例如，只有當(dāng)新員工的工資高于目標(biāo)表中現(xiàn)有員工的工資且電子郵件不同時(shí)，才進(jìn)行更新；如果員工不存在，則插入新記錄。同時(shí)，我們還需要根據(jù)員工的工資來(lái)設(shè)置其績(jī)效獎(jiǎng)金比例，并根據(jù)部門 ID 來(lái)設(shè)置其經(jīng)理 ID。此外，我們還需要根據(jù)員工的工資和部門 ID 來(lái)決定是否更新其工作職位，并根據(jù)員工的工資來(lái)設(shè)置其傭金比例。最后，我們還需要根據(jù)員工的工資來(lái)設(shè)置其電話號(hào)碼。
-- 創(chuàng)建員工表
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)
);
-- 創(chuàng)建新員工表
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)
);
-- 插入初始數(shù)據(jù)
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四、總結(jié)
MERGE INTO 語(yǔ)句是 Oracle 數(shù)據(jù)庫(kù)中一個(gè)強(qiáng)大的工具，用于將兩個(gè)表的數(shù)據(jù)進(jìn)行合并操作。通過(guò)指定匹配條件，可以靈活地對(duì)目標(biāo)表進(jìn)行更新或插入操作。在實(shí)際應(yīng)用中，MERGE INTO 語(yǔ)句廣泛應(yīng)用于數(shù)據(jù)整合、數(shù)據(jù)同步和數(shù)據(jù)初始化等場(chǎng)景。好的，以下為你提供一些更復(fù)雜的 MERGE INTO 語(yǔ)句案例，這些案例涵蓋了多表關(guān)聯(lián)、子查詢、條件判斷等多種復(fù)雜場(chǎng)景。
到此這篇關(guān)于Oracle高級(jí)語(yǔ)法篇之merge into語(yǔ)句復(fù)雜案例的文章就介紹到這了,更多相關(guān)Oracle merge into語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要給大家介紹了在Oracle 11g下編譯使用BBED的方法教程，文中詳細(xì)介紹了關(guān)于拷貝缺失文件、編譯BBED以及BBED使用測(cè)試的相關(guān)資料，通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友們下面來(lái)一起看看吧。2017-07-07
在開(kāi)發(fā)過(guò)程中，可能會(huì)用到oracle sequence語(yǔ)句,本文以oracle sequence語(yǔ)句如何重置進(jìn)行介紹，需要的朋友可以參考下2012-11-11
Oracle找出一個(gè)表的間接授權(quán)信息的方法
在Oracle中,查詢表授權(quán)需結(jié)合直接權(quán)限和通過(guò)視圖間接傳遞的權(quán)限,直接權(quán)限可通過(guò)ALL_TAB_PRIVS獲取,而間接授權(quán)需遞歸檢查視圖的授權(quán)鏈,本文給大家介紹Oracle找出一個(gè)表的間接授權(quán)信息的方法,感興趣的朋友一起看看吧2025-06-06
oracle中函數(shù) trunc(),round(),ceil(),floor的使用詳解
這篇文章主要介紹了oracle中函數(shù) trunc(),round(),ceil(),floor的使用詳解的相關(guān)資料,需要的朋友可以參考下2017-03-03
oracle數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出命令使用方法
這篇文章主要介紹了oracle數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出命令格式，對(duì)單表導(dǎo)出、多張表導(dǎo)出的方法2013-11-11
rownum是Oracle數(shù)據(jù)庫(kù)中的一個(gè)特有關(guān)鍵字，返回的是一個(gè)數(shù)字代表記錄的行號(hào)。這篇文章主要介紹了oracle 使用rownum的三種分頁(yè)方式,需要的朋友可以參考下2019-10-10
Oracle監(jiān)聽(tīng)器被優(yōu)化大師掛掉后的完美解決方法
這篇文章主要介紹了Oracle監(jiān)聽(tīng)器被優(yōu)化大師掛掉后的完美解決方法,需要的朋友可以參考下2017-08-08
今天小編就為大家分享一篇關(guān)于Oracle中的SUM用法講解，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-04-04
Oracle的sql*plus是與oracle數(shù)據(jù)庫(kù)進(jìn)行交互的客戶端工具，借助sql*plus可以查看、修改數(shù)據(jù)庫(kù)記錄。接下來(lái)通過(guò)本文給大家介紹oracle中sqlplus命令知識(shí)，非常不錯(cuò)，感興趣的朋友一起看看吧2016-09-09
Oracle創(chuàng)建帶有參數(shù)的視圖代碼介紹
這篇文章主要介紹了Oracle創(chuàng)建帶有參數(shù)的視圖代碼介紹，簡(jiǎn)介地介紹了創(chuàng)建方法，具有一定參考價(jià)值，需要的朋友可以了解下。2017-09-09