spy DBMS

wap for assign bounds based on job and salary

spy's photo
spy
·

1 min read

declare 
cursor c1 is select * from emp;
e emp %rowtype;
net number(10);
bonus number(10);
begin
open c1;
loop
fetch c1 into e;
exit when c1 %notfound;
net:= e.sal+nvl(e.comm,0);
if(e.job='CLERK' and net>1000) then
bonus:=800;
elsif(e.job='SALESMAN' and net>1500) 
then
bonus:=500;
else
bonus:=200;
end if;
dbms_output.put_line(e.empno || ' ' || 
e.ename||' ' ||e.sal ||' ' || net|| ' ' 
||bonus);
end loop;
dbms_output.put_line('No of records 
fetched' || c1 %rowcount);
close c1;
end;