如何实现每周一自动将Excel数据加载至数据库指定表中

该需求完全可以通过Oracle的DBMS_SCHEDULER实现，具体操作步骤如下：

1. 前置准备 #

• 首先在数据库所在服务器创建固定的文件存放目录，例如/data/weekly_loader，后续每周上传的CSV文件均放在该目录下。
• 在数据库中创建对应目录对象并授权给操作用户：

CREATE OR REPLACE DIRECTORY AUTO_LOAD_DIR AS '/data/weekly_loader';
GRANT READ, WRITE ON DIRECTORY AUTO_LOAD_DIR TO 你的操作用户名;

• 针对你的CSV文件前3行无效、第4行为表头的特点，创建外部表对接CSV文件，无需调用SQL Loader即可直接读取文件内容：

CREATE TABLE LOADER_TAB_EXT (
    i_id NUMBER,
    i_name VARCHAR2(100),
    risk VARCHAR2(100)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY AUTO_LOAD_DIR
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 4 -- 跳过前4行（3行无效内容+1行表头）
        FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('weekly_data.csv') -- 要求每周上传的文件统一命名为该名称
)
REJECT LIMIT UNLIMITED;

注：外部表字段类型、长度需要和LOADER_TAB表完全对齐，可根据实际业务调整上述参数。

2. 创建自动加载存储过程 #

编写存储过程实现文件校验、数据入库、旧文件归档的全流程逻辑：

CREATE OR REPLACE PROCEDURE AUTO_LOAD_WEEKLY_DATA AS
v_file_exists BOOLEAN;
v_file_len NUMBER;
v_block_size BINARY_INTEGER;
BEGIN
    -- 校验文件是否存在
    UTL_FILE.FGETATTR('AUTO_LOAD_DIR', 'weekly_data.csv', v_file_exists, v_file_len, v_block_size);
    IF NOT v_file_exists THEN
        RAISE_APPLICATION_ERROR(-20001, '待加载文件未上传，请检查指定目录');
    END IF;

    -- 全量加载场景先清空目标表，增量加载可删除该行改为去重匹配逻辑
    EXECUTE IMMEDIATE 'TRUNCATE TABLE LOADER_TAB';

    -- 写入目标表
    INSERT INTO LOADER_TAB(i_id, i_name, risk)
    SELECT i_id, i_name, risk FROM LOADER_TAB_EXT;

    -- 归档已加载文件，避免重复加载
    UTL_FILE.FRENAME(
        'AUTO_LOAD_DIR', 'weekly_data.csv',
        'AUTO_LOAD_DIR', 'weekly_data_loaded_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.csv',
        TRUE
    );

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

3. 创建DBMS_SCHEDULER定时任务 #

执行以下语句创建每周一执行的定时任务，可自行调整执行时间：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'WEEKLY_LOAD_DATA_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'AUTO_LOAD_WEEKLY_DATA',
        start_date      => SYSTIMESTAMP,
        -- 规则：每周一上午9点执行，可按需调整BYHOUR、BYMINUTE参数
        repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=9;BYMINUTE=0;BYSECOND=0',
        enabled         => TRUE,
        auto_drop       => FALSE,
        comments        => '每周一自动加载CSV数据到LOADER_TAB表'
    );
END;
/

4. 常用任务管理命令 #

• 查看任务运行状态：

SELECT job_name, state, last_start_date, next_run_date 
FROM USER_SCHEDULER_JOBS 
WHERE JOB_NAME = 'WEEKLY_LOAD_DATA_JOB';

• 手动触发任务测试：EXEC DBMS_SCHEDULER.RUN_JOB('WEEKLY_LOAD_DATA_JOB');
• 停用任务：EXEC DBMS_SCHEDULER.DISABLE('WEEKLY_LOAD_DATA_JOB');
• 启用任务：EXEC DBMS_SCHEDULER.ENABLE('WEEKLY_LOAD_DATA_JOB');

注意事项 #

• 需确保Oracle数据库的运行用户对服务器上的指定文件目录有读写权限
• 每周上传的Excel文件需要先导出为CSV格式，命名为weekly_data.csv放入指定目录即可
• 如果需要直接加载xlsx格式的Excel文件，可引入开源PL/SQL工具包as_read_xlsx读取Excel内容，无需提前转CSV

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