RDBMS PLSQL Program 19 Explicit Cursor with While loop

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

cursor cremp is 

select Eno, ESalary from Emp;

begin

open cremp;

fetch cremp into VEno,VESalary;

while cremp%found

loop

update Emp set ESalary = VESalary + VESalary * 0.1 where Eno = VEno;

fetch cremp into VEno,VESalary;

end loop;

close cremp;

end;

/

commit;

/

RDBMS PLSQL Program 18 Explicit Cursor with Basic loop

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

cursor cremp is

select Eno, ESalary from Emp;

begin

open cremp;

loop

fetch cremp into VEno,VESalary;

exit when cremp%NOTFOUND;

update Emp set ESalary = VESalary - VESalary * 0.05 where Eno = VEno;

end loop;

close cremp;

end;

/

commit;

/

RDBMS PLSQL Program 17 Example of Implicit Cursor

 set serveroutput on

begin

update Emp set ESalary = 75000

where Eno = 'E00001';

if SQL%isopen = true then

if sql%found = true then

dbms_output.put_line(sql%rowcount);

else

dbms_output.put_line('No Rows1');

end if;

else

dbms_output.put_line('No Rows2');

end if;

end;

/

commit;

/

RDBMS PLSQL Program 16 get data from user, select and update table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

if VESalary > 50000 then 

VESalary := VESalary - 3000;

update emp set ESalary = VESalary 

where Eno = VEno;

end if;

end;

commit;

/

RDBMS PLSQL Program 15 get data from user, select and update table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

if VESalary > 10000 then 

VESalary := VESalary+2000;

update emp set ESalary = VESalary 

where Eno = VEno;

end if;

end;

commit;

/

RDBMS PLSQL Program 14 get data from user, select, update and delete from table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

VESalary := VESalary + 5000;

update Emp set ESalary = VESalary

where Eno = VEno;

if VESalary < 80000 then 

delete from emp

where Eno = VEno;  

end if;

end;

commit;

/

RDBMS PLSQL Program 13 get data from user and delete from table

 set serveroutput on

declare

VEno char(6);

begin

VEno := '&VEno';

delete from EMP

where Eno = VEno;

end;

commit;

/

RDBMS PLSQL Program 12 get data from user and update table

 set serveroutput on

declare

VEno char(6);

VNewSalary number(10,2);

begin

VEno := '&VEno';

VNewSalary := &VNewSalary;

update Emp Set ESalary = VNewSalary

where Eno = VEno;

end;

commit;

/

RDBMS PLSQL Program 11 get data from user and insert into table

 set serveroutput on

declare

VEno char(6);

VEname varchar(30);

VESalary number(10,2);

VEDate date;

VEDno char(3);

begin

VEno := '&VEno';

VEname := '&VEname';

VESalary := &VSalary;

VEDate := '&VEDate';

VEDno := '&vEDno';

insert into Emp values(VEno,VEname,VESalary,VEDate,VEDno);

end;

commit;

/

RDBMS SQL Query to Create table and Insert Into the table

 Create Table EMP

(

Eno char(6) Primary Key,

Ename varchar(30),

Esalary number(10,2),

EDoj Date,

EDno char(3)

)

/

insert into Emp values

(

'E00001','ABC',10000,'02-Aug-2017','D01'

);

insert into Emp values

(

'E00002','XYZ',50000,'02-Jan-2016','D02'

);

insert into Emp values

(

'E00003','MNO',40000,'01-Sep-2017','D03'

);

insert into Emp values

(

'E00004','PQR',50000,'24-Feb-2020','D03'

);

insert into Emp values

(

'E00005','ABC',10000,'30-Aug-2020','D01'

);

/