- procedure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
create or replace procedure rahul_increment_emp
(phiredate rahul_emp.hiredate%type, psal rahul_emp.sal%type, incr_sal out rahul_emp.sal%type) is
service_yrs number;
Begin
service_yrs := trunc(months_between(sysdate,phiredate)/12);
if service_yrs > 25 then
incr_sal := psal + (psal*.2);
elsif service_yrs > 23 then
incr_sal := psal + (psal*.15);
else
incr_sal := psal + (psal*.02);
end if;
End;
- block calling procedure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp
where deptno = 10
for update of sal;
new_sal rahul_emp.sal%type;
begin
For mrec in cemp
loop
rahul_increment_emp(mrec.hiredate,mrec.sal,new_sal);
update rahul_emp set sal = new_sal
where current of cemp;
end loop;
End;
- procedure with inout access to variable
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure rahul_increment_emp
(phiredate emp.hiredate%type, psal IN OUT emp.sal%type) is
service_yrs number;
begin
service_yrs := trunc(months_between(sysdate,phiredate)/12);
if service_yrs > 25 then
psal := psal + (psal*.2);
elsif service_yrs > 23 then
psal := psal + (psal*.15);
else
psal := psal + (psal*.02);
end if;
End;
- declare for inout procedure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp
where deptno = 10
for update of sal;
begin
For mrec in cemp
loop
rahul_increment_emp(mrec.hiredate,mrec.sal);
update rahul_emp set sal = mrec.sal
where current of cemp;
end loop;
End;