set serveroutput on
declare
cursor cremp is
select Eno, ESalary from Emp;
begin
for rec1 in cremp
loop
update Emp set ESalary = rec1.ESalary + rec1.ESalary * 0.2
where Eno = rec1.Eno;
end loop;
end;
/
commit;
/
Easy Learning... (Web Development,.NET Technology,Oracle,Silverlight,REST,SQL,Oracle,MySQL,Data Science, Python and many more ...)
Tuesday, September 22, 2020
RDBMS PLSQL Program 20 Explicit Cursor with If condition
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;
VESalary := VESalary + VESalary * 0.1;
if VESalary < 100000 then
update Emp set
ESalary = VESalary
where Eno = VEno;
end if;
end loop;
close cremp;
end;
/
commit;
/
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;
/
Saturday, September 19, 2020
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;
/
Subscribe to:
Comments (Atom)