dokuwiki

-- Start of DDL Script for Package HR.CHG_EMP
-- Generated 6-Nov-2008 9:19:57 from HR@ORCL

CREATE OR REPLACE 
PACKAGE chg_emp
  IS
    p_tax    number;
    dol2eur  number := 1.2822;
    anrede   varchar2(20) := 'Hallo';
    anfang   date   := to_date('20000101', 'yyyymmdd');
    avg_sal  number;
    

   PROCEDURE get_emp
     ( emp_id IN number);

   PROCEDURE get_emp;

/*   procedure add_emp(
     p_employee_id number,
     p_first_name  bonus.first_name%type,
     p_last_name   bonus.last_name%type);*/
    
   procedure add_emp(
     p_first_name  bonus.first_name%type,
     p_last_name   bonus.last_name%type);
   
   function change_tax(new_tax in number)
   return number;

   function get_tax
   return number;
 

END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY chg_emp
IS
   tax      number := 0.2;
   
   function get_p_number(parameter varchar2)
   return number  
   is
    v_number number;
   begin
   select p_number into v_number from parameter
     where p_name = parameter;
   return v_number;
   end;
   
   function change_tax (new_tax number)
   return number
   is 
   begin
     tax := new_tax;
     return new_tax;
   end;
   
   function get_tax
   return number
   is 
   begin
     return tax;
   end;
    
   PROCEDURE get_emp
     ( emp_id IN number)
    IS
   v_first_name bonus.first_name%type;
   v_last_name   bonus.last_name%type;
   begin
     select first_name, last_name into v_first_name, v_last_name 
        from bonus where employee_id = emp_id;
     
     dbms_output.put_line(v_first_name||' '||v_last_name);
   END;

   PROCEDURE get_emp
    IS
   begin
     for r_emp in (select employee_id from bonus) loop
       get_emp(r_emp.employee_id);
     end loop;
   END;
   
   procedure add_emp(
     p_employee_id number,
     p_first_name  bonus.first_name%type,
     p_last_name   bonus.last_name%type)
   is 
   begin
     insert into bonus (
       employee_id,
       first_name,
       last_name
       ) values (
       p_employee_id,
       p_first_name,
       p_last_name);
    end;   
    
   procedure add_emp(
     p_first_name  bonus.first_name%type,
     p_last_name   bonus.last_name%type)
   is 
     v_seq number;
   begin
     select employees_seq.nextval into v_seq from dual;
     add_emp(v_seq, p_first_name, p_last_name);
    end;   

 begin 
     p_tax := get_p_number('tax');
   -- Enter further code below as specified in the Package spec.
END;
/


-- End of DDL Script for Package HR.CHG_EMP