Sri Sivasubramaniya Nadar College of Engineering
(An Autonomous Institution, Affiliated to Anna University, Chennai)
Computer Science and Engineering
UCS2411 – Database Management System Lab
Assignment 6: PL/SQL – Stored Procedures & Stored Functions
Batch 2022-2026 Academic Year 2023-2024( Even)
Faculty: Dr. P. Mirunalini and Dr. N. Sujaudeen
Learning Outcome: Construct PL/SQL block (K3,CO3)
Best Practices: use implicit and explicit cursor, DIsplay appropriate message if data not available,
use appropriate cursor attributes
Write a PL/SQL stored procedure / stored function for the following:
Pizza Ordering System
Consider the following relations for Pizza Ordering System and apply suitable queries to display
the required output: (K3, 1.3.1,1.4.1,2.4.1,13.3.1)
CUSTOMER (cust_id, cust_name, address, phone)
PIZZA (pizza_id, pizza_type, unit_price)
ORDERS (order_no, cust_id, order_date, delv_date)
ORDER_LIST (order_no, pizza_id, qty)
1.Write a stored procedure to display the total number of pizza's ordered by
the given order number. (Use IN, OUT)
2. For the given order number, calculate the Discount as follows:
For total amount > 2000 and total amount < 5000: Discount=5%
For total amount > 5000 and total amount < 10000: Discount=10%
For total amount > 10000: Discount=20%
Calculate the total amount (after the discount) and update the same in
orders table. Print the order as shown below:
************************************************************
Order Number:OP104 Customer Name: Hari
Order Date :29-Jun-2015 Phone: 9001200031
************************************************************
SNo Pizza Type Qty Price Amount
1. Italian 6 200 1200
2. Spanish 5 260 1300
------------------------------------------------------
Total = 11 2500
------------------------------------------------------
Total Amount :Rs.2500
Discount (5%) :Rs. 125
-------------------------- ----
Amount to be paid :Rs.2375
-------------------------- ----
Great Offers! Discount up to 25% on DIWALI Festival Day...
*************************************************************
3. Write a stored function to display the customer name who ordered highest among the total
number of pizzas for a given pizza type.
4. Implement Question (2) using a stored function to return the amount to be paid and update the
same, for the given order number.
What you have to submit:
1. Schema Diagram with constraints
2. Demo script file
