dokuwiki

This is an old revision of the document!

---

--------------------------------------------------------
--  DDL for Procedure INSERT_EMPLOYEE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "HR"."INSERT_EMPLOYEE" 
(
  P_FIRST_NAME IN VARCHAR2  
, P_LAST_NAME IN VARCHAR2  
, P_DEPARTMENT_NAME IN departments.department_name%type
, P_SALARY IN NUMBER
, p_email in varchar2
, p_phone_number in varchar2
, p_hire_date in date
, p_job_title in varchar2
, p_commission_pct in number
, p_manager_id in number
) AS 

v_employee_id number;
v_department_id departments.department_id%type;
v_manager_id number;
v_job_id jobs.job_id%type;
v_max_salary number;

BEGIN
  
  begin
  select department_id into v_department_id
    from departments
   where department_name = P_DEPARTMENT_NAME;
  exception
  when no_data_found then
    raise_application_error(-20001, 'no department found');  
  end;
  
  begin  
  select job_id into v_job_id 
    from jobs
   where job_title = p_job_title;
  exception
  when no_data_found then
    raise_application_error(-20002, 'no such job_title found');  
  end;
   
  v_manager_id := 101;  
    
  select max(salary) into v_max_salary from employees;
  if p_salary > v_max_salary then 
    raise_application_error(-20003, 'too much money exception');  
  end if;    
    
  merge into employees e
  using (select 1 from dual) 
  on (first_name = p_first_name and
      last_name  = p_last_name)
  when matched then 
    update set    
    email          = p_email,
    phone_number   = p_phone_number,
    hire_date      = p_hire_date,
    job_id         = v_job_id,
    salary         = p_salary,
    commission_pct = p_commission_pct,
    manager_id     = v_manager_id,
    department_id  = v_department_id 
  when not matched then
    insert (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
  ) values (
    employees_seq.nextval,
    p_first_name,
    p_last_name,
    p_email,
    p_phone_number,
    p_hire_date,
    v_job_id,
    p_salary,
    p_commission_pct,
    v_manager_id,
    v_department_id
  );
  
END INSERT_EMPLOYEE;

/