Write a PL/SQL procedure to print the names of instructors and the students took
ID: 3777280 • Letter: W
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 instructor_name_cursor IS SELECT inst_id,inst_name FROM instructor;
CURSOR student_name_cursor (instructor_id VARCHAR2) IS
SELECT stud_name,in_id
FROM student
WHERE in_id = instructor_id
ORDER BY stud_name;
i_id instructor.inst_id%type;
i_name instructor.inst_name%type;
s_name student.stud_name%type;
si_id student.in_id%type;
BEGIN
OPEN instructor_name_cursor;
LOOP
FETCH instructor_name_cursor INTO i_id,i_name;
EXIT WHEN instructor_name_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (i_name.inst_name || ' //' || 'instructor');
OPEN student_name_cursor (si_id.in_id);
LOOP
FETCH student_name_cursor INTO s_name,si_id;
EXIT WHEN student_name_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (s_name.stud_name || 'student of instructor' || si_id.in_id);
END LOOP;
CLOSE student_name_cursor;
END LOOP;
CLOSE instructor_name_cursor;
END;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.