-------------------------------------------------------------------------------
-- Creating a procedure with a nested cursor
-- Main cursor iterates through the instructors
-- Secondary cursor iterates through the students
-------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE inst_students1 (p_last varchar2 := null) AS
    CURSOR cur1 IS 
    select fname, lname, ssn  
      from instructor;
    stu_list varchar2(500);
BEGIN
    EXECUTE IMMEDIATE 'truncate table output';
    FOR inst_row IN cur1 LOOP                       --Loop thru the instructors

        stu_list := ('Instructor: '||inst_row.fname||' '||inst_row.lname||' -');

        IF (p_last is null or p_last = inst_row.lname) then    --If inst = param
           DECLARE
               CURSOR cur2 IS  
               select distinct fname, lname
                 from student join class  
                   on ssn=stu_ssn
                where inst_ssn=inst_row.ssn    --inst_row.ssn is a var from cur1
                order by 2,1; 
           BEGIN
              FOR stu_row IN cur2 LOOP                  --Loop thru the students
                 stu_list := stu_list||stu_row.fname||' '||stu_row.lname||', '; 
              END LOOP; 
           END; 
        END IF; 
        insert into output values(stu_list); 
    END LOOP; 
END;
/ 

CALL inst_students1()        --call with or without last name
/

select * from output
/

-------------------------------------------------------------------------------
-- (Doing the same, but a better way. Cursor with a parameter) 
-- Creating a procedure with a nested cursor 
-- Main cursor iterates through the instructors
-- Secondary cursor iterates through students (secondary cursor takes a param)
-------------------------------------------------------------------------------
CREATE or replace PROCEDURE inst_students2(p_last varchar2 := null) AS
    CURSOR cur1 IS 
    select fname, lname, ssn  
      from instructor
     order by 2,1;

    CURSOR cur2 (c_inst_ssn varchar2) IS        -- cursor with a parameter
    select distinct fname, lname
      from student join class
        on ssn = stu_ssn
     where inst_ssn = c_inst_ssn                -- comparing using the parameter
     order by 2,1; 

    stu_list varchar2(500);

BEGIN
    EXECUTE IMMEDIATE 'truncate table output';

    FOR inst_row IN cur1 LOOP                       -- Loop thru the instructors

        stu_list := ('Instructor: '||inst_row.fname||' '||inst_row.lname||' -');

        IF (p_last = inst_row.lname or p_last is null) then    --If inst = param

            FOR stu_row IN cur2(inst_row.ssn) LOOP    -- Loop thru students, and
                                                      -- pass inst ssn to cursor
                stu_list := stu_list||stu_row.fname||' '||stu_row.lname||', ';
            END LOOP; 

        END IF; 
        insert into output values(stu_list);
    END LOOP; 
END;
/

call inst_students2('Sultan')    --call with or without last name
/

select * from output