Create a Package pkcustomer with Specification and Body

Create a package pkcustomer with specification and body. The package has a procedure named findCustomerOrder which accepts the order Id as input and prints the customer name, order amount and order date. If no such order id is present, print "No Customer Found".

Use the skeleton file to develop the package

PACKAGE pkcustomer PROCEDURE findCustomerOrder

Package name: pkcustomer

Procedure name: findCustomerOrder

Input Parameter: nOrder_Id Orders.Order_Id%type

The output should be in the following format:

Sample Data:

customer_name has ordered for Rs.order_amount on customer_date

Sample Output:

MOHAN has ordered for Rs.700 on 11-JUL-19

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

Step-by-Step Guide to Creating a PL/SQL Package to Find Customer Orders

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 customer order details based on a given order ID.

Understanding the Task:
The package will include a procedure that accepts an order ID as input and displays details such as customer name, order amount, and order date. This is useful for quickly fetching customer order 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 pkcustomer, and including a procedure findCustomerOrder:

CREATE OR REPLACE PACKAGE pkcustomer AS
 PROCEDURE findCustomerOrder(nOrder_Id Orders.Order_Id%type);
 END pkcustomer;

This specifies the package with a procedure that takes the nOrder_Id parameter, representing an order ID.

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

CREATE OR REPLACE PACKAGE BODY pkcustomer AS
 PROCEDURE findCustomerOrder(nOrder_Id Orders.Order_Id%type) AS
 CURSOR c_cust IS SELECT Customer_name, Order_amount, Order_date
 FROM Customers
 JOIN Orders USING (Customer_id) 
 WHERE Order_Id = nOrder_Id;
 v_cust c_cust%ROWTYPE;
 BEGIN
     OPEN c_cust;
     FETCH c_cust INTO v_cust;
     IF c_cust%ROWCOUNT = 0 THEN
         dbms_output.put_line('No Customer Found');
     ELSE
         dbms_output.put_line(v_cust.Customer_name || ' has ordered for Rs.' || 
         v_cust.Order_amount || ' on ' || v_cust.Order_date);
     END IF;
     CLOSE c_cust;
 END findCustomerOrder;
 END pkcustomer;

The body includes the actual logic for fetching and displaying customer order details.

Setting Up the Cursor:
We define a cursor to retrieve the customer name, order amount, and order date for the given order ID:

CURSOR c_cust IS SELECT Customer_name, Order_amount, Order_date
 FROM Customers
 JOIN Orders USING (Customer_id) 
 WHERE Order_Id = nOrder_Id;

The cursor fetches the data from the Customers and Orders tables where the order ID matches.

Opening the Cursor:
Next, we open the cursor to fetch the data:

OPEN c_cust;

This prepares the cursor to start retrieving order details.

Fetching and Displaying the Data:
We fetch the customer details and check if a record is found:

FETCH c_cust INTO v_cust;
 IF c_cust%ROWCOUNT = 0 THEN
     dbms_output.put_line('No Customer Found');
 ELSE
     dbms_output.put_line(v_cust.Customer_name || ' has ordered for Rs.' ||
     v_cust.Order_amount || ' on ' || v_cust.Order_date);
 END IF;

If a customer is found, their name, order amount, and order date are displayed. Otherwise, a message is shown.

Closing the Cursor:
Finally, after processing the result, we close the cursor to release resources:

CLOSE c_cust;

This step ensures that the cursor is properly closed after use.

Conclusion:
By following this guide, you’ve successfully created a PL/SQL package that allows you to retrieve and display customer order details for a specific order ID. This is a powerful package for dynamically accessing order information from the database.