Oracle外部表新增列：无需预处理CSV直接提取文件名中的日期时间

当然可以！完全不用预先修改CSV文件，Oracle外部表就能帮你实现这个需求。核心思路是借助Oracle外部表自带的FILENAME伪列获取每个CSV文件的名称，再通过正则表达式从文件名中提取日期和时间信息，直接映射为外部表的新列。

1. 先明确文件名格式（示例假设） #

假设你的CSV文件名格式类似 sales_records_20240520_1430.csv：其中日期为YYYYMMDD格式，时间为HHMM格式，每个CSV内部包含3列业务数据，比如：

1001,笔记本电脑,4500
1002,无线鼠标,89

2. 创建带日期/时间列的外部表 #

下面是可直接参考的SQL示例，记得替换成你自己的目录对象和文件名规则：

CREATE TABLE ext_business_data (
    product_id NUMBER,
    product_name VARCHAR2(100),
    price NUMBER,
    record_date DATE,  -- 从文件名提取的日期列
    record_time VARCHAR2(5)  -- 从文件名提取的时间列
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY csv_data_dir  -- 替换为你存放CSV的Oracle目录对象
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 0  -- 如果CSV有表头，这里改成1跳过第一行
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
            product_id,
            product_name,
            price,
            -- 从文件名提取日期：匹配YYYYMMDD格式的子串并转为DATE类型
            record_date EXPRESSION "TO_DATE(REGEXP_SUBSTR(:FILENAME, '([0-9]{8})'), 'YYYYMMDD')",
            -- 从文件名提取时间：匹配HHMM格式的子串，转为HH:MM格式
            record_time EXPRESSION "REGEXP_REPLACE(REGEXP_SUBSTR(:FILENAME, '([0-9]{4})', 1, 2), '(..)(..)', '\\1:\\2')"
        )
    )
    LOCATION ('sales_records_*.csv')  -- 匹配目录下所有目标CSV文件
)
REJECT LIMIT UNLIMITED;

3. 关键逻辑解释 #

• FILENAME伪列：Oracle的ORACLE_LOADER驱动会自动提供这个伪列，它代表当前读取的CSV文件的完整名称，是我们提取信息的核心依据。
• EXPRESSION子句：允许我们用SQL表达式计算列值，这里用REGEXP_SUBSTR从文件名中匹配日期、时间片段，再通过TO_DATE和REGEXP_REPLACE调整格式。
• 灵活适配不同文件名：如果你的文件名格式不同（比如日期是DD-MM-YYYY、时间带冒号），只需要修改正则表达式的匹配规则和日期转换的格式掩码即可。比如文件名是data_20-05-2024_14:30.csv，日期的表达式可以改成：

  record_date EXPRESSION "TO_DATE(REGEXP_SUBSTR(:FILENAME, '(\\d{2}-\\d{2}-\\d{4})'), 'DD-MM-YYYY')"
  

4. 查询验证 #

创建完成后，直接查询外部表就能看到包含日期、时间列的完整数据：

SELECT * FROM ext_business_data;

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