- record creation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
declare
Type emp_info is Record
( empno emp.empno%type,
ename emp.ename%type,
hiredate emp.hiredate%type,
sal emp.sal%type
);
mrec emp_info;
mempno emp.empno%type; service_yrs number;
begin
mempno := &aempno;
select empno,ename,hiredate,sal into mrec
from emp where empno = mempno;
service_yrs := trunc(months_between(sysdate,mrec.hiredate)/12);
if service_yrs > 25 then
mrec.sal := mrec.sal + (mrec.sal*.2);
else
mrec.sal := mrec.sal + (mrec.sal*.02);
end if;
dbms_output.put_line('the revised salary of employee ' || mrec.ename || ' is ' || mrec.sal);
End;
- cursors
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp Order by ename;
mrec cemp%rowtype; service_yrs number;
begin
open cemp;
loop
fetch cemp into mrec;
exit when cemp%notfound;
service_yrs := trunc(months_between(sysdate,mrec.hiredate)/12);
if service_yrs > 25 then
mrec.sal := mrec.sal + (mrec.sal*.2);
else
mrec.sal := mrec.sal + (mrec.sal*.02);
end if;
dbms_output.put_line('the revised salary of employee ' || mrec.ename || ' is ' || mrec.sal);
end loop;
close cemp;
End;
- cursors for loop
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
declare
cursor cemp is select empno,ename,job,sal,hiredate from emp Order by ename;
service_yrs number;
begin
for mrec in cemp
loop
service_yrs := trunc(months_between(sysdate,mrec.hiredate)/12);
if service_yrs > 25 then
mrec.sal := mrec.sal + (mrec.sal*.2);
else
mrec.sal := mrec.sal + (mrec.sal*.02);
end if;
dbms_output.put_line('the revised salary of employee ' || mrec.ename || ' is ' || mrec.sal);
end loop;
End;
- cursor update multiple records
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp Order by ename for update of sal;
service_yrs number;
begin
for mrec in cemp
loop
service_yrs := trunc(months_between(sysdate,mrec.hiredate)/12);
dbms_output.put_line('the original salary of employee ' || mrec.ename || ' is ' || mrec.sal);
if service_yrs > 25 then
mrec.sal := mrec.sal + (mrec.sal*.2);
else
mrec.sal := mrec.sal + (mrec.sal*.02);
end if;
update rahul_emp
set sal=mrec.sal
where current of cemp;
dbms_output.put_line('the revised salary of employee ' || mrec.ename || ' is ' || mrec.sal);
end loop;
End;
- cursor code my own for updating perticular record
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp where job='&oldInput' Order by ename for update of job;
oldJobTitle rahul_emp.job%type;
newJobTitle rahul_emp.job%type;
updated boolean;
begin
newJobTitle:='&newInput';
updated:=false;
for mrec in cemp
loop
dbms_output.put_line('the original job title of employee ' || mrec.ename || ' is ' || mrec.job);
update rahul_emp
set job=newJobTitle
where current of cemp;
dbms_output.put_line('the revised job title of employee ' || mrec.ename || ' is ' || mrec.job);
updated:=true;
end loop;
if not updated then
dbms_output.put_line('job title not found');
end if;
End;
- cursor update modified
1
2
3
4
5
6
7
8
9
10
11
12
begin
update rahul_emp
set
job ='&newJobTitle'
where job ='&oldJobTitle';
if sql%notfound then
dbms_output.put_line('no record found');
else
dbms_output.put_line(' record found and updated '||sql%rowcount);
end if;
End;