User Tools

Site Tools


kurs:procedure_upsert_employee

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
kurs:procedure_upsert_employee [2014/09/10 21:22]
127.0.0.1 external edit
kurs:procedure_upsert_employee [2016/05/11 14:34]
mh
Line 1: Line 1:
 +
 +
 +<​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>​ <​code>​
 -------------------------------------------------------- --------------------------------------------------------
kurs/procedure_upsert_employee.txt · Last modified: 2016/05/11 14:34 by mh