This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:procedure_upsert_employee [2014/05/06 12:37] mh created |
kurs:procedure_upsert_employee [2016/05/11 14:34] (current) 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> | ||
-------------------------------------------------------- | -------------------------------------------------------- |