oracle插入数据前判断是否存在

插入数据前如果不存在才插入数据,对于存在数据的忽略处理,当然也可以其他处理

第一种:

declare

  num number;
begin
  select nvl(count(1), 0) into num from hts_employee where ID = 522;
  if num = 0 then
    insert into hts_employee
      (ID,
       EMPCODE,
       EMPNAME,
       NSPELL,
       EMPCLASS,
       DEPTID,
       SEX,
       PEOPLE,
       BIRTHDAY,
       CARDNO,
       PAYCARDNO,
       JXFACTOR,
       EMPSTATUS,
       FFBS,
       FFBL,
       OPID,
       OPTIME,
       PJJBS,
       JOBSUBSIDY,
       STOPPED,
       PHOTO,
       PHONE,
       TEL,
       ADDRESS,
       GZCODE,
       KHCODE,
       RESPONSE,
       SIGNNAME,
       TECHNAME,
       DEGREE)
    values
      (522,
       '0105',
       '高春东',
       'GCD',
       3172,
       3204,
       1,
       '849',
       to_date('1899-12-30', 'yyyy-mm-dd'),
       null,
       null,
       1.00,
       847,
       null,
       null,
       622,
       to_date('28-03-2013 08:56:39', 'dd-mm-yyyy hh24:mi:ss'),
       null,
       null,
       0,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       '本科');
  end if;
end;

第二种 用Merge into方法 

实例:

merge into HU_REST.HTS_SPONSOR t1
                                    using (select :ID as ID, :NAME as NAME, :NSPELL as NSPELL , :TEL as TEL, :ADDRESS as ADDRESS, :CARDNO as CARDNO, :XS as XS, :STOPPED as STOPPED
                                             from dual) t2
                                    on (t1.ID = t2.ID)--不要忘记带括号


                                    when not matched then
                                      insert --与正常的插入语句有些不同
                                        (t1.ID, t1.NAME, t1.NSPELL, t1.TEL, t1.ADDRESS, t1.CARDNO, t1.XS, t1.STOPPED)
                                      values
                                        (t2.ID, t2.NAME, t2.NSPELL, t2.TEL, t2.ADDRESS, t2.CARDNO, t2.XS, t2.STOPPED)