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

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

Im making a lot of assumptions here – Im 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 dont 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 Ive put query alterations in CAPS.

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

Simiarly, I retained the DISTINCT in the second query, but Id 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?)

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