title: oracle check duplicate value before insert into table
body: <p>I have two functions to check the duplicate value before insert into table, but I do not know which method is more efficient?</p>
  
  <p>First method:</p>
  
  <pre><code>select count(*)
        into ln_rec_cnt
        from ieexco_tbl t
       where t.ieexco_dept_code = p_dept
         and NVL(t.IEEXCO_SEQ, '|') = NVL(p_indx, '|')
      if ln_rec_cnt &gt; 0 then
        raise_application_error(-20001, 'Save coordinator record repeatedly');
      end if;
  </code></pre>
  
  <p>Second method:</p>
  
  <pre><code>insert into ieexco_tbl t
              (IEEXCO_DEPT_CODE,
               IEEXCO_SEQ,
               IEEXCO_NAME,
               IEEXCO_PHONE,
               IEEXCO_EMAIL,
               IEEXCO_CREATE_DATE,
               IEEXCO_OPR_PIDM)
   select 
               p_dept,
               p_indx(i),
               p_name(i),
               p_tel(i),
               p_email(i),
               sysdate,
               p_opr_pidm 
  from dual 
  where not exists(SELECT 1 
                   FROM ieexco_tbl 
                   WHERE IEEXCO_DEPT_CODE = p_dept
                   and NVL(IEEXCO_SEQ,'|')= NVL(p_indx(i),'|'));
  </code></pre>