- packages creation
1
2
3
4
5
create or replace package rahul_salary
as
procedure rahul_process_increment;
function rahul_fn_increment(phiredate rahul_emp.hiredate%type , psal rahul_emp.sal%type) return number;
end;
- package body for above declared package
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
create or replace package body rahul_salary
as
procedure rahul_process_increment
is
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp
for update of sal;
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;
update rahul_emp set sal = mrec.sal
where current of cemp;
end loop;
End;
function rahul_fn_increment
(phiredate rahul_emp.hiredate%type , psal rahul_emp.sal%type)
return number
is
service_yrs number;
revised_sal rahul_emp.sal%type ;
begin
service_yrs := trunc(months_between(sysdate,phiredate)/12);
if service_yrs > 25 then
revised_sal := psal + (psal*.2);
elsif service_yrs > 23 then
revised_sal := psal + (psal*.15);
else
revised_sal := psal + (psal*.02);
end if;
return revised_sal;
End;
End;
- calling the procedure of above declared package
1
2
3
4
5
begin
rahul_salary.rahul_process_increment;
end;
- calling the function of above declared package
1
2
3
4
5
begin
dbms_output.put_line(rahul_salary.rahul_fn_increment('1-JAN-2010',4500));
end;
- drop package
1
2
3
# drop package <package name>
drop package body <package name>