If no data is found, the data will be updated. If no data is found, the data will be inserted:

create or replace function fn_merge_index(statdate      in date,
                                          cpid          in varchar2,
                                          indextypecode in number,
                                          indexitemcode in number,
                                          indexdata     in varchar2)
  return number is
  numb number;
begin
  select count(*)
    into numb
    from cp_index_statistics_rec
   where stat_date = to_date(to_char(statdate, 'yyyy/mm/dd'), 'yyyy/mm/dd')
     and cp_id = cpid
     and index_type_code = indextypecode
     and index_item_code = indexitemcode;
  if numb = 0 then
    -- Data not present, insert
    begin
      insert into cp_index_statistics_rec
        (stat_id,
         stat_date,
         diagnosis,
         cp_id,
         is_validate,
         index_type_code,
         index_item_code,
         stat_data,
         stat_create_date,
         cp_name)
      values
        (cp_index_statistics_rec_seq.nextval,
         to_date(to_char(statdate, 'yyyy/mm/dd'), 'yyyy/mm/dd'),
         ' ',
         cpid,
         1,
         indextypecode,
         indexitemcode,
         indexdata,
         (select sysdate from dual),
         (select cp_name from cp_templet_master where cp_id = cpid));
      commit;
    end;
  else
    -- Data exists, update
    begin
      update cp_index_statistics_rec
         set is_validate      = 1,
             stat_data        = indexdata,
             stat_create_date =
             (select sysdate from dual)
       where stat_date = to_date(to_char(statdate, 'yyyy/mm/dd'), 'yyyy/mm/dd')
         and cp_id = cpid
         and index_type_code = indextypecode
         and index_item_code = indexitemcode;
      commit;
    end;
  end if;
  return numb;
end fn_merge_index;
Copy the code

To_date (to_char(statdate, ‘YYYY /mm/dd’),’ YYYY /mm/ DD ‘); to_date(statdate, ‘YYYY /mm/ DD ‘) may cause data errors depending on NLS. See here for details

In addition, Oracle provides merge into to implement this function, which is theoretically more efficient than the above, but we did not test it. Merge into has a disadvantage that it can cause problems in oracle versions below 10G, which can cause serious consequences (it is said that all data will be updated, and 9I does not support conditional after update), so I did not use this method.

Merge into

merge into bonuses d 
using (select employee_id, salary, department_id from employees 
where department_id = 80) s 
on (d.employee_id = s.employee_id) 
when matched then update set d.bonus = d.bonus + s.salary*.01 
when not matched then insert (d.employee_id, d.bonus) 
values (s.employee_id, s.salary*0.01); 
Copy the code

If the number of affected rows is 0, the number of affected rows is 0. If the number of affected rows is 0, the number of affected rows is 0. If the number of rows is greater than 0, the number of qualified rows exists and the UPDATE is successfully executed.