Insert if data does not exist in Oracle, insert or update if data exists, and oracleinsert

Insert if data does not exist in Oracle, insert or update if data exists, and oracleinsert

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.

If the oracle table exists, it is not updated. If it does not exist, how to write the insert statement?

A statement cannot be written.
The general idea is to first judge whether there is 2011 data in the table, and then insert it again.

If a primary key exists in oracle, it is updated. Otherwise, how can I add a new record (insert )?

This is easy, just use merge. The specific usage is roughly as follows,
Merge into target table
Using (select ......) ----- Here is the data to be updated or inserted. It is generally constructed using select.
On ...... ------ Here is the Association condition for updating the judgment
When match then update table name set field name = ...... ----- If it can match (that is, there is data), update
WHEN NOT MATCHED THEN
INSERT (Field List)
Values (Value List)

In general, for more detailed usage, You can further use Baidu MERGE. There is a lot of information and I will not go into details here.