How to display only the appointment handled by the staff?-CodePudding

I'm trying to create a procedure that display the appointment record that are only handled by specific staff. However, it is still displaying all of the appointments for this specific staff. May I know where are my mistake?

 CREATE OR REPLACE PROCEDURE proc_gen_staff_detail_report(staffID IN NUMBER) IS

CURSOR appointmentDetail IS
    SELECT * FROM Appointments WHERE Appointments.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;
    BEGIN
    OPEN appointmentDetail;
    LOOP
        FETCH appointmentDetail INTO v_appointmentID, v_createdAt, v_bookingDateTime, v_petID, v_roomID, v_staffID;
        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;
    /

CodePudding user response：

 CREATE OR REPLACE PROCEDURE proc_gen_staff_detail_report(staffID IN NUMBER) IS

CURSOR appointmentDetail IS
    SELECT * FROM Appointments WHERE Appointments.staffId = staffID;

Your parameter name is the same as the name of column in the WHERE clause change it to something like

PROCEDURE proc_gen_staff_detail_report(p_staffID IN NUMBER)
...
  WHERE Appointments.staffId = p_staffID;