Oracle存储过程UpdateAttendance编译失败，请求技术协助

以下是出现编译错误的UpdateAttendance存储过程代码，错误集中在标注的SELECT INTO部分：

PROCEDURE UpdateAttendance
(
V_AttendanceDate DATE,
V_LoginId varchar2,
V_SignType varchar2,
V_IpAddress varchar2,
V_LogonUserName VARCHAR2,
V_ShiftName VARCHAR2,
V_WorkLocationType VARCHAR2,
V_WorkLocation VARCHAR2,
V_Self_Covid_Status VARCHAR2,
V_Self_Covid_Remark VARCHAR2,
V_Family_Covid_Status VARCHAR2,
V_Family_Covid_Remark VARCHAR2,
curAttendanceDate OUT T_CURSOR
)
AS
BEGIN

-- 此处为错误段
SELECT CASE WHEN SignOut IS NULL THEN INTERVAL '-1' DAY (5) + V_AttendanceDate ELSE V_AttendanceDate END
INTO V_AttendanceDate FROM T_ATTENDANCE_ATTENDANCE
         WHERE     UPPER (ShiftName) = 'NIGHT'
               AND LoginId = V_LoginId
               AND AttendanceDate = INTERVAL '-1' DAY (5) + V_AttendanceDate
               AND V_SignType = 'SIGNOUT'
               AND Active = 1;

        UPDATE T_ATTENDANCE_ATTENDANCE
           SET SignIn =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND SignIn IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignIn
                  END,
               SignOut =
                  CASE
                     WHEN     V_SignType = 'SIGNOUT'
                          AND signout IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignOut
                  END,
               IpAddress =
                  CASE
                     WHEN NVL (IpAddress, '') = '' THEN V_IPAddress
                     ELSE IpAddress || ',' || V_IPAddress
                  END,
               LogonUserName =
                  CASE
                     WHEN NVL (LogonUserName, '') = '' THEN V_LogonUserName
                     ELSE LogonUserName || ',' || V_LogonUserName
                  END,
               WorkLocation =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocation
                     ELSE WorkLocation
                  END,
               WorkLocationType =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocationType
                     ELSE WorkLocationType
                  END,
               ShiftName =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND NVL (ShiftName, '') = ''
                     THEN
                        V_ShiftName
                     ELSE
                        ShiftName
                  END
         WHERE     AttendanceDate = V_AttendanceDate
               AND LoginId = V_LoginId
               AND Active = 1;

        UPDATE T_ATTENDANCE_ATTENDANCE
           SET ShiftName = 'LEAVE'
         WHERE     AttendanceDate < SYSTIMESTAMP - 2
               AND SignIn IS NULL
               AND SignOut IS NULL
               AND ShiftName IN ('MORNING',
                                 'AFTERNOON1',
                                 'AFTERNOON2',
                                 'GENERAL1',
                                 'GENERAL2',
                                 'NIGHT');

        INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
                                                    AttendanceDate,
                                                    Self_Covid_Status,
                                                    Self_Covid_Remark,
                                                    Family_Covid_Status,
                                                    Family_Covid_Remark,
                                                    UpdatedOn,
                                                    UpdatedBy)
           SELECT V_LoginId,
                  V_AttendanceDate,
                  V_Self_Covid_Status,
                  V_Self_Covid_Remark,
                  V_Family_Covid_Status,
                  V_Family_Covid_Remark,
                  SYSTIMESTAMP,
                  UPPER (V_LoginId)
             FROM DUAL
            WHERE V_SignType = 'SIGNIN';

END UpdateAttendance;

错误原因分析 #

1. 输入参数不可直接赋值：V_AttendanceDate是IN模式参数，默认只读，不能通过SELECT INTO直接修改其值。
2. 未处理单行查询异常：SELECT INTO要求查询必须返回且仅返回1行，若无匹配行（触发NO_DATA_FOUND）或多行匹配（触发TOO_MANY_ROWS），会导致运行时错误，编译阶段也可能因潜在风险提示问题。
3. 逻辑冗余风险：原查询中INTERVAL '-1' DAY (5)的写法虽语法合法，但DATE类型加减天数可简化为V_AttendanceDate - 1，更易读。

修正后的代码 #

PROCEDURE UpdateAttendance
(
V_AttendanceDate IN OUT DATE, -- 修改为IN OUT模式，允许修改参数值
V_LoginId varchar2,
V_SignType varchar2,
V_IpAddress varchar2,
V_LogonUserName VARCHAR2,
V_ShiftName VARCHAR2,
V_WorkLocationType VARCHAR2,
V_WorkLocation VARCHAR2,
V_Self_Covid_Status VARCHAR2,
V_Self_Covid_Remark VARCHAR2,
V_Family_Covid_Status VARCHAR2,
V_Family_Covid_Remark VARCHAR2,
curAttendanceDate OUT T_CURSOR
)
AS
l_AttendanceDate DATE; -- 声明局部变量存储查询结果
BEGIN

-- 修正后的SELECT INTO段
SELECT CASE WHEN SignOut IS NULL THEN V_AttendanceDate - 1 ELSE V_AttendanceDate END
INTO l_AttendanceDate FROM T_ATTENDANCE_ATTENDANCE
         WHERE     UPPER (ShiftName) = 'NIGHT'
               AND LoginId = V_LoginId
               AND AttendanceDate = V_AttendanceDate - 1
               AND V_SignType = 'SIGNOUT'
               AND Active = 1
               AND ROWNUM = 1; -- 确保仅返回1行

V_AttendanceDate := l_AttendanceDate; -- 将局部变量值赋值给IN OUT参数

        UPDATE T_ATTENDANCE_ATTENDANCE
           SET SignIn =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND SignIn IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignIn
                  END,
               SignOut =
                  CASE
                     WHEN     V_SignType = 'SIGNOUT'
                          AND signout IS NULL
                     THEN
                        SYSDATE
                     ELSE
                        SignOut
                  END,
               IpAddress =
                  CASE
                     WHEN NVL (IpAddress, '') = '' THEN V_IPAddress
                     ELSE IpAddress || ',' || V_IPAddress
                  END,
               LogonUserName =
                  CASE
                     WHEN NVL (LogonUserName, '') = '' THEN V_LogonUserName
                     ELSE LogonUserName || ',' || V_LogonUserName
                  END,
               WorkLocation =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocation
                     ELSE WorkLocation
                  END,
               WorkLocationType =
                  CASE
                     WHEN V_SignType = 'SIGNIN' THEN V_WorkLocationType
                     ELSE WorkLocationType
                  END,
               ShiftName =
                  CASE
                     WHEN     V_SignType = 'SIGNIN'
                          AND NVL (ShiftName, '') = ''
                     THEN
                        V_ShiftName
                     ELSE
                        ShiftName
                  END
         WHERE     AttendanceDate = V_AttendanceDate
               AND LoginId = V_LoginId
               AND Active = 1;

        UPDATE T_ATTENDANCE_ATTENDANCE
           SET ShiftName = 'LEAVE'
         WHERE     AttendanceDate < SYSTIMESTAMP - 2
               AND SignIn IS NULL
               AND SignOut IS NULL
               AND ShiftName IN ('MORNING',
                                 'AFTERNOON1',
                                 'AFTERNOON2',
                                 'GENERAL1',
                                 'GENERAL2',
                                 'NIGHT');

        INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
                                                    AttendanceDate,
                                                    Self_Covid_Status,
                                                    Self_Covid_Remark,
                                                    Family_Covid_Status,
                                                    Family_Covid_Remark,
                                                    UpdatedOn,
                                                    UpdatedBy)
           SELECT V_LoginId,
                  V_AttendanceDate,
                  V_Self_Covid_Status,
                  V_Self_Covid_Remark,
                  V_Family_Covid_Status,
                  V_Family_Covid_Remark,
                  SYSTIMESTAMP,
                  UPPER (V_LoginId)
             FROM DUAL
            WHERE V_SignType = 'SIGNIN';

-- 添加异常处理块
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 无匹配行时的处理逻辑，例如保持原参数值或抛出自定义异常
        NULL;
    WHEN TOO_MANY_ROWS THEN
        -- 多行匹配时的处理逻辑，例如取第一行或抛出异常
        NULL;
END UpdateAttendance;

关键修正点 #

• 将V_AttendanceDate参数改为IN OUT模式，允许修改其值（若无需对外输出，也可仅用局部变量，不修改参数）。
• 用局部变量l_AttendanceDate接收查询结果，避免直接修改输入参数。
• 添加ROWNUM = 1确保查询最多返回1行，避免TOO_MANY_ROWS异常。
• 简化日期计算为V_AttendanceDate - 1，提升代码可读性。
• 增加异常处理块，捕获NO_DATA_FOUND和TOO_MANY_ROWS异常，增强程序健壮性。

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