Write a PL/SQL Procedure to Display All Student First Name

Write a PL/SQL procedure to display all the students first name registered for the course based on the course id. The procedure will take course id as an argument and it should display all the students first name registered for that course. Use cursor.

Consider the details given below:

(Note: In the below example course_id 201 is given as input to the FindStudentDetails procedure and the efore output is displayed as per the sample records)

Pl/SQL Procedure name: FindStudentDetails

Input parameter : cid in registration.courseid%type

Sample Records:

STUDENT TABLE:

STUDID   FIRSTNAME   LASTNAME  STREET        CITY       DOB
101      James       Peter     10th street   Mumbai     12-AUG-92
102      Prem        Chand     12th street   Bangalore  05-AUG-93
103      James       Peter     10th street   Chennai    21-SEP-91

REGISTRATION TABLE:

COURSEID	STUDID	DOJ
201	101	4-JAN-19
201	102	7-FEB-19
202	103	11-MAR-19

Functional Requirement

Procedure FindStudentDetails(cid)

Sample Input:

FindStudentDetails(201)

Sample Output

Student Names are:
James 
Prem

(Note: In the above example courseid 101 is given as input to the FindStudentDetails procedure and the output is displayed as per the sample records )

(Hint: Use '/' to terminate the PLSQL block. Data is case sensitive.)

Step-by-Step Guide to Creating a PL/SQL Procedure to Display Student First Names

Introduction to PL/SQL Procedures:
A PL/SQL procedure is a named block of PL/SQL code that performs a specific task. In this guide, we will walk through creating a procedure that displays the first names of students based on a given course ID.

Understanding the Task:
The procedure will accept a course ID as input and then display all student first names that are enrolled in that course. This is useful for retrieving specific data based on user input, such as generating a list of students for a particular course.

Writing the Procedure Code:
Let's break down the procedure code step by step.

Procedure Declaration:
We start by declaring the procedure with a name. Here, it is called FindStudentDetails:

CREATE OR REPLACE PROCEDURE FindStudentDetails (cid IN registration.courseid%type)

This line tells the database we are creating or replacing a procedure named FindStudentDetails that takes an input parameter cid.

Setting Up the Cursor:
We define a cursor to retrieve the student first names that match the given course ID:

CURSOR c_stud IS SELECT FirstName FROM STUDENT 
                 JOIN registration USING (StudId) 
                 WHERE courseid = cid;

This allows us to fetch student first names from the database where the course ID matches the input parameter.

Opening the Cursor:
The next step is to open the cursor and prepare to fetch data:

OPEN c_stud;

This line opens the cursor and prepares it to start fetching student first names.

Processing the Results:
Now, we loop through each row returned by the cursor and display the student first names:

 LOOP 
    FETCH c_stud INTO v_stud; 
    EXIT WHEN c_stud%NOTFOUND; 
    dbms_output.put_line(v_stud); 
END LOOP; 

Each student first name is fetched and displayed one by one. The loop exits when there are no more names left to process.

Closing the Cursor:
After processing all student first names, we close the cursor to free up resources:

CLOSE c_stud;

This ensures that the cursor is properly closed after use.

Conclusion:
With this procedure in place, you can retrieve and display all student first names for a given course, providing a dynamic and efficient way to access specific data from the student database.