CREATE OR REPLACE PROCEDURE GEM_GRADE
(p_sectionID IN section.section_id%TYPE,
p_grade_type IN grade.grade_type_code%TYPE)
AS
TYPE type_stud IS RECORD
(voornaam student.first_name%type,
achternaam student.last_name%TYPE);

TYPE type_curs IS RECORD
(nummer course.course_no%TYPE,
beschrijving course.description%TYPE);

TYPE type_coll_grades
IS TABLE OF grade%ROWTYPE
INDEX BY PLS_INTEGER;

t_grades type_coll_grades;
r_stud type_stud;
r_curs type_curs;
BEGIN

SELECT *
BULK COLLECT INTO t_grades
FROM grade
WHERE (p_sectionid = section_id
AND grade_type_code = p_grade_type);

FOR i IN 1..t_grades.COUNT
LOOP
SELECT first_name, last_name
INTO r_stud.voornaam, r_stud.achternaam
FROM student
WHERE student_id = t_grades(i).student_id;

SELECT description, course_no
INTO r_curs.beschrijving, r_curs.nummer
FROM course
WHERE course_no = (
                SELECT course_no
                FROM section
                WHERE section_id = t_grades(i).section_id);

dbms_output.put_line(
      'Section ' || p_section_id || ' - ' || r_curs.beschrijving
      || ' #' || r_curs.nummer || ' > Student ' ||
      r_stud.achternaam || ' ' || r_stud.voornaam ||
      ' Score on ' || p_grade_type || ': ' || t_grades(i).numeric_grade
    );


END LOOP;
END GEM_GRADE;