11) employee
NO NAME JOB SAL DEPT_NO
---------- -------- ----------
---------- ----------
101 renuka staff 10000 1
102 krishna staff 10000 1
103 rachna HOD 20000 1
201 mansi staff 10000 2
202 jenny staff 10000 2
203 teena HOD 20000 2
301 pooja staff 10000 3
302 pinky staff 10000
3
303 sevu HOD 20000 3
400 fairy Principal 50000 4
INPUT
declare
cursor cur is select no,sal,dept_no
from em where dept_no=1;
mno em.no%type;
msal em.sal%type;
mdept_no em.dept_no%type;
begin
open cur;
loop
fetch cur into mno,msal,mdept_no;
exit when cur%notfound;
update em set sal=sal+sal*0.1 where
no=mno;
insert into era
values(mno,msal*0.1,mdept_no,sysdate);
end loop;
if cur%rowcount > 0 then
dbms_output.put_line('no
of rows updated are:'||cur%rowcount);
else
dbms_output.put_line('no
such record found');
end if;
close cur;
end;
/
OUTPUT
no of rows updated are:3
PL/SQL procedure successfully
completed.
Table:- era
NO SAL DEPT_NO
IDATE
---------- ---------- ----------
---------
101 1000 1 18-FEB-14
102 1000 1 18-FEB-14
103 2000 1 18-FEB-14
101 1100 1 18-FEB-14
102 1100 1 18-FEB-14
103 2200 1 18-FEB-14
Table:- em
NO NAME JOB SAL DEPT_NO
---------- -------- ----------
---------- ----------
101 renuka staff 11100 1
102 krishna staff 11100 1
103 rachna HOD 22200 1
201 mansi staff 10000 2
202 jenny staff 10000 2
203 teena HOD 20000 2
301 pooja staff 10000 3
302 pinky staff 10000 3
303 sevu HOD 20000 3
400 fairy Principal 50000 4
0 Comments