Oracle PL/SQL创建存储过程报ORA-00904 QUARTER标识符无效如何解决

报错根本原因 #

• ORA-00904: "QUARTER": invalid identifier错误的核心原因是Oracle数据库无内置QUARTER()函数，该函数是MySQL等其他数据库的内置能力，Oracle需要通过日期格式化函数实现季度提取。
• 除QUARTER外，代码中使用的dayofweek、dayofmonth、dayofyear、dayname、monthname、month都不是Oracle的内置日期函数，全部需要替换为Oracle兼容的语法。
• 代码中monthname(start_date + i) + i逻辑存在错误：月份名称是字符串类型，无法直接和数字做加法运算，属于笔误需要修正。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE pop_date_dim (DDMMYYYY IN number, n IN number)
IS 
start_date date;
BEGIN
start_date := TO_DATE(DDMMYYYY, 'DDMMYYYY');
    FOR i IN 1..n LOOP
        -- 请将下列INSERT语句中的列名替换为date_dim表的实际列名，显式指定列可避免表结构变更后存储过程失效
        INSERT INTO date_dim(日期列, 周几列, 月内天数列, 年内天数列, 星期名称列, 月份数字列, 月份名称列, 年份列, 季度列)
            VALUES ( 
            start_date + i, 
            -- 周几：默认周日为1，需要周一为1可调整为TO_NUMBER(TO_CHAR(start_date + i, 'D')) - 1，周日单独赋值7即可
            TO_NUMBER(TO_CHAR(start_date + i, 'D')),
            -- 当月第几天
            TO_NUMBER(TO_CHAR(start_date + i, 'DD')),
            -- 当年第几天
            TO_NUMBER(TO_CHAR(start_date + i, 'DDD')),
            -- 星期名称，需要中文可加NLS参数：TO_CHAR(start_date + i, 'DAY', 'NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE')
            TRIM(TO_CHAR(start_date + i, 'DAY')),
            -- 月份数字1-12
            TO_NUMBER(TO_CHAR(start_date + i, 'MM')),
            -- 月份名称，已删除错误的+i逻辑，若有特殊需求可自行调整
            TRIM(TO_CHAR(start_date + i, 'MONTH')),
            -- 年份
            TO_NUMBER(TO_CHAR(start_date + i, 'YYYY')),
            -- 季度1-4
            TO_NUMBER(TO_CHAR(start_date + i, 'Q'))
            ); 
    END LOOP;
    -- 可根据事务需求调整是否提交
    COMMIT;
END;
/

优化建议 #

批量插入日期维度数据时，不建议使用FOR循环逐行插入，当n数值较大时性能极差，可使用Oracle的CONNECT BY语法一次性生成所有日期批量插入，性能比逐行插入提升数十倍以上，示例如下：

CREATE OR REPLACE PROCEDURE pop_date_dim (DDMMYYYY IN number, n IN number)
IS 
start_date date;
BEGIN
start_date := TO_DATE(DDMMYYYY, 'DDMMYYYY');
INSERT INTO date_dim(日期列, 周几列, 月内天数列, 年内天数列, 星期名称列, 月份数字列, 月份名称列, 年份列, 季度列)
SELECT 
    dt,
    TO_NUMBER(TO_CHAR(dt, 'D')),
    TO_NUMBER(TO_CHAR(dt, 'DD')),
    TO_NUMBER(TO_CHAR(dt, 'DDD')),
    TRIM(TO_CHAR(dt, 'DAY')),
    TO_NUMBER(TO_CHAR(dt, 'MM')),
    TRIM(TO_CHAR(dt, 'MONTH')),
    TO_NUMBER(TO_CHAR(dt, 'YYYY')),
    TO_NUMBER(TO_CHAR(dt, 'Q'))
FROM (
    SELECT start_date + LEVEL dt
    FROM dual
    CONNECT BY LEVEL <= n
);
COMMIT;
END;
/

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