If Oracle implements data that does not exist, data is inserted. If data exists, data is updated (insertorupdate)
The idea is to write a function that first queries data based on conditions. If data is queried, it is updated. If no data is queried, It is 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 does not exist, insert begin insert into values (stat_id, stat_date, diagnosis, cp_id, is_validate, index_item_code, stat_data, stat_create_date, cp_name) values (values, 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 if; return numb; end fn_merge_index;
Note that to_date (to_char (statdate, 'yyyy/mm/dd'), 'yyyy/mm/dd') is written. If it is written as to_date (statdate, 'yyyy/mm/dd'), which may cause data errors according to NLS. For details, see here
In addition, oracle provides merge into to implement this function, which is theoretically more efficient than above, but has not been tested. The disadvantage of merge into is that problems may occur in oracle versions earlier than 10 Gb, resulting in serious consequences (it is said that all data will be updated, 9i does not support adding conditions after update), so I didn't use this method.
Usage of merge:
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);
In addition, there is another idea: directly update. After execution, the affected number of rows will be returned. If the number of rows is 0, it indicates that no data meets the conditions, and insert will be executed later. If the number of rows is greater than 0, indicates that the number of rows meeting the condition exists and the update operation is successful.