如何在Oracle SQL表中生成并插入100年的月年数据

需求说明 #

需向包含record、month、year、startdate、enddate列的Oracle表插入月度数据，每行对应一个月份：

• startdate固定为当月1日
• enddate为当月最后一日，2月需自动识别闰年，闰年时为29日

批量生成插入方案（推荐） #

使用递归CTE可以批量生成任意时间段的月度数据，无需手动逐行编写：

1. 创建目标表（若未创建） #

CREATE TABLE monthly_records (
    record NUMBER,
    month VARCHAR2(3),
    year NUMBER,
    startdate DATE,
    enddate DATE
);

2. 递归生成并插入数据 #

WITH date_range AS (
    -- 初始行：1970年1月
    SELECT 
        0 AS record,
        TO_CHAR(TO_DATE('01-01-1970', 'DD-MM-YYYY'), 'Mon', 'NLS_DATE_LANGUAGE=ENGLISH') AS month,
        EXTRACT(YEAR FROM TO_DATE('01-01-1970', 'DD-MM-YYYY')) AS year,
        TO_DATE('01-01-1970', 'DD-MM-YYYY') AS startdate,
        LAST_DAY(TO_DATE('01-01-1970', 'DD-MM-YYYY')) AS enddate
    FROM DUAL
    UNION ALL
    -- 递归生成后续月份
    SELECT 
        record + 1,
        TO_CHAR(ADD_MONTHS(startdate, 1), 'Mon', 'NLS_DATE_LANGUAGE=ENGLISH'),
        EXTRACT(YEAR FROM ADD_MONTHS(startdate, 1)),
        ADD_MONTHS(startdate, 1),
        LAST_DAY(ADD_MONTHS(startdate, 1))
    FROM date_range
    -- 可调整终止条件，比如生成到2024年12月就加 WHERE startdate < TO_DATE('01-01-2025', 'DD-MM-YYYY')
)
INSERT INTO monthly_records (record, month, year, startdate, enddate)
SELECT record, month, year, startdate, enddate
FROM date_range;

关键函数说明

• LAST_DAY(date)：自动返回指定日期所在月份的最后一天，Oracle会自动处理闰年，2月闰年时返回29日
• TO_CHAR(..., 'Mon', 'NLS_DATE_LANGUAGE=ENGLISH')：确保月份缩写为英文（Jan/Feb等），不受数据库语言设置影响
• ADD_MONTHS(date, n)：用于生成下一个月的起始日期

手动插入指定行方案 #

如果只需要插入1970年1月和2月的两行数据，可直接写INSERT语句，同样用LAST_DAY处理闰年：

-- 插入1970年1月数据
INSERT INTO monthly_records (record, month, year, startdate, enddate)
VALUES (0, 'Jan', 1970, TO_DATE('01/01/1970', 'DD/MM/YYYY'), TO_DATE('31/01/1970', 'DD/MM/YYYY'));

-- 插入1970年2月数据（自动适配闰年）
INSERT INTO monthly_records (record, month, year, startdate, enddate)
VALUES (1, 'Feb', 1970, TO_DATE('01/02/1970', 'DD/MM/YYYY'), LAST_DAY(TO_DATE('01/02/1970', 'DD/MM/YYYY')));

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