Oracle中如何实现夜间任务仅插入新增数据至目标表？

一、实现仅插入新增记录的SQL逻辑 #

要跳过table_b中已存在的记录，核心是通过记录唯一性判断过滤掉已存在的数据，以下两种常用方法：

方法1：INSERT + NOT EXISTS（推荐简单场景） #

通过NOT EXISTS子查询判断记录是否已存在于table_b，仅插入不存在的记录。需根据table_b的实际唯一键调整判断条件，示例假设唯一键为id（来自table_a的id）：

INSERT INTO table_b
SELECT 
    a.id, a.changed, a.column_name, a.identification, 
    a.old_text, a.new_text, d.id, d.no, d.name, d.status, d.status_date 
FROM table_a a 
INNER JOIN table_d d ON d.id = a.id_double1
-- 注意：修正原WHERE条件的逻辑歧义，若需求是仅DEVICE表的STATUS/STATUS_DATE变更，需加括号
WHERE table_name = 'DEVICE' AND (column_name = 'STATUS' OR column_name = 'STATUS_DATE')
AND NOT EXISTS (
    SELECT 1 FROM table_b b
    -- 替换为table_b的实际唯一键判断，比如组合键可写：b.id = a.id AND b.column_name = a.column_name
    WHERE b.id = a.id
);

方法2：MERGE语句（灵活支持插入/更新） #

若后续需要支持更新已存在记录的场景，MERGE更适合，仅需在WHEN NOT MATCHED分支执行插入：

MERGE INTO table_b b
USING (
    SELECT 
        a.id, a.changed, a.column_name, a.identification, 
        a.old_text, a.new_text, d.id AS d_id, d.no, d.name, d.status, d.status_date 
    FROM table_a a 
    INNER JOIN table_d d ON d.id = a.id_double1
    WHERE table_name = 'DEVICE' AND (column_name = 'STATUS' OR column_name = 'STATUS_DATE')
) src
ON (b.id = src.id) -- 同样替换为实际唯一键匹配条件
WHEN NOT MATCHED THEN
INSERT (id, changed, column_name, identification, old_text, new_text, d_id, no, name, status, status_date)
VALUES (src.id, src.changed, src.column_name, src.identification, src.old_text, src.new_text, src.d_id, src.no, src.name, src.status, src.status_date);

二、配置夜间定时任务 #

使用Oracle自带的DBMS_SCHEDULER创建定时任务，步骤如下：

1. 创建存储过程封装插入逻辑 #

把上面的SQL逻辑封装为存储过程，方便任务调用：

CREATE OR REPLACE PROCEDURE insert_into_table_b AS
BEGIN
    -- 复制上面的INSERT或MERGE语句到这里，示例用INSERT+NOT EXISTS版本
    INSERT INTO table_b
    SELECT 
        a.id, a.changed, a.column_name, a.identification, 
        a.old_text, a.new_text, d.id, d.no, d.name, d.status, d.status_date 
    FROM table_a a 
    INNER JOIN table_d d ON d.id = a.id_double1
    WHERE table_name = 'DEVICE' AND (column_name = 'STATUS' OR column_name = 'STATUS_DATE')
    AND NOT EXISTS (
        SELECT 1 FROM table_b b
        WHERE b.id = a.id
    );
    COMMIT; -- 按需提交事务，若业务需要原子性可保留
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 出错时回滚
        -- 可选：添加错误日志记录，比如插入到自定义日志表
        -- INSERT INTO job_error_log (job_name, error_msg, error_time) VALUES ('INSERT_TABLE_B_NIGHTLY', SQLERRM, SYSDATE);
        -- COMMIT;
END;
/

2. 创建定时任务 #

示例设置为每天凌晨2点执行，可根据需求调整时间规则：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'INSERT_TABLE_B_NIGHTLY', -- 任务名称，唯一即可
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'insert_into_table_b', -- 调用的存储过程名
        start_date      => SYSTIMESTAMP, -- 任务开始时间，立即生效
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0;', -- 每天2点执行
        enabled         => TRUE, -- 创建后立即启用
        comments        => '夜间定时增量插入table_b的任务'
    );
END;
/

时间规则调整示例：

• 每周一至周五凌晨2点：'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=2; BYMINUTE=0; BYSECOND=0;'
• 每月1号凌晨3点：'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=3; BYMINUTE=0; BYSECOND=0;'

3. 权限说明 #

若执行时提示权限不足，需联系DBA授予以下权限：

GRANT CREATE PROCEDURE TO 你的用户名;
GRANT CREATE JOB TO 你的用户名;
GRANT EXECUTE ON DBMS_SCHEDULER TO 你的用户名;

三、注意事项 #

• 必须明确table_b的唯一标识，否则会导致重复插入或漏插，若为组合键需同步调整NOT EXISTS或MERGE的匹配条件。
• 原WHERE条件存在逻辑歧义，务必确认业务需求后调整括号位置，避免引入不符合要求的数据。
• 可通过DBMS_SCHEDULER.DROP_JOB('INSERT_TABLE_B_NIGHTLY');删除任务，DBMS_SCHEDULER.DISABLE('INSERT_TABLE_B_NIGHTLY');临时禁用任务。

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