Oracle 银行场景拉链表（基于SCD2规则，某大行生产环境）

一、业务场景银行标准维度银行客户信息维度拉链表跟踪客户等级、风险等级、联系方式、地址、职业、客户经理等缓慢变化拉链规则start_date记录生效日期包含end_date记录失效日期不包含end_date TO_DATE(9999-12-31,YYYY-MM-DD)当前有效适用监管审计、历史回溯、客户画像、风险追溯二、Oracle 建表语句拉链主表-- 创建数据仓库用户建议专用账号已存在可跳过 CREATE USER dw IDENTIFIED BY dw123 DEFAULT TABLESPACE users; GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PROCEDURE TO dw; -- 切换用户 CONN dw/dw123; -- 银行客户信息拉链表 DROP TABLE dw_cust_zip PURGE; CREATE TABLE dw_cust_zip ( id NUMBER(18) NOT NULL, -- 主键ID cust_id VARCHAR2(32) NOT NULL, -- 客户唯一标识业务主键 cust_name VARCHAR2(100) NOT NULL, -- 客户姓名 cert_type VARCHAR2(10), -- 证件类型 01身份证 cert_no VARCHAR2(32), -- 证件号码 cust_level VARCHAR2(10), -- 客户等级 A/B/C/D risk_level VARCHAR2(10), -- 风险等级 低/中/高 occupation VARCHAR2(50), -- 职业 manager_id VARCHAR2(32), -- 客户经理编号 manager_name VARCHAR2(50), -- 客户经理姓名 phone VARCHAR2(20), -- 手机号 address VARCHAR2(255), -- 联系地址 start_date DATE NOT NULL, -- 生效日期 end_date DATE NOT NULL, -- 失效日期 is_current CHAR(1) NOT NULL, -- 1当前有效 0失效 create_time DATE DEFAULT SYSDATE, -- 入库时间 CONSTRAINT pk_dw_cust_zip PRIMARY KEY (id), CONSTRAINT uk_cust_start UNIQUE (cust_id, start_date) ); -- 创建索引提升查询速度 CREATE INDEX idx_is_current ON dw_cust_zip(is_current); CREATE INDEX idx_date_range ON dw_cust_zip(start_date, end_date); -- 创建序列Oracle 主键自增用 DROP SEQUENCE seq_dw_cust_zip; CREATE SEQUENCE seq_dw_cust_zip START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10;三、触发器实现主键自增CREATE OR REPLACE TRIGGER trg_cust_zip_id BEFORE INSERT ON dw_cust_zip FOR EACH ROW BEGIN IF :NEW.id IS NULL THEN SELECT seq_dw_cust_zip.NEXTVAL INTO :NEW.id FROM DUAL; END IF; END; /四、存储过程 1首次全量初始化基线CREATE OR REPLACE PROCEDURE p_cust_zip_init( p_start_date IN DATE -- 基线生效日期 ) AS BEGIN -- 清空基线 EXECUTE IMMEDIATE TRUNCATE TABLE dw_cust_zip; -- 全量导入客户数据 -- 源表ODS层银行客户全量表替换成你真实表 INSERT INTO dw_cust_zip ( cust_id, cust_name, cert_type, cert_no, cust_level, risk_level, occupation, manager_id, manager_name, phone, address, start_date, end_date, is_current ) SELECT cust_id, cust_name, cert_type, cert_no, cust_level, risk_level, occupation, manager_id, manager_name, phone, address, p_start_date, TO_DATE(9999-12-31,YYYY-MM-DD), 1 FROM ods.ods_customer_full; COMMIT; DBMS_OUTPUT.PUT_LINE(初始化完成基线日期 || TO_CHAR(p_start_date,YYYY-MM-DD)); EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(初始化失败 || SQLERRM); END; /五、存储过程 2每日增量更新核心拉链逻辑CREATE OR REPLACE PROCEDURE p_cust_zip_refresh( p_biz_date IN DATE -- 业务日期 前一天 ) AS v_new_start_date DATE; v_max_end_date DATE : TO_DATE(9999-12-31,YYYY-MM-DD); BEGIN -- 新记录生效日期 业务日期 1 v_new_start_date : p_biz_date 1; -- -- 1. 创建临时表存放当日变更增量客户 -- EXECUTE IMMEDIATE CREATE GLOBAL TEMPORARY TABLE tmp_cust_delta ( cust_id VARCHAR2(32) PRIMARY KEY, cust_name VARCHAR2(100), cert_type VARCHAR2(10), cert_no VARCHAR2(32), cust_level VARCHAR2(10), risk_level VARCHAR2(10), occupation VARCHAR2(50), manager_id VARCHAR2(32), manager_name VARCHAR2(50), phone VARCHAR2(20), address VARCHAR2(255) ) ON COMMIT DELETE ROWS; -- 加载增量数据变更/新增客户 INSERT INTO tmp_cust_delta SELECT cust_id, cust_name, cert_type, cert_no, cust_level, risk_level, occupation, manager_id, manager_name, phone, address FROM ods.ods_customer_inc WHERE dt TRUNC(p_biz_date); -- 无增量直接退出 IF SQL%ROWCOUNT 0 THEN DBMS_OUTPUT.PUT_LINE(无变更数据执行结束); RETURN; END IF; -- -- 2. 事务关闭旧记录 插入新记录 -- BEGIN -- 关闭旧有效记录 UPDATE dw_cust_zip SET end_date p_biz_date, is_current 0 WHERE is_current 1 AND cust_id IN (SELECT cust_id FROM tmp_cust_delta); -- 插入新记录 INSERT INTO dw_cust_zip ( cust_id, cust_name, cert_type, cert_no, cust_level, risk_level, occupation, manager_id, manager_name, phone, address, start_date, end_date, is_current ) SELECT cust_id, cust_name, cert_type, cert_no, cust_level, risk_level, occupation, manager_id, manager_name, phone, address, v_new_start_date, v_max_end_date, 1 FROM tmp_cust_delta; COMMIT; DBMS_OUTPUT.PUT_LINE(执行成功业务日期 || TO_CHAR(p_biz_date,YYYY-MM-DD) || 新生效日 || TO_CHAR(v_new_start_date,YYYY-MM-DD)); EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(更新失败 || SQLERRM); END; -- 删除临时表 EXECUTE IMMEDIATE DROP TABLE tmp_cust_delta; END; /六、调用示例直接执行1. 初始化基线SET SERVEROUTPUT ON; EXEC p_cust_zip_init(TO_DATE(2025-01-01,YYYY-MM-DD));2. 每日更新传前一天日期SET SERVEROUTPUT ON; EXEC p_cust_zip_refresh(TO_DATE(2025-04-01,YYYY-MM-DD));七、标准查询 SQL银行常用1. 查询当前最新有效客户SELECT * FROM dw_cust_zip WHERE is_current 1;2. 历史快照查询监管 / 审计必备SELECT * FROM dw_cust_zip WHERE start_date TO_DATE(2025-03-01,YYYY-MM-DD) AND end_date TO_DATE(2025-03-01,YYYY-MM-DD);3. 单个客户全生命周期变更轨迹SELECT * FROM dw_cust_zip WHERE cust_id CUST202500001 ORDER BY start_date;4. 统计客户变更次数SELECT cust_id, cust_name, COUNT(*) change_times FROM dw_cust_zip GROUP BY cust_id, cust_name ORDER BY change_times DESC;八、银行场景特别说明合规性不删除、不修改历史记录满足监管留存要求数据安全建议对cert_no、phone做脱敏处理调度建议每日凌晨 2 点自动执行传入前一日日期分区优化数据量大可按end_date分区提升查询速度