dokuwiki

merge into emp_merge m
using 
(select  1 employee_id, -- sequence not allowed here
        'Hofstetter' last_name,  
        'IT_PROG' job_id,
        10001 salary,
        sysdate hire_date
        from dual) e
on (m.last_name = e.last_name) 
when matched then 
  update set salary = e.salary
when not matched then 
insert (
  employee_id,
  last_name,
  job_id,
  salary,
  hire_date
  ) values (
  seq.nextval,
  e.last_name,
  e.job_id,
  e.salary,
  e.hire_date
  )

merge into emp_copy c
using (select 1 from dual)
-- using (select 'bla' fn, 'bli' ln from dual) e
-- using ( select first_name, last_name from emp_copy )
on (:b_last_name = c.last_name and :b_first_name = c.first_name)
 when matched then
   update set salary        = :b_salary,             
              email         = :b_email,
              hire_date     = :b_hire_date,
              job_id        = :b_job_id ,
              department_id = :b_department_id
when not matched THEN
  insert (
   employee_id,
   first_name,
   last_name,
   email,
   hire_date,
   job_id,
   department_id,
   salary
  ) values (
   employees_seq.nextval,
   :b_first_name,
   :b_last_name,
   :b_email,
   :b_hire_date,
   :b_job_id,
   :b_department_id,
   :b_salary
  )

merge into accountants
using (select 1 from dual)
on (last_name = :ln and first_name = :fn)
when matched then update set salary = :salary
when not matched then 
  insert 
    (first_name,
    last_name,
    salary)
    values (
    :fn,
    :ln,
    :salary)