PL/SQL Procedures and Functions for
Online Retail Management System
1. Core Procedures
Add Customer
CREATE OR REPLACE PROCEDURE add_customer (
p_name IN VARCHAR2,
p_email IN VARCHAR2,
p_phone IN VARCHAR2,
p_address IN VARCHAR2
) AS
BEGIN
INSERT INTO Customer (Customer_ID, Name, Email, Phone,
Address)
VALUES (Customer_SEQ.NEXTVAL, p_name, p_email, p_phone,
p_address);
END;
Add Supplier
CREATE OR REPLACE PROCEDURE add_supplier (
p_name IN VARCHAR2,
p_gst IN VARCHAR2,
p_email IN VARCHAR2,
p_phone IN VARCHAR2
) AS
BEGIN
INSERT INTO Supplier (Supplier_ID, Name, GST_No, Email, Phone)
VALUES (Supplier_SEQ.NEXTVAL, p_name, p_gst, p_email, p_phone);
END;
Add Product with Category
CREATE OR REPLACE PROCEDURE add_product (
p_name IN VARCHAR2,
p_desc IN VARCHAR2,
p_cost_price IN DECIMAL,
p_category_id IN INT
) AS
BEGIN
INSERT INTO Product (Product_ID, Name, Description, Cost_Price,
Category_ID)
VALUES (Product_SEQ.NEXTVAL, p_name, p_desc, p_cost_price,
p_category_id);
END;
Update Inventory
CREATE OR REPLACE PROCEDURE update_inventory (
p_product_id IN INT,
p_supplier_id IN INT,
p_quantity IN INT
) AS
BEGIN
INSERT INTO Inventory (Inventory_ID, Product_ID, Supplier_ID,
Quantity)
VALUES (Inventory_SEQ.NEXTVAL, p_product_id, p_supplier_id,
p_quantity);
END;
Create Order
CREATE OR REPLACE PROCEDURE create_order (
p_customer_id IN INT,
p_order_date IN DATE,
p_total_amount IN DECIMAL
) AS
BEGIN
INSERT INTO Order (Order_ID, Customer_ID, Order_Date,
Total_Amount)
VALUES (Order_SEQ.NEXTVAL, p_customer_id, p_order_date,
p_total_amount);
END;
2. Useful Functions
Get Stock of a Product
CREATE OR REPLACE FUNCTION get_stock (
p_product_id IN INT
) RETURN INT IS
v_quantity INT;
BEGIN
SELECT SUM(Quantity)
INTO v_quantity
FROM Inventory
WHERE Product_ID = p_product_id;
RETURN NVL(v_quantity, 0);
END;
Calculate Order Total
CREATE OR REPLACE FUNCTION get_order_total (
p_order_id IN INT
) RETURN DECIMAL IS
v_total DECIMAL;
BEGIN
SELECT SUM(Quantity * Selling_Price)
INTO v_total
FROM Order_Item
WHERE Order_ID = p_order_id;
RETURN NVL(v_total, 0);
END;
3. Reports (Procedures with Cursor)
Sales Report by Date
CREATE OR REPLACE PROCEDURE sales_report (
p_start_date IN DATE,
p_end_date IN DATE,
p_report OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_report FOR
SELECT o.Order_ID, [Link] AS Customer_Name, o.Order_Date,
o.Total_Amount
FROM Order o
JOIN Customer c ON o.Customer_ID = c.Customer_ID
WHERE o.Order_Date BETWEEN p_start_date AND p_end_date;
END;
Inventory Report
CREATE OR REPLACE PROCEDURE inventory_report (
p_report OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_report FOR
SELECT [Link], SUM([Link]) AS Stock
FROM Inventory i
JOIN Product p ON i.Product_ID = p.Product_ID
GROUP BY [Link];
END;
4. Workflow (Execution Flow)
1. Create Customers, Suppliers, Categories
2. Insert Products with Category and Supplier
3. Update Inventory when stock arrives
4. Create Orders (with Order Items)
5. Process Payment
6. Generate Reports (sales, inventory, customers)