

<code>
-- kurs vom 11.05.2016
CREATE OR REPLACE PROCEDURE P_UPSERT_EMP_COPY 
  (p_first_name       emp_copy.first_name%type, 
   p_last_name        emp_copy.last_name%type,
   p_email            emp_copy.email%type,
   p_job_title        jobs.job_title%type,
   p_department_name  departments.department_name%type,
   p_salary           emp_copy.salary%type,
   p_hire_date       
        emp_copy.hire_date%type default sysdate,
   p_employee_id  out emp_copy.employee_id%type
   )
AS 
/*
  lookup 
    + department_id
    + job_id 
  in eigenen blöcken, fehler bei nichtexistenz 
  mit dbms_output reporten 
  
  ein employee ist eindeutig durch first_/last_name 
  identifiziert, existiert diese Kombination also, dann 
  update, wenn nicht insert
*/
  v_err varchar2(100) := null;
  v_department_id departments.department_id%type;
  v_job_id jobs.job_id%type;
BEGIN
  begin 
    select department_id into v_department_id 
      from departments 
     where department_name = p_department_name;
  exception
     when no_data_found then
       v_err := v_err || '[no such department]';
  end;      
    
  begin 
    select job_id into v_job_id 
      from jobs
     where job_title = p_job_title;
   exception
     when no_data_found then
       v_err := v_err || '[no such job]';
  end;      
   
  if v_err is not null then
    dbms_output.put_line(v_err);
    return;
  end if;
  
merge into emp_copy ec 
using (select 1 from dual) 
on (p_first_name = ec.first_name and 
    p_last_name  = ec.last_name)
when matched then update
  set email = p_email,
      job_id = v_job_id,
      department_id = v_department_id,
      salary = p_salary
when not matched then
  insert  (
    employee_id,
    first_name,
    last_name,
    email,
    job_id,
    department_id,
    salary,
    hire_date
) values (
    employees_seq.nextval,
    p_first_name,
    p_last_name,
    p_email,
    v_job_id,
    v_department_id,
    p_salary,
    p_hire_date
    );
 
 select employee_id into p_employee_id
   from emp_copy where 
        last_name  = p_last_name and
        first_name = p_first_name;


/* 
v_department_id := 
  get_department_id_per_name(p_department_name);
*/  
END P_UPSERT_EMP_COPY;
</code>

<code>
--------------------------------------------------------
--  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;

/

</code>