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:
