Oracle存储过程getCalendar报SQL命令未正确结束错误求助

问题概述 #

编写的Oracle存储过程getCalendar执行时提示错误：SQL命令未正确结束，该存储过程用于处理考勤数据，包含数据插入、查询、更新逻辑，关联表为T_ATTENDANCE_ATTENDANCE。

报错原因分析 #

• 孤立SELECT语句：PL/SQL中不允许直接执行无接收对象的SELECT，原代码中select dates from DaysInMonth;未将结果存入变量或输出游标，违反语法规范。
• UPDATE语句无效ORDER BY：Oracle的UPDATE语句不支持末尾添加ORDER BY，排序对更新逻辑无实际作用，属于语法错误。
• 未使用输出游标：存储过程定义了输出参数curAttendanceDate OUT T_CURSOR，但原代码直接执行SELECT ...未将结果赋值给游标。
• 日期转换冗余：TO_DATE(a.attendancedate)属于冗余操作，ATTENDANCEDATE本身是TIMESTAMP类型，可直接用TO_CHAR(a.attendancedate, 'DAY')格式化。
• 变量赋值语法不规范：V_Date := INTERVAL '1' MONTH + V_Date;虽能运行，但Oracle更推荐使用ADD_MONTHS函数处理月份增减。
• 子查询关联缺失：UPDATE中的子查询未与主表关联，可能导致单行子查询返回多行的错误。

修正后的存储过程代码 #

PROCEDURE getCalendar(
    V_Year                 VARCHAR2,
    V_Month                VARCHAR2,
    V_LoginId              VARCHAR2,
    curAttendanceDate      OUT T_CURSOR
)
AS
    V_Date  DATE := TO_DATE(V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
BEGIN 
    -- 插入当前月考勤数据（仅插入不存在的记录）
    INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate, ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
    WITH DaysInMonth (dates) AS (
        SELECT V_Date FROM DUAL 
        UNION ALL
        SELECT dates + INTERVAL '1' DAY
        FROM DaysInMonth
        WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1)
    )
    SELECT u.LoginID, d.dates, '', null, null, 1, 'Approved' 
    FROM DaysInMonth d
    CROSS JOIN T_ATTENDANCE_USER_MASTER U
    WHERE u.LoginId = V_LoginId
      AND EXTRACT(MONTH FROM d.dates) = EXTRACT(MONTH FROM V_Date)
      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为下月（若业务不需要此逻辑可删除）
    V_Date := ADD_MONTHS(V_Date, 1);

    -- 插入下月考勤数据（仅插入不存在的记录）
    INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate, ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
    WITH DaysInMonth (dates) AS (
        SELECT V_Date FROM DUAL 
        UNION ALL
        SELECT dates + INTERVAL '1' DAY
        FROM DaysInMonth
        WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1)
    )
    SELECT u.LoginID, d.dates, '', null, null, 1, 'Approved' 
    FROM DaysInMonth d
    CROSS JOIN T_ATTENDANCE_USER_MASTER u 
    WHERE u.LoginId = V_LoginId
      AND EXTRACT(MONTH FROM d.dates) = EXTRACT(MONTH FROM V_Date)
      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
      );

    -- 将查询结果赋值给输出游标
    OPEN curAttendanceDate FOR
        SELECT AttendanceDate, 
               ShiftName, 
               TO_CHAR(AttendanceDate, 'ddd') AS Day, 
               V_YEAR, 
               V_MONTH, 
               TO_CHAR(SignIn, 'yyyy-MM-dd hh:mm:ss tt') AS SignIn, 
               TO_CHAR(SignOut, 'yyyy-MM-dd hh:mm:ss tt') AS SignOut 
        FROM T_ATTENDANCE_ATTENDANCE
        WHERE LoginId = V_LoginId 
          AND EXTRACT(YEAR FROM ATTENDANCEDATE) = V_Year 
          AND EXTRACT(MONTH FROM AttendanceDate) = V_Month
          AND ACTIVE = 1
        ORDER BY AttendanceDate;

    -- 更新班次名称，修正子查询关联并移除无效ORDER BY
    UPDATE T_ATTENDANCE_ATTENDANCE a
    SET a.shiftname = (
        SELECT CASE
                 WHEN TO_CHAR(a.attendancedate, 'DAY') IN ('SATURDAY') 
                      AND sub.week IN (2, 4)
                 THEN 'WEEKLYOFF'
                 WHEN TO_CHAR(a.attendancedate, 'DAY') IN ('SUNDAY')
                 THEN 'WEEKLYOFF'
                 ELSE 'GENERAL1' 
               END
        FROM (
            SELECT a1.attendancedate,
                   ROW_NUMBER() OVER (
                       PARTITION BY TO_CHAR(a1.attendancedate, 'DAY')
                       ORDER BY a1.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 a1.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. 移除孤立的SELECT语句，通过OPEN ... FOR将查询结果赋值给输出游标，符合PL/SQL语法规范。
2. 删除UPDATE语句末尾无效的ORDER BY子句。
3. 修正日期转换冗余代码，直接对TIMESTAMP字段进行格式化。
4. 使用ADD_MONTHS函数替代INTERVAL加法，规范变量赋值语法。
5. 为UPDATE子查询添加主表关联条件，避免单行子查询返回多行的错误。
6. 将NOT EXISTS中的SELECT LoginId改为SELECT 1，提升查询效率。

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