Oracle实现数据不存在则插入，数据存在则更新（insert or update）

0987

  思路是写一个函数，先按条件查询数据，如果查询到数据则更新，如果没有查询到数据则插入：


    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执行成功。