Working with nested loops in pl/sql but not displaying the proper output

Question

SET SERVEROUTPUT ON SIZE 4000;
DECLARE 
call_id COURSE.CALL_ID%type;
sec_num COURSE_SECTION.SEC_NUM%type;
fname STUDENT.S_FIRST%TYPE ;
lname STUDENT.S_LAST%TYPE;

CURSOR c_info is 
SELECT CALL_ID , SEC_NUM 
FROM COURSE_SECTION ,COURSE,TERM
WHERE COURSE_SECTION.COURSE_ID = COURSE.COURSE_ID
AND TERM.TERM_ID = COURSE_SECTION.TERM_ID
AND TERM.TERM_DESC = 'Summer 2007' ;

CURSOR S_NAME IS 
SELECT DISTINCT S_FIRST, S_LAST    
FROM STUDENT,COURSE_SECTION,TERM,ENROLLMENT 
WHERE TERM.TERM_ID = COURSE_SECTION.TERM_ID
AND COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID
AND COURSE_SECTION.TERM_ID=TERM.TERM_ID                             
AND ENROLLMENT.S_ID = STUDENT.S_ID
AND TERM.TERM_DESC LIKE 'Summer 2007';


   BEGIN
   OPEN c_info; 
  
   LOOP 
   FETCH c_info INTO call_id , sec_num ;
      EXIT WHEN c_info%notfound; 
      DBMS_OUTPUT.PUT_LINE('==================================');
      DBMS_OUTPUT.PUT_LINE(call_id || ' ' || 'Sec. ' || sec_num);
      DBMS_OUTPUT.PUT_LINE('==================================');   
      OPEN S_NAME;
      LOOP
        FETCH S_NAME INTO fname , lname ;
        EXIT WHEN S_NAME%notfound;
        DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname );  
      END LOOP;
       CLOSE S_NAME ;
   END LOOP; 
  
   CLOSE c_info; 
  
END; 

-- The output expected
-- I have having some issues, I am unable to display the proper output. I am trying to use a nested loop but i made some mistake when implementing it. Plus i think an explicit cursor is much better to be used.

Make use of the Northwood university database. https://drive.google.com/file/d/1M_g7FbgOUahoFtE943OK28UxIFbUFgRk/view?usp=sharing The script

Answer 1

I'm making a lot of assumptions here - I'm guessing you are getting all students for all courses in your inner loop, but you really just want get students for the particular course section you are dealing with in your outer loop.

So your second query will need to reference the right course section ID to limit the students to just that section.

You don't need to explicitly define cursors unless you need them for some reason - if you just iterating through them, its better to reference them directly in the FOR loop.

So that brings me to the following

set serveroutput on size 4000;
begin
   for c_info in ( 
      select call_id, 
             sec_num,
             SEC_ID       -- PK to link to enrollment later
      from   course_section,
             course,
             term
      where course_section.course_id = course.course_id
      and   term.term_id = course_section.term_id
      and   term.term_desc = 'Summer 2007' ;
   )
   loop 
      dbms_output.put_line('==================================');
      dbms_output.put_line(c_info.call_id || ' ' || 'Sec. ' || c_info.sec_num);
      dbms_output.put_line('==================================');   
      
      for s_name in (
        select distinct s_first, s_last    
        from student,
             course_section,
             term,
             enrollment 
        where term.term_id = course_section.term_id
        and   course_section.c_sec_id = enrollment.c_sec_id
        and   course_section.term_id=term.term_id                             
        and   enrollment.s_id = student.s_id
        and   term.term_desc like 'Summer 2007'
        AND   ENROLLMENT.C_SEC_ID = C_INFO.SEC_ID  -- get students just for THIS course section
      )
      loop
        dbms_output.put_line(s_name.s_first || ' ' || s_name.s_last );  
      end loop;
   end loop; 
end; 

where I've put query alterations in CAPS.

Since I cut/pasted your SQL there are no aliases in the first query - I'd recommend you correct that, as aliasing columns is always good practice.

Simiarly, I retained the DISTINCT in the second query, but I'd suspect its redundant, because I imagine a student wont enroll more than once for a single course section. (And in reality, if you had two different students named Sue Smith, you would probably want to print them out twice, no?)