Write a PL/SQL procedure to print the names of instructors and the students took

Question

Write a PL/SQL procedure to print the names of instructors and the students took some class taught by the instructor in the following format:

Srinivasan //instructor

Bourikas //student of Srinivasan

Brown //student of Srinivasan

Levy //student of Srinivasan

Shankar //student of Srinivasan

Williams //student of Srinivasan

Zhan //student of Srinivasan

Wu //instructor

Chavez //student of Wu

Mozart //instructor

Sanchez //student of Mozart

Einstein //student of Mozart

Peltier //student of Mozart

......

Program structure:

Declare a cursor to select the instructor id and the name for all instructors.

Declare another cursor, with parameter, to select the names of students who took some class from the instructor identified by the parameter instructor id. The syntax to define a cursor with parameter is as follows: CURSOR student_name_cursor(instructor_id IN VARCHAR2) IS …

Loop through the first cursor

Print instructor name

open the second cursor using instructor id

Loop through the second cursor

Print student name

End loop

Close the second cursor

End loop

Close the first cursor

Call dbms_output.put_line() function to print the names:

tab is chr(9)

a newline is chr(13)||chr(10)

|| is character concatenation operator

Assume that the procedure name is proc1, to test the procedure we need to write a PL/SQL program to call the procedure:

/* turn on the server output */

set serveroutput on

begin

proc1();

end;

Explanation / Answer

DECLARE
   CURSOR student_name_cursor(instructor_id IN VARCHAR2) IS
       instructor_name VARCHAR2;
       select instructor_name FROM instructor WHERE instructor_id = instructor_id;
       CURSOR student_data IS
           FOR obj IN (select * FROM student WHERE instructor_name = instructor_name INTO data)
           dbms_output.put_line('id=' ||obj.id || ',name=' || obj.name || ',instructor name=' || obj.instructor_name);
           END LOOP;
       CLOSE student_data
   CLOSE student_name_cursor
  
   BEGIN
       OPEN student_name_cursor;
       CLOSE student_name_cursor;
   END;
END   

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.