Scribd
Upload
40 views2 pages

Ex.6 Stored Procedures Functions

Uploaded by

M.Micheal Berdinanth
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
40 views2 pages

Ex.6 Stored Procedures Functions

Uploaded by

M.Micheal Berdinanth
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

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

You might also like