思路是寫一個函數,先按條件查詢數據,如果查詢到數據則更新,如果沒有查詢到數據則插入:
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 --數據不存在,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 --數據存在,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;注意to_date(to_char(statdate, 'yyyy/mm/dd'), 'yyyy/mm/dd')這個寫法,如果寫成to_date(statdate, 'yyyy/mm/dd'),根據NLS不同,可能導致數據出錯。具體請看這裡
另外oracle提供了merge into可以實現此功能,理論上講比上面的效率會高,但是沒做試驗。merge into有個缺點就是在10g以下版本的oracle中會出現問題,導致比較嚴重的後果(據說會把所有的數據都更新,而9i又不支持在update後加條件),所以我沒有采用這個方法。
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);另外還有個思路,直接update,執行後會返回受影響的行數,如果行數為0,表示沒有符合條件的數據,後面執行insert;如果行數大於0,表示有符合條件的行數且update執行成功。