Oracle存储过程求助：如何仅展示指定员工处理的预约记录？

核心逻辑错误（需求不匹配） #

你当前的存储过程逻辑是查询该员工参与的所有预约，和需求中的「仅由指定员工处理的预约（即该预约无其他员工参与）」完全不符。要实现目标需求，必须调整查询条件，筛选出当前员工处理且无其他员工介入的预约：

-- 假设Appointments表中同一条预约若对应多条员工记录，则表示多员工处理
SELECT a.* 
FROM Appointments a
WHERE a.staffId = staffID
AND NOT EXISTS (
    SELECT 1 
    FROM Appointments a2 
    WHERE a2.id = a.id 
    AND a2.staffId != staffID
);

代码中的语法/变量错误 #

• 未声明v_staffID变量：FETCH语句中用到了v_staffID但未提前声明，会触发编译错误。如果不需要获取该字段，直接从FETCH列表中移除；若需要，则添加声明：

  v_staffID Appointments.staffId%TYPE;
  

• 未声明appointmentCount变量：直接对该变量执行自增操作，但未声明和初始化，需补充：

  appointmentCount NUMBER := 0;
  

• SELECT *存在维护隐患：若Appointments表的列顺序或数量变动，FETCH语句会因变量匹配错误报错，建议明确指定所需列：

  CURSOR appointmentDetail IS
      SELECT id, createdAt, bookingDateTime, petId, roomId
      FROM Appointments a
      WHERE a.staffId = staffID
      AND NOT EXISTS (
          SELECT 1 
          FROM Appointments a2 
          WHERE a2.id = a.id 
          AND a2.staffId != staffID
      );
  

修正后的完整存储过程 #

CREATE OR REPLACE PROCEDURE proc_gen_staff_detail_report(staffID IN NUMBER) IS
    CURSOR appointmentDetail IS
        SELECT id, createdAt, bookingDateTime, petId, roomId
        FROM Appointments a
        WHERE a.staffId = staffID
        AND NOT EXISTS (
            SELECT 1 
            FROM Appointments a2 
            WHERE a2.id = a.id 
            AND a2.staffId != staffID
        );

    v_appointmentID Appointments.id%TYPE;
    v_createdAt Appointments.createdAt%TYPE;
    v_bookingDateTime Appointments.bookingDateTime%TYPE;
    v_petID Appointments.petId%TYPE;
    v_roomID Appointments.roomId%TYPE;
    appointmentCount NUMBER := 0;
BEGIN
    OPEN appointmentDetail;
    LOOP
        FETCH appointmentDetail INTO v_appointmentID, v_createdAt, v_bookingDateTime, v_petID, v_roomID;
        EXIT WHEN appointmentDetail%NOTFOUND;
        appointmentCount := appointmentCount + 1;

        DBMS_OUTPUT.PUT_LINE(RPAD(v_appointmentID, 6, ' ') || ' ' || 
                             RPAD(v_createdAt, 30, ' ') || ' ' || 
                             RPAD(v_bookingDateTime, 30, ' ') || ' ' || 
                             RPAD(v_petID, 10, ' ') || ' ' || 
                             RPAD(v_roomID, 10, ' '));
    END LOOP;
    CLOSE appointmentDetail;
END;
/

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