dokuwiki

PACKAGE PCK_SALARY
  IS
--
-- To modify this template, edit file PKGSPEC.TXT in TEMPLATE 
-- directory of SQL Navigator
--
-- Purpose: Briefly explain the functionality of the package
--
-- MODIFICATION HISTORY
-- Person      Date    Comments
-- ---------   ------  ------------------------------------------       
   -- Enter package declarations as shown below

  tax_percent number;
  euro2dollar number;
  
  called_init boolean := false;

  PROCEDURE init;

  PROCEDURE calc_tax;
 
  PROCEDURE calc_tax(v_employee_id IN number);
  
   FUNCTION tax
     ( salary IN number)
     RETURN  number;

END; -- Package spec

CREATE OR REPLACE PACKAGE BODY PCK_SALARY
IS
--
-- To modify this template, edit file PKGBODY.TXT in TEMPLATE 
-- directory of SQL Navigator
--
-- Purpose: Briefly explain the functionality of the package body
--
-- MODIFICATION HISTORY
-- Person      Date    Comments
-- ---------   ------  ------------------------------------------      
   -- Enter procedure, function bodies as shown below

   PROCEDURE init
    IS
   BEGIN
    select isnumeric(par_value) into tax_percent from parameter
      where par_key='tax_percent';
    called_init := true;
   END;

   PROCEDURE calc_tax(v_employee_id IN number)
     is
     BEGIN
     update emp_bonus
       set tax = tax(salary)
       where emp_id = v_employee_id;
   end;


   PROCEDURE calc_tax
    IS
    cursor cur_emp_bonus is
      select * from emp_bonus
      for update of tax;
      
    rec_emp_bonus emp_bonus%rowtype;
   BEGIN
    
    for rec_emp_bonus in cur_emp_bonus loop
      calc_tax(rec_emp_bonus.emp_id);
    end loop;
   -- dbms_output.put_line(isnumeric(tax_percent));
   END;
   
   
  FUNCTION tax
     ( salary IN number)
     RETURN  number
  is
  begin
   if called_init=false then
     init;
   end if;

  return salary*isnumeric(tax_percent);
  end;

   -- Enter further code below as specified in the Package spec.
END;
/