Oracle中如何通过子查询修正考勤存储过程的更新语句

原代码存在的问题 #

• 语法不兼容：Oracle不支持UPDATE ... FROM这种SQL Server风格的语法，必须改用Oracle原生的关联更新方式
• 冗余类型转换：AttendanceDate本身是DATE类型，无需重复调用TO_DATE()转换
• 日期格式符错误：判断星期日时误用了'WW'（周数格式），应使用'DAY'来获取星期名称
• 窗口函数语法错误：ROW_NUMBER()的PARTITION BY子句缺少右括号，且无需对DATE字段做TO_DATE转换
• 系统函数错误：Oracle系统日期函数为SYSDATE，不能写成Sysdate()（多余括号）
• 星期匹配潜在问题：TO_CHAR(..., 'DAY')返回的字符串默认带补位空格，可能导致匹配失败，需用TRIM()处理

修复后的方案一：关联子查询更新 #

-- 替换原报错的UPDATE语句
UPDATE T_ATTENDANCE_ATTENDANCE a_main
SET a_main.ShiftName = (
    SELECT CASE 
               WHEN TRIM(TO_CHAR(a_sub.AttendanceDate, 'DAY')) = 'SATURDAY' AND a_sub.week IN (2,4) THEN 'WEEKLYOFF'
               WHEN TRIM(TO_CHAR(a_sub.AttendanceDate, 'DAY')) = 'SUNDAY' THEN 'WEEKLYOFF'
               ELSE 'GENERAL1'
           END
    FROM (
        SELECT 
            A1.AttendanceDate,
            A1.ShiftName,
            -- 按星期分组，给同星期的日期编序号（用于判断第2、4个周六）
            ROW_NUMBER() OVER (
                PARTITION BY TRIM(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 = ''
          AND EXTRACT(YEAR FROM A1.AttendanceDate) = EXTRACT(YEAR FROM SYSDATE)
          AND EXTRACT(MONTH FROM A1.AttendanceDate) = EXTRACT(MONTH FROM SYSDATE)
          AND NVL(A1.ShiftName, '') = ''
    ) a_sub
    WHERE a_sub.AttendanceDate = a_main.AttendanceDate
      AND a_sub.ShiftName = a_main.ShiftName
)
-- 仅更新符合条件的记录，避免全表扫描
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            A1.AttendanceDate,
            A1.ShiftName
        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 = ''
          AND EXTRACT(YEAR FROM A1.AttendanceDate) = EXTRACT(YEAR FROM SYSDATE)
          AND EXTRACT(MONTH FROM A1.AttendanceDate) = EXTRACT(MONTH FROM SYSDATE)
          AND NVL(A1.ShiftName, '') = ''
    ) a_sub
    WHERE a_sub.AttendanceDate = a_main.AttendanceDate
      AND a_sub.ShiftName = a_main.ShiftName
);

修复后的方案二：MERGE语句更新（更简洁高效） #

如果是批量更新，Oracle的MERGE语法更直观，性能更优：

MERGE INTO T_ATTENDANCE_ATTENDANCE a_main
USING (
    SELECT 
        A1.AttendanceDate,
        A1.ShiftName,
        ROW_NUMBER() OVER (
            PARTITION BY TRIM(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 = ''
      AND EXTRACT(YEAR FROM A1.AttendanceDate) = EXTRACT(YEAR FROM SYSDATE)
      AND EXTRACT(MONTH FROM A1.AttendanceDate) = EXTRACT(MONTH FROM SYSDATE)
      AND NVL(A1.ShiftName, '') = ''
) a_sub
ON (a_main.AttendanceDate = a_sub.AttendanceDate AND a_main.ShiftName = a_sub.ShiftName)
WHEN MATCHED THEN
UPDATE SET a_main.ShiftName = CASE 
                                 WHEN TRIM(TO_CHAR(a_sub.AttendanceDate, 'DAY')) = 'SATURDAY' AND a_sub.week IN (2,4) THEN 'WEEKLYOFF'
                                 WHEN TRIM(TO_CHAR(a_sub.AttendanceDate, 'DAY')) = 'SUNDAY' THEN 'WEEKLYOFF'
                                 ELSE 'GENERAL1'
                             END;

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