statement to select rows from one or more sources for update or insertion into a table or view.
You can specify conditions to determine whether to update or insert into the target table or view.
where employee_id = i_employee_id; if sql%rowcount = 0 then -- no rows were updated, so the record does not exist insert into employees ( ... ); end if; end; When any SQL statement is executed in PLSQL, the SQL%ROWCOUNT variable will contain the number of rows affected (in this case updated) by the most recent query. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table.
In this case, if it contain zero, it means the update failed to find any rows to update and therefore the record needs to be inserted instead. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.
If the procedure is expected to mostly insert new records and rarely update existing ones, then use the following pattern (assuming there are unique constraints on the database to prevent duplicate employees being created): This code relies of the database to tell you the record already exists based on the integrity constraints on the table, which is much more efficient and less error prone that attempting to do it yourself.
If the more likely case is that existing records will be updated, the code below is better: begin update employees set ....
This statement is a convenient way to combine multiple operations.
It lets you avoid multiple clause to specify the target table or view you are updating or inserting into.
A frequent occurrence when writing database procedures is to handle a scenario where given a set of fields, for example a new employee record, update the existing employee record if it exists otherwise create it.
Often this problem is solved with a select statement and then an IF statement, eg: declare v_exists varchar2(1) := ' F'; begin begin select ' T' into v_exists from employee where employee_id := i_employee_id; exception when no_data_found then null; end; if v_exists = ' T' then update employee set ... When coding a procedure, you should try and get an idea of how the procedure will be used.
where employee_id = i_employee_id else insert ( ... In this case, the question to ask whether the procedure will mostly be used to update existing employee records, or insert new ones.
Maybe there will be no clear winner, but there often is.
Sounds pretty similar the problem outlined above, except that merge wants the new records to be in a staging table.
Luckily enough, we can fake a staging table using DUAL: create table employees ( employee_id integer not null, employee_name varchar2(100) not null); alter table employees add constraint employee_pk primary key (employee_id); create or replace procedure merge_employee( i_emp_id in integer, i_emp_name in varchar2 ) is begin merge into employees e using (select i_emp_id id, i_emp_name name from dual) s on (e.employee_id = s.id) when matched then update set employee_name = when not matched then insert (employee_id, employee_name) values (s.id, s.name); end; / With the merge statement, we now have a single more complex query instead of the 3 we started with, or the two of the refined approach.