--Developing Oracle Applications with Developer/2000
--@C:\DEV2000\SQL\SAL_MGT.SQL

spool C:\DEV2000\SQL\SAL_MGT.TXT

Prompt Developing Oracle Applications with Developer/2000
Prompt Creating Packages for...

select substr (user,1,20) User_Id,
sysdate Sys_Date,
substr (to_char(sysdate, 'HH:MIAM'),1,10) Sys_Time from dual;

Prompt Creating Package Sal_Mgt...

create or replace package sal_mgt as
  procedure sal_update;
  function sal_incr (emp_num number) return number;
end sal_mgt;

/

Prompt Creating Package Body Sal_Mgt...

create or replace package body sal_mgt as
procedure sal_update is
  empnum    number;
  new_sal   number;
  cursor new_salaries is
     select empno
     from emp;
begin
  open new_salaries;
  loop
     fetch new_salaries into empnum;
     if new_salaries%NOTFOUND then
        exit;
     end if;
     new_sal := sal_incr(empnum);
     update emp
     set sal = new_sal
     where empno = empnum;
  end loop;
  close new_salaries;
end;
function sal_incr (emp_num number) return number is
  dept_num  number;
  dept_incr number;
  emp_incr  number;
  incr      number;
  new_sal   number;
  sal       number;
begin
  select deptno, sal, increase_pct
  into dept_num, sal, emp_incr
  from emp
  where empno = emp_num;

  select increase_pct into dept_incr
  from dept
  where deptno = dept_num;

  incr := dept_incr * (emp_incr/100)/100;
  new_sal := sal + (sal*incr);
  return (new_sal);
end;
end sal_mgt;
/

Prompt Done Creating Package Sal_Mgt.

spool off;
