This shows you the differences between two versions of the page.
kurs:merge_in_procedure [2014/09/10 21:22] |
kurs:merge_in_procedure [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | declare | ||
+ | fn varchar2(100); | ||
+ | ln varchar2(100); | ||
+ | v_bonus number; | ||
+ | sal number; | ||
+ | begin | ||
+ | fn := 'Mark'; | ||
+ | ln := 'Hofstetter'; | ||
+ | sal := 144; | ||
+ | v_bonus := 111; | ||
+ | merge into copy_employees ce | ||
+ | using (select 1 from dual) | ||
+ | on (ce.first_name = :fn and ce.last_name = :ln) | ||
+ | when matched then update set | ||
+ | salary = :sal, bonus = :v_bonus | ||
+ | when not matched then | ||
+ | insert | ||
+ | (employee_id, first_name, last_name, salary, bonus, hire_date) | ||
+ | values | ||
+ | (employees_seq.nextval, | ||
+ | :fn, | ||
+ | :ln, | ||
+ | :sal, | ||
+ | :v_bonus, | ||
+ | sysdate); | ||
+ | end; | ||
+ | / | ||
+ | </code> |