Create a Package pkcourse with Specification and Body

Create a package pkcourse with specification and body. The package has a procedure named findCourse which accepts the student Id as input and prints the student's first name along with the end date of course duration. If no such student id is present, print "No Student Found".

Use the skeleton file to develop the package

PACKAGE pkcourse PROCEDURE findCourse

Package name: pkcourse

Procedure name: findCourse

Input Parameter: nstudId Registration.studid%type

The output should be in the following format:

Sample Data:

firstname will complete the course on 14-MAY-22

Sample Output:

Peter will complete the course on 20-APR-18

(Hint: Course end date is calculated using date of joining and course duration)

Step-by-Step Guide to Creating a PL/SQL Package to Find Course Completion Date

Introduction to PL/SQL Packages:
A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. In this guide, we will create a package that retrieves a student's course completion date based on a given student ID.

Understanding the Task:
The package will include a procedure that accepts a student ID as input and displays details such as the student's first name and the expected course completion date. This is useful for quickly fetching student course information.

Writing the Package Code:
Let's break down the package code step by step.

Package Specification:
We start by declaring the package, naming it pkcourse, and including a procedure findCourse:

CREATE OR REPLACE PACKAGE pkcourse AS
PROCEDURE findCourse(nstudId Registration.studid%type);
END pkcourse;

This specifies the package with a procedure that takes the nstudId parameter, representing a student ID.

Package Body:
The package body contains the implementation of the procedure:

CREATE OR REPLACE PACKAGE BODY pkcourse AS
PROCEDURE findCourse(nstudId Registration.studid%type) AS
v_fname VARCHAR2(20); -- Variable to store student's first name
v_date DATE; -- Variable to store course completion date
BEGIN
    SELECT FirstName, DOJ + Duration INTO v_fname, v_date 
    FROM STUDENT 
    JOIN Registration USING (StudId)
    JOIN COURSE USING (courseid)
    WHERE StudId = nstudId;
    dbms_output.put_line(v_fname ||' will complete the course on '||v_date);
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line('No Student Found');
END findCourse;
END pkcourse;

The body includes the actual logic for fetching and displaying the student's course completion date.

Fetching Data:
We use a `SELECT` query to fetch the student's first name and calculate the course completion date by adding the date of joining (DOJ) and the course duration:

SELECT FirstName, DOJ + Duration INTO v_fname, v_date 
FROM STUDENT 
JOIN Registration USING (StudId)
JOIN COURSE USING (courseid)
WHERE StudId = nstudId;

This query retrieves the student's first name and course completion date based on the provided student ID.

Displaying the Data:
Once the data is fetched, we output the results using dbms_output.put_line:

dbms_output.put_line(v_fname ||' will complete the course on '||v_date);

This displays a message like: `John will complete the course on 15-MAR-2025`.

Handling Exceptions:
In case no student is found, we handle the exception with:

EXCEPTION
WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('No Student Found');

This ensures that a clear message is shown if the student ID doesn't exist in the database.

Conclusion:
By following this guide, you’ve successfully created a PL/SQL package that retrieves and displays a student's course completion date based on their student ID. This package is highly useful for dynamically accessing course information from the database.