STORED PROCEDURE
A stored procedure in SQL is a set of precompiled SQL statements that can be executed as a
single unit. Stored procedures are stored in the database and can be invoked by applications
or users, allowing for greater modularity and reusability of code. Here are some key aspects:
Key Features:
1. Modularity: Stored procedures encapsulate complex SQL logic, making it easier to
manage and reuse code across multiple applications.
2. Performance: Since stored procedures are precompiled, they can improve
performance compared to executing individual SQL statements repeatedly.
3. Security: They can restrict direct access to the underlying tables, allowing users to
perform operations without having direct permissions on the tables.
4. Parameterization: Stored procedures can accept parameters, making them versatile
for various operations (e.g., inserting data, retrieving records).
Usage:
• Data Manipulation: Insert, update, delete records in the database.
• Business Logic Implementation: Enforce business rules by incorporating conditional
logic.
• Batch Processing: Execute multiple SQL commands in a single call, which is
efficient for batch operations.
Dataset:
https://drive.google.com/drive/u/0/folders/1n6JT261WuEhRgfoEgQX2r2nYFVXm1DWu
1
2
Here are some examples of stored procedures based on the tables:
1. Stored Procedure to Insert a New Order.
This stored procedure will insert a new order and its details into the order and order_details
tables.
CREATE PROCEDURE AddNewOrder(
IN input_user_id INT,
IN input_r_id INT,
IN input_amount DECIMAL(10,2),
IN input_date DATE,
IN input_partner_id INT,
IN input_delivery_time INT,
IN input_delivery_rating INT,
IN input_restaurant_rating INT,
IN input_f_ids VARCHAR(255)
)
BEGIN
-- Declare variables
DECLARE new_order_id INT;
DECLARE f_id1 INT;
DECLARE f_id2 INT;
-- Split food items
SET f_id1 = SUBSTRING_INDEX(input_f_ids, ',', 1);
SET f_id2 = SUBSTRING_INDEX(input_f_ids, ',', -1);
-- Get new order ID
SELECT MAX(order_id) + 1 INTO new_order_id FROM orders;
-- Insert into orders table
INSERT INTO orders (
order_id, user_id, r_id, amount, date, partner_id, delivery_time, delivery_rating,
restaurant_rating
) VALUES (
3
new_order_id, input_user_id, input_r_id, input_amount, input_date, input_partner_id,
input_delivery_time, input_delivery_rating, input_restaurant_rating
);
-- Insert into order_details table
INSERT INTO order_details (order_id, f_id)
VALUES (new_order_id, f_id1), (new_order_id, f_id2);
END
CALL AddNewOrder(1, 3, 300, '2024-10-10', 1, 30, 5, 4, '3,6');
SELECT * FROM orders;
2. Stored Procedure to Update Delivery Rating for an Order
This procedure updates the delivery_rating of an order based on the order_id.
CREATE PROCEDURE UpdateDeliveryRating(
4
IN input_order_id INT,
IN new_delivery_rating INT
BEGIN
UPDATE orders
SET delivery_rating = new_delivery_rating
WHERE order_id = input_order_id;
END
------------------------------------------------------------------------------
CALL UpdateDeliveryRating(1001, 4);
SELECT * FROM orders;
5
3. Stored Procedure to Get Total Amount Spent by a User
This stored procedure calculates the total amount spent by a user based on their
user_id.
CREATE PROCEDURE GetUserTotalSpent(
IN input_user_id INT,
OUT total_spent DECIMAL(10,2)
BEGIN
SELECT SUM(amount) INTO total_spent
FROM orders
WHERE user_id = input_user_id;
END
----------------------------------------------------------------------------------
CALL GetUserTotalSpent(2, @total_spent);
SELECT @total_spent;
6
4. Stored Procedure to Get All Orders with a Specific Partner
This procedure retrieves all orders that were delivered by a specific delivery partner based
on partner_id.
CREATE PROCEDURE GetOrdersByPartner(
IN input_partner_id INT
BEGIN
SELECT *
FROM orders
WHERE partner_id = input_partner_id;
END
-------------------------------------------------------------------------------------------
CALL GetOrdersByPartner(1);
5. Stored Procedure to Get Order Details for a Specific Order
This procedure retrieves all the details of a specific order, including the food items ordered.
CREATE PROCEDURE GetOrderDetails(
7
IN input_order_id INT
BEGIN
SELECT o.order_id, o.user_id, o.r_id, o.amount, o.date, d.f_id
FROM orders o
JOIN order_details d ON o.order_id = d.order_id
WHERE o.order_id = input_order_id;
END
CALL GetOrderDetails(1001);
