PL/SQL Cursor ex :7 DBMS Lab

PL/SQL Cursor

SQL> create table emp(empno number(5),empname varchar(2),dob date, basicpay number(5),da number(5),allowance number(5),tax number(5),lic number(5), grosspay number(5));

Table created. 

SQL> insert into emp values(&empno,'&empname','&dob',&basicpay,&da, &allowance,&tax,&lic,&grosspay);

Enter value for empno: 101

Enter value for empname: Aa

Enter value for dob: 12-MAR-1990

Enter value for basicpay: 10000

Enter value for da: 4500

Enter value for allowance: 2000

Enter value for tax: 1000

Enter value for lic: 1200

Enter value for grosspay: 0

old   1: insert into emp values(&empno,'&empname','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspa

new   1: insert into emp values(101,'Aa','12-MAR-1990',10000,4500,2000,1000,1200,0)

1 row created.

SQL> /

Enter value for empno: 103

Enter value for empname: Cc

Enter value for dob: 26-SEP-1975

Enter value for basicpay: 17000

Enter value for da: 3200

Enter value for allowance: 2100

Enter value for tax: 1200

Enter value for lic: 1500

Enter value for grosspay: 0

old   1: insert into emp values(&empno,'&empname','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspa

new   1: insert into emp values(103,'Cc','26-SEP-1975',17000,3200,2100,1200,1500,0)

1 row created.

SQL> /

Enter value for empno: 105

Enter value for empname: Ee

Enter value for dob: 17-JAN-1989

Enter value for basicpay: 15500

Enter value for da: 3500

Enter value for allowance: 2500

Enter value for tax: 1000

Enter value for lic: 1600

Enter value for grosspay: 0

old   1: insert into emp values(&empno,'&empname','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspa

new   1: insert into emp values(105,'Ee','17-JAN-1989',15500,3500,2500,1000,1600,0)

1 row created.

SQL> /

Enter value for empno: 106

Enter value for empname: Ff

Enter value for dob: 09-APR-1986

Enter value for basicpay: 60000

Enter value for da: 5400

Enter value for allowance: 3600

Enter value for tax: 1600

Enter value for lic: 1800

Enter value for grosspay: 0

old   1: insert into emp values(&empno,'&empname','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspa

new   1: insert into emp values(106,'Ff','09-APR-1986',60000,5400,3600,1600,1800,0)

1 row created. 

SQL> /

Enter value for empno: 108

Enter value for empname: Gg

Enter value for dob: 07-OCT-1978

Enter value for basicpay: 32000

Enter value for da: 4500

Enter value for allowance: 3200

Enter value for tax: 1400

Enter value for lic: 1600

Enter value for grosspay: 0

old   1: insert into emp values(&empno,'&empname','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspa

new   1: insert into emp values(108,'Gg','07-OCT-1978',32000,4500,3200,1400,1600,0)

1 row created. 

SQL> select * from emp;

     EMPNO EM DOB         BASICPAY         DA  ALLOWANCE        TAX        LIC   GROSSPAY

      ---------- -- ---------    --------------- ---------- ------------------- ---------- ----------          ----------

       101        Aa   12-MAR-90      10000       4500       2000       1000       1200         0

       103        Cc   26-SEP-75      17000       3200       2100       1200       1500         0

       105        Ee   17-JAN-89      15500       3500       2500       1000       1600         0

     EMPNO EM DOB         BASICPAY         DA  ALLOWANCE        TAX        LIC   GROSSPAY

      ---------- -- ---------    --------------- ---------- ------------------- ---------- ----------          ----------

       106   Ff        09-APR-86      60000       5400       3600       1600       1800         0

       108    Gg        07-OCT-78      32000       4500       3200       1400       1600         0 

SQL> ed

Wrote file afiedt.buf

line 25 truncated.

  1  declare

  2  cursor c is select * from emp order by grosspay desc;

  3  rec emp % rowtype;

  4  cnt integer:=0;

  5  begin

  6  open c;

  7  loop

  8  fetch c into rec;

  9  exit when c% notfound;

10  if(cnt<5) then

11  update emp set grosspay=(rec.basicpay+rec.da+rec.allowance)-(rec.lic+rec.tax) where empno=rec.e

12  dbms_output.put_line('Employee no:'||rec.empno);

13  dbms_output.put_line('Name:'||rec.empname);

14  dbms_output.put_line('Basic Pay:'||rec.basicpay);

15  dbms_output.put_line('DA:'||rec.da);

16  dbms_output.put_line('Allowance:'||rec.allowance);

17  dbms_output.put_line('Tax:'||rec.tax);

18  dbms_output.put_line('LIC:'||rec.lic);

19  dbms_output.put_line('Grosspay:'||rec.grosspay);

20  cnt:=cnt+1;

21  end if;

22  end loop;

23  close c;

24* end;

25  /

PL/SQL procedure successfully completed. 

SQL> select * from emp; 

     EMPNO EM DOB         BASICPAY         DA  ALLOWANCE        TAX        LIC   GROSSPAY

      ---------- -- ---------    --------------- ---------- ------------------- ---------- ----------          ----------                           

        101   Aa   12-MAR-90    10000       4500       2000           1000       1200     14300

        103   Cc   26-SEP-75      17000       3200       2100           1200       1500      19600 

       105   Ee   17-JAN-89      15500       3500       2500           1000       1600     18900 

     EMPNO EM DOB         BASICPAY         DA  ALLOWANCE        TAX        LIC   GROSSPAY

        ---------- -- ---------    --------------- ---------- ------------------- ---------- -------   ----------

       106    Ff   09-APR-86      60000       5400         3600            1600       1800     65600 

     108    Gg    07-OCT-78      32000       4500         3200            1400       1600     36700