PL/SQL to print electricity bill of the consumer

7.sql

/*
AIM:
Write a PL/SQL program using cursor to print the electricity bill of the consumer (refer unit-charge table given below)

Unit consumed charge
1 100	5
101-300	7.5
301-500	15
>500	22.5

Accept the consumer number, present and past reading of the unit consumed by customer.
*/

CREATE TABLE unit_charge (
    MIN_unit NUMBER,
    MAX_unit NUMBER,
    charge NUMBER
);

-- Insert sample data
INSERT INTO unit_charge VALUES (1, 100, 5);

INSERT INTO unit_charge VALUES (101, 300, 7.5);

INSERT INTO unit_charge VALUES (301, 500, 15);

INSERT INTO unit_charge VALUES (501, NULL, 22.5);

-- Declare the variables for consumer details and bill calculation.
DECLARE
    consumer_number NUMBER;
    present_reading NUMBER;
    past_reading NUMBER;
    unit_consumed NUMBER;
    charge_per_unit NUMBER;

    -- Cursor to fetch the charge per unit based on unit consumed.
    CURSOR unit_charge_cursor IS
        SELECT MIN_unit, MAX_unit, charge
        FROM unit_charge
        ORDER BY MIN_unit;
BEGIN
    consumer_number := &consumer_number;
    present_reading := &present_reading;
    past_reading := &past_reading;

    unit_consumed := present_reading - past_reading;

    charge_per_unit := 0;

    FOR unit_charge_rec IN unit_charge_cursor LOOP
        IF unit_consumed >= unit_charge_rec.MIN_unit THEN
            charge_per_unit := unit_charge_rec.charge;
        END IF;
    END LOOP;

    DECLARE
        total_bill NUMBER;
    BEGIN
        total_bill := unit_consumed * charge_per_unit;
        
        DBMS_OUTPUT.PUT_LINE('Consumer Number: ' || consumer_number);
        DBMS_OUTPUT.PUT_LINE('Present Reading: ' || present_reading);
        DBMS_OUTPUT.PUT_LINE('Past Reading: ' || past_reading);
        DBMS_OUTPUT.PUT_LINE('Units Consumed: ' || unit_consumed);
        DBMS_OUTPUT.PUT_LINE('Charge per Unit: ' || charge_per_unit);
        DBMS_OUTPUT.PUT_LINE('Total Bill: ' || total_bill);
    END;
END;
/