Resources: 1. Tutorial \"Using oracle PL/SQL\" http:LLinfolab,stanford.edu/Nullm

Question

Resources: 1. Tutorial "Using oracle PL/SQL" http:LLinfolab,stanford.edu/Nullmanlfcdbloraclelor.plsgl html 2. PL/SQL examples on Blackboard: get gpafunc.txt, getgpaproc.txt 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 Bourikas. Shankar Zhang Mozart Sanchez Einstein Peltier 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

Explanation / Answer

create procedure proc1
IS
   CURSOR instructor_cursor IS SELECT name,id from instructor;
  
   CURSOR student_name_cursor(instructor_id IN VARCHAR2) IS SELECT name,id from student
   WHERE inst_id=instructor_id;
  
   printtext VARCHAR2(30000) :='';
  
begin
   open(instructor_cursor);
   for inst in instructor_cursor
   loop
       printtext:=printtext||inst.name||chr(10)||chr(13);
       open(student_name_cursor(inst.id));
       for student in student_name_cursor(inst.id)
       loop
           printtext:=printtext||chr(9)||student.name||chr(10)||chr(13);
       end loop;
       close student_name_cursor;
   end loop;
   close inst;
   dbms_output.put_line(printtext);
end;
/

set serveroutput on;
begin
proc1();
end;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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