PL/SQL adding another condition to an if/else statement

Question

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.';

Answer 1

I've got two other solutions in mind:

1. A simple update to your code could be:

    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
           SELECT COUNT(*)
             INTO ln_exists_in_waiting_list 
             FROM waitlist wl
            WHERE p_callnum = wl_callnum;
   
           IF ln_exists_in_waiting_list > 0 THEN
               INSERT INTO waitlist
                    VALUES (p_snum,
                            p_callnum,
                            to_char(SYSDATE)); -- Enrolls student to waitlist
               COMMIT;
               p_errormsg := 'Sorry, this class is full.';
           ELSE
               p_ErrorMsg := 'Student is already on the waiting list for this CallNum';
           END IF;
       ELSE
           p_errormsg := 'Invalid student number.';
       END IF;
   END IF;
   

2. You can also create a unique index on waitlist:

   CREATE UNIQUE INDEX waitlist_u1 ON waitlist (wl_callnum);
   

And then catch the DUP_VAL_ON_INDEX exception (ORA-00001: unique constraint violated):

   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
            BEGIN
                INSERT INTO waitlist
                     VALUES (p_snum,
                             p_callnum,
                             to_char(SYSDATE)); -- Enrolls student to waitlist
                COMMIT;
                p_errormsg := 'Sorry, this class is full.';
            EXCEPTION
                WHEN DUP_VAL_ON_INDEX THEN
                    p_ErrorMsg := 'Student is already on the waiting list for this CallNum';
            END;
        END IF;
    ELSE
        p_errormsg := 'Invalid student number.';
    END IF;

Answer 2

Your revision would not work. First off the select following "elsif" will not compile as you are missing the "into clause. But even with that in place it will fail during execution with "No Data Found" exception. However the select is not even necessary. Please lookup the documentation for Merge statement. In this case delete the "elsif" through the final "end if" then

else 
    *Place Merge here*
    if sql%rowcount = 0 
    then 
        p_ErrorMsg := 'Student is already on the waiting list for this CallNum';
    else 
        p_ErrorMsg := 'Class chosen is full. Student placed on the waiting list.';
end if; 

end-if ;  --( depending on having been deleted "above" )

In this case the Merge statement will do the select and if it finds the row (identified by the ON clause) will not process further, if it does not find then it will do the insert. Again get to understand how the Merge statement works. If can be a very powerful statement but you must clearly understand it for it to work for you.

Answer 3

For wait list you can use a Merge statement:

  merge into waitlist wl
    using (select p_snum student_id
                , p_callnum class_name
                , to_char(sysdate) wait_date
             from dual ) vals
       on (    vals.student_id = wl.student_id 
           and vals.class      = wl.class_name )
     when not Matched then
       insert (student_id, class_name, stupid_way_to_store_date))   -- <<< Actual Column Names >>> 
       values (vals.student_id, vals.class_name, vals.wait_date);

   if sql%rowcount = 0 then
      p_ErrorMsg := 'Sorry, this class is full.';
   end-if;