ELECTRICITY BILLING SYSTEM

create table electricity(customer_id varchar2(11),ename varchar2(8),phase
number(1),prereading number(4)
,currentreading number(4),unit_consumed number(4),metercharge
number(2),charge_unit number(3),total_charge number(6));
insert into electricity values('e101','Hasna',1,456,564,null,null,null,null);
insert into electricity values('e102','Umaibanu',3,1235,1545,null,null, null,null);
insert into electricity values('e103','Sruthi',3,2785,3575,null,null,null,null);
insert into electricity values('e104',Raheele',2,2345,3458,null,null,null,null);
declare i
cursor c1 is
select * from electricity order by customer_id;
eb c1%rowtype;
begin
dbms_output.put_line('------------------KSEB-----------------------');
dbms_output.put_line('------------------CUSTOMER BILL REPPORT FOR THE
MONTH OF -------------');
dbms_output.put_line('DATE:'||sysdate);
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('customer_id ename prereading currentreading phase
unit_consumed metercharge charge_unit total_charge');
open c1;
loop
fetch c1 into eb;
exit when c1%notfound;
if(eb.phase=1) then
eb.metercharge:=20;
else
eb.metercharge:=40;
end if;
eb.unit_consumed:=(eb.currentreading)-(eb.prereading);
if((eb.unit_consumed)>1 and (eb.unit_consumed)<40) then
eb.charge_unit:=0;
else if((eb.unit_consumed)>40 and (eb.unit_consumed)<80) then
eb.charge_unit:=0.4;
else
eb.charge_unit:=1.4+((eb.unit_consumed)-80)*0.4;
end if;
end if;
if (eb.unit_consumed<40 and eb.phase=3) then
eb.total_charge:=240;
else
eb.total_charge:=( eb.unit_consumed) * (eb.charge_unit);
end if;
update electricity set
unit_consumed=eb.unit_consumed,metercharge=eb.metercharge,charge_unit=eb.ch
arge_unit,total_charge=eb.total_charge where customer_id=eb.customer_id;
dbms_output.put_line('eb.customer_id,eb.name,eb.prereading,eb.currentreading,eb.
phase,eb.unit_consumed,eb.metercharge,eb.charge_unit,eb.total_charge');
dbms_output.put_line('-------------------------------------------------');
end loop;
dbms_output.put_line('-----------------billing agent------------');
dbms_output.put_line('-----------------chief engineer------------------ ');
close c1;
dbms_output.put_line('--------------------------------------');
end;
/

OUTPUT

……………………………………………KSEB………………………………………
........ ……………….CUSTOMER BILL REPORT FOR THE MONTH OF …………………
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------
CUSTOMER_ID CNAME PHASE PREREADING CURRENTREADING
UNIT_CONSUMED METERCHARGE CHARGE_UNIT TOTAL_CHARGE
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --
e101 Hasna 1 456 564
108 20 13 1404
e102 Umaibanu 3 1235 1545
310 40 93 28830
e103 Sruthi 3 2785 3575
790 40 285 225150
e104 Raheela 2 2345 3458
1113 40 415 461895
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --