truncate table output
/
--------------------------------------------------------------------------------
-- Creating and using a cursor with parameter
-- the same cursor is used 2 times  ('M',2) and ('F',3)
-- if using simple loop, pass the parameters when opening the cursor
-- if using for loop,    pass the parameters when using the FOR loop
--------------------------------------------------------------------------------
DECLARE
    CURSOR  student_cur  (p_sex  varchar2 := 'M',  p_num  int )  IS  
    select fname, lname, sex as gender, count(course_id) as cnt
      from student, class
     where ssn = stu_ssn
       and sex = p_sex
     group by (fname, lname, sex)
    having count(course_id) >= p_num;

    v_row student_cur%ROWTYPE;

BEGIN
   OPEN student_cur('M',2);                 -- open cursor with parameters
   LOOP
      FETCH student_cur  INTO  v_row;
      EXIT WHEN  student_cur%NOTFOUND;
      insert into output values(v_row.fname||' '||v_row.gender||' '||v_row.cnt); 
   END LOOP;
   CLOSE student_cur;
                                            -- implicit open with the FOR loop
   insert into output values('');

   FOR row IN  student_cur('F',3) LOOP 
      insert into output values (row.fname ||' '||row.gender||' '||row.cnt); 
  END LOOP;
END;
/

select * from output