Electricity billing system using PL/SQL

Objective

Create an electricity billing system, rent rs 20/-

Slab 1 : 1-40 units-0

Slab2: 40-80 units -40

Slab3: >80 -1.40+excess of 80

Program

SQL> create table electricity(cons_id varchar(4) primary key, c_name varchar(20), rent number(2) check (rent=20), unit number(6));

Table created

SQL> insert into electricity values (‘E001’,’deepika’,20,35);

1 row created

SQL> insert into electricity values (‘E002’,’varna’,20,61);

1 row created

SQL> insert into electricity values (‘E003’,’arun’,20,80);

1 row created

SQL> insert into electricity values (‘E004’,’rahul’,20,90);

1 row created

SQL> alter table electricity add (total number (6,2));

Table altered

SQL>declare

2 v_total electricity.total%type;

3 cursor c is select * from electricity;

4 begin

5 for I in c loop

6 if (i.unit <=40) then

7 v_total:=i.rent;

8 elseif (i.unit<=80) then

9 v_total:= i.rent+(i.unit-40)*.40;

10 else

11  v_total:= i.rent+(40*.40)+(i.unit-80)*1.40;

12 end if

13 update electricity set total=v_total,where

14 cons_id = i.cons_id;

15 end loop;

16 end;

17 /

PL/SQL procedure successfully completed

SQL> select* from electricity;

Cons   c_name     rent    unit    total

E001   deepika      20       35       20

E002    varna         20        61       28.4

E003    arun        20          80       36

E004     rahul      20          90        50