Question

Create a PL/SQL procedure that takes department ID as an input and prints
the details of all the students including the Student ID (represented as StuID), name,
and the description of the major that they are currently enrolled in. You may use the
following procedure template:
CREATE OR REPLACE PROCEDURE
Student_Major (I_deptID IN Student.DeptID%type)
AS
CURSOR c1 is ...
...
BEGIN
...
END Student_Major;

Answer 1

The source code for stored procedure is given below:

create or replace procedure Student_Major(i_deptid  IN Student.DeptID%type)
is
StuID Student.StuID%type;
Name Student.Name%type;
Major Student.Major%type;

  CURSOR c_student is
  SELECT StuID, Name, major FROM student where DeptID=i_deptid;
BEGIN
  dbms_output.put_line('StuID' || ' ' || 'Name' || ' ' || 'Major');
  dbms_output.put_line('----------------------------------------');
OPEN c_student;
LOOP
FETCH c_student into StuID, Name, Major;
  EXIT WHEN c_student%notfound;
  dbms_output.put_line(StuID || ' ' || Name || ' ' || Major);
END LOOP;
CLOSE c_student;
END Student_Major;​

Source code screen shot is given below:

The schema of Student table is given below:

Student table contains the following records:

Output screen shot is given below: