Oracle implementation data does not exist then inserted, data exists update (INSERT or UPDATE)

The idea is to write a function to query the data conditionally, if the query to the data is updated, if no query to the data is inserted:

Create or Replace function Fn_merge_index (statdate in date, cpid in VARCHAR2, Indextypecode in number, Inde Xitemcode in number, indexdata in VARCHAR2) return number is numb Number;be Gin Select COUNT (*) into numb from cp_index_statistics_rec where stat_date = To_date (To_char (statdate, ' yyyy/mm/d  d '), ' 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 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_c Har (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;

Note that the To_date (To_char (statdate, ' yyyy/mm/dd '), ' Yyyy/mm/dd ') is written in To_date (Statdate, ' yyyy/mm/dd '), which, depending on the NLS, can result in data errors. Please see here for details.

In addition, Oracle provides the merge into to achieve this function, which is theoretically more efficient than the above, but not tested. The downside of merge into is that there is a problem with Oracle in the following version of 10g, which leads to more serious consequences (it is said that all data will be updated, and 9i does not support adding conditions after the update), so I did not use this method.

Merge into usage:

There is also a way of thinking, direct update, after execution will return the number of rows affected, if the number of rows is 0, indicating that there is no qualifying data, followed by insert, if the number of rows is greater than 0, indicates that there is a qualifying number of rows and update execution succeeds.

Oracle Implementation data is not present, inserted, data exists update (INSERT or update)