如何用PL/SQL实现SCD：管理员工薪资更新与部门变更并保留历史

一、部门变更：用SCD Type 2搞定历史留存 #

你之前用的SCD1是直接覆盖数据，没法留存变更历史，现在要保留部门变动记录，SCD Type 2是标准解决方案——每次部门变动时生成新记录，同时把旧记录标记为失效状态。

关键思路 #

先给员工维度表新增三个核心字段：

• start_date：记录本条数据的生效日期
• end_date：记录本条数据的失效日期（当前生效的记录可设为9999-12-31这类极大值）
• is_current：标识本条数据是否为当前生效状态（用Y/N区分）

处理逻辑分两步：

1. 定位源数据中部门发生变更的员工（含新入职员工）
2. 将该员工当前生效的旧记录的end_date设为变更前一天，is_current改为N；再插入一条带新部门信息的新记录，设置start_date为变更当天、end_date为极大值、is_current为Y

PL/SQL代码示例 #

假设源表stg_employee存储最新员工数据，目标维度表dim_employee存储带历史的员工数据：

DECLARE
    -- 抓取部门变更的员工（包含新入职员工）
    CURSOR c_dept_changes IS
        SELECT s.emp_id, s.dept_id, s.salary
        FROM stg_employee s
        LEFT JOIN dim_employee d 
            ON s.emp_id = d.emp_id AND d.is_current = 'Y'
        WHERE d.dept_id IS NULL -- 新员工无历史记录
           OR d.dept_id != s.dept_id; -- 老员工部门变更
BEGIN
    FOR rec IN c_dept_changes LOOP
        -- 标记旧的有效记录为失效
        UPDATE dim_employee
        SET end_date = TRUNC(SYSDATE) - 1,
            is_current = 'N'
        WHERE emp_id = rec.emp_id AND is_current = 'Y';
        
        -- 插入新的有效记录
        INSERT INTO dim_employee (emp_id, dept_id, salary, start_date, end_date, is_current)
        VALUES (rec.emp_id, rec.dept_id, rec.salary, TRUNC(SYSDATE), DATE '9999-12-31', 'Y');
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 抛出异常便于排查问题
END;
/

二、薪资变更：根据业务需求选择SCD类型 #

选SCD Type 2：需要完整薪资历史 #

如果业务要求追踪员工每一次薪资调整的全记录（比如审计、合规需求），直接复用部门变更的SCD2逻辑即可——每次薪资变动生成新记录，标记旧记录失效，完整留存薪资变动轨迹。

选SCD Type 1：只关注当前薪资状态 #

如果不需要薪资历史，仅需保留最新薪资数据，就用SCD1的方式：直接定位到员工当前生效的记录，更新salary字段即可，无需生成新记录，操作简单高效。

选SCD Type 6：兼顾历史与当前便捷性 #

如果想同时满足“可查薪资历史”和“快速获取当前薪资”的需求，可采用SCD6（俗称1+2+3混合类型）：

• 用SCD2的方式存储所有薪资历史版本
• 在当前生效的记录中，要么直接更新salary字段（历史版本保留旧值），要么新增current_salary字段专门存储最新薪资，方便报表直接取数

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