PL/SQL Block to calculate the electricity bill

Q. Write a PL/SQL block to calculate the electricity bill for customers.

I) Insert the Meter no., Units used, Rate, Amount, Surcharge, Excise duty and Net amount for each customer into some other suitable table .
II) Insert the total Amount, Surcharge, Excise and Net(total) into some other table.

Answer:

Formulas:
i) Units used = Current Reading or Previous Reading
ii) Rate =Rs.1/ 1.25/ 1.50/ 1.30 for A/I/C/R respectively.
iii) Amount = rate*units used
iv) Surcharge = 5% for single phase , 10% for 3 phase
v) Excise = 30% of (amount +Surcharge)
vi) Net = Amount +Surcharge + Excise

Step1: Create a table CUSTOMER

CREATE TABLE CUSTOMER ( "Meter Number" Varchar2(4), "Meter Type" Character(1), "Previous Reading" Number(5), "Current Reading" Number(5), "Customer Type" Character(1), "Last Bill payment" Character(1) check("Last Bill payment"='Y' OR "Last Bill payment"='N') )


Create table Bill:

CREATE TABLE bill ( "Meter Number" Varchar2(4) PRIMARY KEY, units number, rate number, amount number, surcharge number, Excise number, Net number )


Step2: Create a Procedure 'calculatebill'

CREATE OR REPLACE PROCEDURE calculatebill AS v_customer customer%rowtype;
v_bill bill%rowtype;
CURSOR c1 IS
SELECT *
FROM customer;
rate number(3,2);
units number;
amount number;
surcharge number;
Excise number;
Net number;  
BEGIN
DELETE
FROM bill;

FOR v_customer IN c1 LOOP
SELECT decode(v_customer."Customer Type",'A',1,'I',1.25,'C',1.50,'R',1.30) INTO rate
FROM dual;

SELECT decode(v_customer."Meter Type",'T',10,'S',5) INTO surcharge
FROM dual;

units:=v_customer."Current Reading"-v_customer."Previous Reading";
amount:=rate*units;
surcharge:=surcharge*amount;
Excise:=(amount +Surcharge)*30/100;
Net:= Amount +Surcharge + Excise;

INSERT INTO bill
VALUES(v_customer."Meter Number",
       units,
       rate,
       amount,
       surcharge,
       Excise,
       Net);

END LOOP;
END;
/


Step 3: Execution

The following command is used to execute the above stored procedure.

EXEC calculatebill;

Output:

electricity bill
▲