如何从XX_EMPLOYEES、Jobs表向PROFESSION表插入指定数据？

问题背景 #

现有Oracle数据库三张表：XX_EMPLOYEES、Jobs、PROFESSION，表结构及XX_EMPLOYEES、Jobs的初始化数据如下。需要将指定的员工职位关联数据插入PROFESSION表，该表约束如下：

• EMP_ID、EFFECTIVE_FROM、EFFECTIVE_TO为必填项
• EFFECTIVE_FROM必须小于EFFECTIVE_TO
• 同一员工的有效日期记录不能重叠

用户尝试了以下SQL但未成功：

INSERT INTO PROFESSION(EMP_ID,JOB_ID,STAFF)
VALUES ((SELECT EMP_ID FROM XX_EMPLOYEES WHERE EMP_ID=1003),(SELECT JOB_ID FROM JOBS WHERE JOB_ID=102),(SELECT EMP_FIRST_NAME FROM XX_EMPLOYEES WHERE EMP_ID=1003))

需插入的目标数据（对应字段：员工姓名、职位名称、经理姓名、生效起始日、生效结束日）：

(Tomm, General Manager,null,01-Jan-2000,null)
(Mohammed, Senior Accountant, Tomm,01-Jan-2010, Null)
(Ali, Administration, Tomm,01-Jan-2000,Null)
(Basel, Accountant, Mohammed,01-Mar-2007,Null)

表结构与初始化SQL #

XX_EMPLOYEES表 #

CREATE TABLE XX_EMPLOYEES
( EMP_ID NUMBER NOT NULL, EMP_FIRST_NAME VARCHAR2(250) NOT NULL, EMP_MIDDLE_NAME VARCHAR2(250) NOT NULL, EMP_LAST_NAME VARCHAR2(250) NOT NULL, Hired_Date DATE NOT NULL, Country VARCHAR2(250) NOT NULL, Salary NUMBER NOT NULL );
INSERT ALL
INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (1,'Tomm','Jef','Adam','01-Jan-2016','JORDAN',1000) 
INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (2,'Mohammed','Ahmed','Mahmoud','15-Jul-2009','UAE',900) 
INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (4,'Ali','Ahmad','Mahmoud','07-Jul-2000','UK',1200) 
INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (10,'Basel','Jamal','Saeed','10-Apr-2001','UAE',1000)
SELECT * FROM dual;

Jobs表 #

CREATE TABLE Jobs
(
    JOB_ID NUMBER NOT NULL,
    JOB_Description VARCHAR2(250) NOT NULL
);
INSERT ALL
INTO Jobs (Job_ID, Job_Description) VALUES (1, 'Accountant')
INTO Jobs (Job_ID, Job_Description) VALUES (2, 'General Manager')
INTO Jobs (Job_ID, Job_Description) VALUES (3, 'Administration')
INTO Jobs (Job_ID, Job_Description) VALUES (4, 'Senior Accountant')
SELECT * FROM dual;

PROFESSION表 #

CREATE TABLE  PROFESSION
(     EMP_ID  NUMBER NOT NULL, 
     JOB_ID  NUMBER NOT NULL, 
     MANAGER_ID NUMBER, 
     EFFECTIVE_FROM DATE NOT NULL, 
     EFFECTIVE_TO DATE NOT NULL, 
     CONSTRAINT RESTRICT CHECK (EFFECTIVE_FROM < EFFECTIVE_TO)
   )

原SQL的问题分析 #

1. 字段不匹配：PROFESSION表没有STAFF字段，正确字段应为MANAGER_ID；同时缺少必填的EFFECTIVE_FROM和EFFECTIVE_TO字段，违反非空约束。
2. 无效子查询：子查询中EMP_ID=1003、JOB_ID=102在现有表中无对应数据，实际Tomm的EMP_ID是1，Mohammed是2，Ali是4，Basel是10。
3. 数据类型错误：MANAGER_ID是数字类型，原SQL插入的是员工姓名（字符串），类型不匹配。
4. 约束未满足：PROFESSION表要求EFFECTIVE_TO非空，但目标数据中为Null，且未处理EFFECTIVE_FROM < EFFECTIVE_TO的约束。

正确实现SQL #

使用INSERT ... SELECT批量插入，关联其他表获取对应ID，同时处理约束要求：

INSERT INTO PROFESSION (EMP_ID, JOB_ID, MANAGER_ID, EFFECTIVE_FROM, EFFECTIVE_TO)
SELECT
    emp.EMP_ID,
    job.JOB_ID,
    mgr.EMP_ID AS MANAGER_ID,
    TO_DATE(target.eff_from, 'DD-Mon-YYYY'),
    TO_DATE(NVL(target.eff_to, '31-Dec-9999'), 'DD-Mon-YYYY')
FROM (
    -- 构造目标数据集，对应：员工姓名、职位名称、经理姓名、生效起始日、生效结束日
    SELECT 'Tomm' AS emp_name, 'General Manager' AS job_name, NULL AS mgr_name, '01-Jan-2000' AS eff_from, NULL AS eff_to FROM dual
    UNION ALL
    SELECT 'Mohammed' AS emp_name, 'Senior Accountant' AS job_name, 'Tomm' AS mgr_name, '01-Jan-2010' AS eff_from, NULL AS eff_to FROM dual
    UNION ALL
    SELECT 'Ali' AS emp_name, 'Administration' AS job_name, 'Tomm' AS mgr_name, '01-Jan-2000' AS eff_from, NULL AS eff_to FROM dual
    UNION ALL
    SELECT 'Basel' AS emp_name, 'Accountant' AS job_name, 'Mohammed' AS mgr_name, '01-Mar-2007' AS eff_from, NULL AS eff_to FROM dual
) target
-- 关联员工表获取EMP_ID
JOIN XX_EMPLOYEES emp ON emp.EMP_FIRST_NAME = target.emp_name
-- 关联职位表获取JOB_ID
JOIN Jobs job ON job.JOB_Description = target.job_name
-- 左连接经理表获取经理EMP_ID（允许经理为Null）
LEFT JOIN XX_EMPLOYEES mgr ON mgr.EMP_FIRST_NAME = target.mgr_name;

关键说明 #

• 约束处理：用NVL将Null的EFFECTIVE_TO替换为远未来日期31-Dec-9999，满足非空和EFFECTIVE_FROM < EFFECTIVE_TO的要求。
• 关联匹配：通过员工姓名、职位名称关联对应表获取ID，若存在重名员工，需补充姓氏等字段确保唯一匹配。
• 批量插入：一次性插入所有记录，效率更高且避免单条插入的错误。

内容的提问来源于stack exchange，提问作者lilpupper