Cursor For Loop how to print message when not found

Question

OK so I have a cursor for loop that I want to print an error message when the select statement doesn't find a course that the user has inputted. But the problem is that cursor for loops automatically exits when the select statement fails, so my else statement never executes. How do I print a message saying that they course they are looking for doesn't exist. Note switching to a cursor fetch is not an option. For example id the user enters a course that exists it prints all relevant information. When the user inputs a course with no prerequisite it prints a proper message, but if the user inputs a course that doesn't exists, nothing gets printed.

DECLARE
course_name VARCHAR2(40) := '&course_input';
TYPE course_r IS RECORD(
  course_description course.description%TYPE,
  cost course.cost%TYPE,
  prerequisite course.prerequisite%TYPE,
  prerequisite_cost course.cost%TYPE
);
course_rec course_r;
CURSOR course_cursor IS
  SELECT a.description, a.cost, a.prerequisite,  b.cost AS preq_cost
  FROM COURSE a
  LEFT JOIN COURSE b ON a.prerequisite = b.course_no
  WHERE UPPER(a.description) LIKE '%'||'&course_input'||'%';

BEGIN
FOR record IN course_cursor
LOOP    
    course_rec.course_description := record.description;
    course_rec.cost := record.cost;
    course_rec.prerequisite := record.prerequisite;
    course_rec.prerequisite_cost := record.preq_cost;
IF course_rec.prerequisite IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('There is NO prerequisite course for any that starts on ' || course_name || '. Try again');
ELSIF course_rec.prerequisite IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('Course: ' || course_rec.course_description);
    DBMS_OUTPUT.PUT_LINE('Cost: ' || course_rec.cost);
    DBMS_OUTPUT.PUT_LINE('Prerequisite: ' || course_rec.prerequisite);
    DBMS_OUTPUT.PUT_LINE('Prerequisite Cost: ' || course_rec.prerequisite_cost);
    DBMS_OUTPUT.PUT_LINE('=================================================');
ELSE
    DBMS_OUTPUT.PUT_LINE('There is NO VALID course that starts on '||course_name||'. Try again.');
END IF;
END LOOP;
END;
/

Answer 1

You can do this by having a variable which only gets set inside the loop. Then you can check that variable after the loop has completed to see if it was set, and decide if you need to do additional work.

Something like:

DECLARE
  course_name    VARCHAR2(40) := '&course_input';
  v_rows_present BOOLEAN := FALSE;
BEGIN
  FOR course_rec IN (SELECT a.description,
                            a.cost,
                            a.prerequisite,
                            b.cost AS preq_cost
                     FROM   course a
                     LEFT   JOIN course b
                     ON     a.prerequisite = b.course_no
                     WHERE  upper(a.description) LIKE '%' || course_name || '%')
  LOOP
    v_rows_present := TRUE;

    IF course_rec.prerequisite IS NULL
    THEN
      dbms_output.put_line('There is NO prerequisite course for any that starts on ' || course_name || '. Try again');
    ELSE
      dbms_output.put_line('Course: ' || course_rec.course_description);
      dbms_output.put_line('Cost: ' || course_rec.cost);
      dbms_output.put_line('Prerequisite: ' || course_rec.prerequisite);
      dbms_output.put_line('Prerequisite Cost: ' || course_rec.prerequisite_cost);
      dbms_output.put_line('=================================================');

    END IF;
  END LOOP;

  IF NOT v_rows_present
  THEN
    dbms_output.put_line('There is NO VALID course that starts on ' || course_name || '. Try again.');
  END IF;

END;
/

N.B. I've updated your code as you appear to have misapprehended how to use a cursor for loop.

• Cursor-for-loops create their own record variable implicitly, so you don't need to declare one yourself.
• You also don't need to declare a cursor explicitly either - that can be done as part of the cursor-for-loop statement.
• You don't need to populate a new record with the same values from the cursor-for-loop record in order to use the values (as long as you're using them within the cursor-for-loop, of course!)

Answer 2

THE CURSOR FOR LOOP doesn't execute if there are no such courses in the courses table.So, check if a row exists before entering the loop.

Two other things to note:

• LIKE '%'||'&course_input'||'%' is not required in the where clause as the same variable is already passed from user input and assigned
  in the declare section.Simply use LIKE '%' || course_name || '%'

• RECORD is a PL/SQL reserved word and shouldn't be used as a loop
  index variable, i've changed it to rec.

---

DECLARE
     course_name          VARCHAR2(40) := '&course_input';
     TYPE course_r IS RECORD ( course_description   course.description%TYPE,
     cost                 course.cost%TYPE,
     prerequisite         course.prerequisite%TYPE,
     prerequisite_cost    course.cost%TYPE );
     course_rec           course_r;
     cur_count            NUMBER;

     CURSOR course_cursor IS SELECT a.description,
                                    a.cost,
                                    a.prerequisite,
                                    b.cost AS preq_cost
                             FROM course a
                             LEFT JOIN course b ON a.prerequisite = b.course_no
                             WHERE upper(a.description) LIKE '%' || course_name || '%';
BEGIN
     SELECT COUNT(*)
      INTO cur_count
       FROM course a
     WHERE upper(a.description) LIKE '%' || course_name || '%';
     IF
          cur_count > 0
     THEN
          FOR rec IN course_cursor LOOP
               course_rec.course_description := rec.description;
               course_rec.cost := rec.cost;
               course_rec.prerequisite := rec.prerequisite;
               course_rec.prerequisite_cost := rec.preq_cost;
               IF
                    course_rec.prerequisite IS NULL
               THEN
                    dbms_output.put_line('There is NO prerequisite course for any that starts on ' ||
                    course_name || '. Try again');
               ELSE
                    dbms_output.put_line('Course: ' || course_rec.course_description);
                    dbms_output.put_line('Cost: ' || course_rec.cost);
                    dbms_output.put_line('Prerequisite: ' || course_rec.prerequisite);
                    dbms_output.put_line('Prerequisite Cost: ' || course_rec.prerequisite_cost);
                    dbms_output.put_line('=================================================');
               END IF;
          END LOOP;
     ELSE
          dbms_output.put_line('There is NO VALID course that starts on ' || course_name || '. Try again.'
          );
     END IF;
END;
/

Answer 3

You could declare a counter, as say, PLS_INTEGER, initialize it to 0, then increment it inside the loop. After the loop, you can check the value and if it's 0, you know no rows were returned.