Differences

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

--- kurs:procedure_upsert_employee [2014/09/10 21:22]
127.0.0.1 external edit
+++ 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>
 --------------------------------------------------------

dokuwiki

User Tools

Site Tools

Differences

Page Tools