I need to check if a student is already on a waitlist for a callnum (class number).
If a student is already on the waitlist, he or she should not be added to the waitlist.
It should print a message stating that but I'm not too sure where I would put that in.
if p_ErrorMsg is null then -- Stores the error messages in p_ErrorMsg
select capacity into v_capacity -- Checks the capacity limit.
from schclasses
where callnum = p_callnum;
select count(callnum) into v_enrolled
from enrollments
where callnum = p_callnum
and grade is null;
if v_capacity > v_enrolled then
insert into enrollments values (p_snum, p_callnum, null);
commit;
p_ErrorMsg := null;
dbms_output.put_line('Student ' ||p_snum|| ' has been enrolled in class ' ||p_callnum|| '.'); -- Confirmation message that student is enrolled in course.
else
insert into waitlist values (p_snum, p_callnum, to_char(sysdate)); -- Enrolls student to waitlist
commit;
p_ErrorMsg := 'Sorry, this class is full.';
end if;
end if;
else
p_ErrorMsg := 'Invalid student number.';
end if;
end;
Would this work?
if v_capacity > v_enrolled then
-- 11. If p_ErrorMsg is null (no error), then the student is enrolled.
insert into enrollments values (p_snum, p_callnum, null);
commit;
p_ErrorMsg := null;
dbms_output.put_line('Student ' ||p_snum|| ' has been enrolled in class ' ||p_callnum|| '.');
elsif select wl_snum, wl_callnum FROM waitlist wl
if p_snum = wl_snum AND p_callnum = wl_callnum then
p_ErrorMsg := 'Student is already on the waiting list for this CallNum';
else
insert into waitlist values (p_snum, p_callnum, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
commit;
p_ErrorMsg := 'Class chosen is full. Student will be placed on the waiting list.';
IF SELECT...orELSIF SELECT...because SELECT statements don't return a BOOLEAN value.