Oracle存储过程CTE(WITH DAYSINMONTH)编译错误求助

核心错误点分析 #

• CTE作用域限制：Oracle的CTE（WITH子句）仅对紧随其后的第一条SQL语句生效，原代码中第一个CTE后连续写了两个INSERT语句，第二个INSERT无法引用已失效的CTE，这是编译报错的主要原因。
• 字符串拼接错误：日期初始化时用+拼接字符串，Oracle中字符串拼接必须用||，且原拼接结果格式与TO_DATE指定的格式不匹配。
• 无效裸SELECT：存储过程中不能直接执行未处理结果的SELECT语句，必须将结果存入变量或游标。
• 注释语法错误：中间的************--...--是无效注释格式，会干扰编译。
• UPDATE语句语法错误：更新语句未指定目标表别名的来源，存在多余的日期转换、格式符误用（WW是周数，不是星期几）。

修正后的完整存储过程 #

PROCEDURE getCalendar 
(        
    V_Year                 VARCHAR2,
    V_Month                VARCHAR2,
    V_LoginId              VARCHAR2,
    curAttendanceDate      OUT T_CURSOR,
    res                    OUT SYS_REFCURSOR,
    sud                    OUT SYS_REFCURSOR,
    cur2                   OUT SYS_REFCURSOR,
    cur                    OUT SYS_REFCURSOR
)
AS
    V_date Date := TO_DATE(V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd'); -- 修正字符串拼接
BEGIN
    -- 第一个CTE仅作用于后续的INSERT语句
    WITH DaysInMonth (dates) AS 
    (
        SELECT V_Date AS dates FROM DUAL
        UNION ALL
        SELECT dates + INTERVAL '1' DAY
        FROM DaysInMonth
        WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1)
    )
    INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate, ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
    SELECT u.LoginID, d.dates, '', NULL, NULL, 1, 'Approved'
    FROM DaysInMonth d
    CROSS JOIN T_ATTENDANCE_USER_MASTER u
    WHERE EXTRACT(MONTH FROM dates) = EXTRACT(MONTH FROM V_Date)
      AND u.LoginId = V_LoginId
      AND NOT EXISTS (
          SELECT 1 
          FROM T_ATTENDANCE_ATTENDANCE 
          WHERE LoginId = V_LoginId
            AND EXTRACT(YEAR FROM ATTENDANCEDATE) = EXTRACT(YEAR FROM V_Date)
            AND EXTRACT(MONTH FROM AttendanceDate) = EXTRACT(MONTH FROM V_Date)
            AND ACTIVE = 1
      );

    -- 第二个INSERT重新定义CTE
    WITH DaysInMonth (dates) AS 
    (
        SELECT V_Date AS dates FROM DUAL
        UNION ALL
        SELECT dates + INTERVAL '1' DAY
        FROM DaysInMonth
        WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1)
    )
    INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate, ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
    SELECT u.LoginID, d.dates, '', NULL, NULL, 1, 'Approved'
    FROM DaysInMonth d
    CROSS JOIN T_ATTENDANCE_USER_MASTER u
    WHERE EXTRACT(MONTH FROM dates) = EXTRACT(MONTH FROM V_Date)
      AND u.LoginId = V_LoginId
      AND NOT EXISTS (
          SELECT 1 
          FROM T_ATTENDANCE_ATTENDANCE 
          WHERE LoginId = V_LoginId
            AND EXTRACT(YEAR FROM ATTENDANCEDATE) = EXTRACT(YEAR FROM V_date)
            AND EXTRACT(MONTH FROM AttendanceDate) = EXTRACT(MONTH FROM V_date)
            AND ACTIVE = 1
      );

    V_Date := ADD_MONTHS(V_Date, 1); -- 替换原INTERVAL写法，符合Oracle习惯

    -- 将日期列表输出到指定游标
    WITH DaysInMonth (dates) AS 
    (
        SELECT V_Date AS dates FROM DUAL
        UNION ALL
        SELECT dates + INTERVAL '1' DAY
        FROM DaysInMonth
        WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1)
    )
    OPEN res FOR
        SELECT dates FROM DaysInMonth;

    -- 打开考勤日期游标
    OPEN curAttendanceDate FOR
        SELECT AttendanceDate,
               ShiftName,
               TO_CHAR(AttendanceDate, 'ddd') cDAY,
               V_Year,
               V_Month,
               TO_CHAR(SignIn, 'yyyy-MM-dd hh:mm:ss tt') SignIn,
               TO_CHAR(SignOut, 'yyyy-MM-dd hh:mm:ss tt') SignOut
        FROM T_ATTENDANCE_ATTENDANCE
        WHERE LoginId = V_LoginId
          AND EXTRACT(YEAR FROM ATTENDANCEDATE) = TO_NUMBER(V_Year)
          AND EXTRACT(MONTH FROM AttendanceDate) = TO_NUMBER(V_Month)
          AND ACTIVE = 1
        ORDER BY AttendanceDate ASC;

    -- 修正UPDATE语句语法与逻辑错误
    UPDATE t_attendance_attendance a
    SET a.shiftname = (
        SELECT CASE 
                 WHEN TRIM(TO_CHAR(a.attendancedate, 'DAY')) = 'SATURDAY' AND sub.week IN (2,4) THEN 'WEEKLYOFF'
                 WHEN TRIM(TO_CHAR(a.attendancedate, 'DAY')) = 'SUNDAY' THEN 'WEEKLYOFF'
                 ELSE 'GENERAL1' 
               END
        FROM (
            SELECT attendancedate,
                   ROW_NUMBER() OVER (PARTITION BY TRIM(TO_CHAR(attendancedate, 'DAY')) ORDER BY attendancedate) AS week
            FROM t_attendance_attendance a1
            LEFT JOIN t_attendance_user_master u ON u.loginid = a1.loginid
            LEFT JOIN t_attendance_employee_master e ON e.empid = u.empid
            WHERE a1.loginid = v_loginid
              AND e.company IS NULL
              AND EXTRACT(YEAR FROM a1.attendancedate) = EXTRACT(YEAR FROM SYSDATE)
              AND EXTRACT(MONTH FROM a1.attendancedate) = EXTRACT(MONTH FROM SYSDATE)
              AND NVL(a1.shiftname, 'x') = 'x'
        ) sub
        WHERE sub.attendancedate = a.attendancedate
    )
    WHERE EXISTS (
        SELECT 1
        FROM (
            SELECT attendancedate
            FROM t_attendance_attendance a1
            LEFT JOIN t_attendance_user_master u ON u.loginid = a1.loginid
            LEFT JOIN t_attendance_employee_master e ON e.empid = u.empid
            WHERE a1.loginid = v_loginid
              AND e.company IS NULL
              AND EXTRACT(YEAR FROM a1.attendancedate) = EXTRACT(YEAR FROM SYSDATE)
              AND EXTRACT(MONTH FROM a1.attendancedate) = EXTRACT(MONTH FROM SYSDATE)
              AND NVL(a1.shiftname, 'x') = 'x'
        ) sub
        WHERE sub.attendancedate = a.attendancedate
    );

END getCalendar;

关键修正说明 #

1. CTE作用域处理：每个INSERT语句前重新定义CTE，确保SQL能正确引用；也可将CTE结果存入临时表减少重复定义。
2. 日期初始化修正：用||拼接字符串，确保格式与TO_DATE参数匹配。
3. 裸SELECT处理：将原无意义的SELECT改为输出到指定游标，符合存储过程语法要求。
4. UPDATE语句优化：
   • 指定目标表别名关联条件，避免子查询返回多行。
   • 用TRIM(TO_CHAR(..., 'DAY'))处理Oracle返回的带空格星期字符串。
   • 修正格式符WW为DAY，修复逻辑错误。
   • 增加WHERE EXISTS过滤，仅更新需要修改的行，提升性能。
5. 日期运算优化：用ADD_MONTHS(V_Date, 1)替代INTERVAL '1' MONTH + V_Date，更符合Oracle日期操作习惯。

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