Building a Real-Time Inventory System in PL/SQL

Build a real-time inventory system example using:

  • Inventory and transaction tables

  • Data records

  • A cursor for low-stock alerts

  • A trigger for auditing sales

✅ Inventory System 

Tables:

  • tblProducts: holds product details and stock

  • tblSales: tracks product sales

  • tblSales_Audit: logs all sales automatically via trigger

✅ Step 1: Create Tables

-- Product master table
CREATE TABLE tblProducts (
    product_id    NUMBER PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    quantity      NUMBER(10) NOT NULL,
    price         NUMBER(10,2) NOT NULL
);

-- Sales table
CREATE TABLE tblSales (
    sale_id      NUMBER PRIMARY KEY,
    product_id   NUMBER NOT NULL,
    quantity_sold NUMBER NOT NULL,
    sale_date    DATE DEFAULT SYSDATE,
    FOREIGN KEY (product_id) REFERENCES tblProducts(product_id)
);

-- Sales audit table
CREATE TABLE tblSales_Audit (
    audit_id     NUMBER PRIMARY KEY,
    sale_id      NUMBER,
    product_id   NUMBER,
    quantity     NUMBER,
    action       VARCHAR2(20),
    log_time     DATE
);

✅ Step 2: Create Sequences

CREATE SEQUENCE seq_product_id START WITH 1;
CREATE SEQUENCE seq_sale_id START WITH 1001;
CREATE SEQUENCE seq_audit_id START WITH 5001;

✅ Step 3: Insert Sample Products

INSERT INTO tblProducts VALUES (seq_product_id.NEXTVAL, 'Laptop',        10, 60000);
INSERT INTO tblProducts VALUES (seq_product_id.NEXTVAL, 'Mouse',         100, 400);
INSERT INTO tblProducts VALUES (seq_product_id.NEXTVAL, 'Keyboard',      50, 800);
INSERT INTO tblProducts VALUES (seq_product_id.NEXTVAL, 'Monitor',       15, 10000);
INSERT INTO tblProducts VALUES (seq_product_id.NEXTVAL, 'Printer',       5, 15000);

COMMIT;

✅ Cursor Example: Show Low Stock Products

DECLARE
    CURSOR cur_low_stock IS
        SELECT product_name, quantity
        FROM tblProducts
        WHERE quantity < 10;

    v_name tblProducts.product_name%TYPE;
    v_qty  tblProducts.quantity%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Low Stock Items:');
    FOR rec IN cur_low_stock LOOP
        DBMS_OUTPUT.PUT_LINE('Product: ' || rec.product_name || ', Stock: ' || rec.quantity);
    END LOOP;
END;
/

✅ Use Case:

  • Used to alert purchase department of low-stock items

  • Could also trigger reorder workflow in a real system

✅ Trigger Example: Audit Each Sale

Trigger to Log Sale into tblSales_Audit

CREATE OR REPLACE TRIGGER trg_audit_sales
AFTER INSERT ON tblSales
FOR EACH ROW
BEGIN
    INSERT INTO tblSales_Audit (
        audit_id, sale_id, product_id, quantity, action, log_time
    )
    VALUES (
        seq_audit_id.NEXTVAL,
        :NEW.sale_id,
        :NEW.product_id,
        :NEW.quantity_sold,
        'SALE',
        SYSDATE
    );
END;
/

✅ Procedure Example: Sell a Product with Stock Check

CREATE OR REPLACE PROCEDURE sp_sell_product(
    p_product_id   IN tblProducts.product_id%TYPE,
    p_qty          IN tblSales.quantity_sold%TYPE
)
AS
    v_stock tblProducts.quantity%TYPE;
    v_price tblProducts.price%TYPE;
BEGIN
    SELECT quantity, price INTO v_stock, v_price
    FROM tblProducts
    WHERE product_id = p_product_id;

    IF v_stock < p_qty THEN
        DBMS_OUTPUT.PUT_LINE('Not enough stock!');
        RETURN;
    END IF;

    -- Insert into sales table
    INSERT INTO tblSales (sale_id, product_id, quantity_sold)
    VALUES (seq_sale_id.NEXTVAL, p_product_id, p_qty);

    -- Update stock
    UPDATE tblProducts
    SET quantity = quantity - p_qty
    WHERE product_id = p_product_id;

    DBMS_OUTPUT.PUT_LINE('Sale completed. Total: ₹' || (p_qty * v_price));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Product not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

✅ Test the Full Flow

-- Sell 3 monitors
BEGIN
    sp_sell_product(4, 3);
END;
/

-- Sell 6 printers (should fail if stock < 6)
BEGIN
    sp_sell_product(5, 6);
END;
/

-- Show low stock report
-- (should include Printer if below 10)

✅ View Results

SELECT * FROM tblProducts;
SELECT * FROM tblSales;
SELECT * FROM tblSales_Audit;

✅ Summary

FeatureDemo Provided
TablesProducts, Sales, Sales_Audit
SequenceAuto ID for all tables
CursorLow stock checker
TriggerSale audit logger
ProcedureProduct sale with validation

Comments

Post a Comment

Popular posts from this blog

Interview Tips: Dot NET Framework vs Net CORE

Image
Interview Tips: Dot NET Framework vs Net CORE .NET Framework What :  It's a software development framework created by Microsoft, mainly used for building Windows applications. Why :  It has been around for a long time and is very reliable for building large, complex applications especially for enterprises. How :  You use it to build applications that run exclusively on Windows, making use of a vast array of built-in libraries and features. Why Not :  It can't be used for cross-platform applications, so if you need your application to run on other operating systems like macOS or Linux, .NET Framework isn't the right choice. .NET Core What :  It's a newer, open-source, and cross-platform framework from Microsoft, designed for modern app development. Why :  It's lightweight, high-performance, and can run on multiple operating systems (Windows, macOS, Linux). How :  You use it to build modern, scalable, and quickly deployable applications, especially cloud...
Read more

FREE Webinar: Run Your Own Independent DeepSeek LLM

Image
Faith Infotech Academy, Technopark invites you to an exclusive FREE webinar on "Run Your Own Independent DeepSeek LLM" , where you’ll learn how to deploy and manage DeepSeek LLM effectively. Speaker:  Abhilash Nelson  Trainer & Researcher in AI & Machine Learning  An expert in AI and machine learning, Abhilash will guide you through the process of setting up and running your own independent DeepSeek LLM, empowering you with insights into model deployment, fine-tuning, and real-world applications. What You’ll Learn: ✅ Understanding DeepSeek LLM and its capabilities ✅ Setting up DeepSeek LLM on your own infrastructure ✅ Fine-tuning and optimizing the model for various applications ✅ Use cases and practical demonstrations Key Takeaways: ✅ Mastering DeepSeek LLM for Developers ✅ Boost Your VS Code workflow ✅ Learn to Run DeepSeek locally for AI assistance 📅 Date: 6 February 2025, Thursday 🕒 Time:  10.00 to 11.00 AM 📍 Mode: Online (Webinar link will be shar...
Read more

Delegates and Events

1. What are Delegates in C#? Explain: Delegates are type-safe function pointers. They act as a placeholder for methods that have a specific signature (return type and parameters). Analogy: Imagine a delegate as a contract. It specifies the "shape" of the method it can hold. Any method that fits that shape (same return type and parameters) can be assigned to the delegate. Example: public delegate int MathOperation(int x, int y);  // Define a delegate // Methods that match the delegate signature public int Add(int x, int y) { return x + y; } public int Subtract(int x, int y) { return x - y; } // Use the delegate MathOperation operation = Add; // Assign the Add method to the delegate int result = operation(5, 3); // Call the method through the delegate 2. What are Events in C#? Explain: Events are a mechanism for broadcasting notifications to interested parties (subscribers). They are built upon delegates. Analogy: Imagine an event as a doorbell. When someone presses the d...
Read more