Last active
November 7, 2023 02:49
-
-
Save rithask/b8b8651267ef51b580fd3ccf9d754951 to your computer and use it in GitHub Desktop.
PL/SQL to print electricity bill of the consumer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment