Skip to content

G-8120: Never check existence of a row to decide whether to create it or not.

Major

Efficiency, Reliability

Reason

The result of an existence check is a snapshot of the current situation. You never know whether in the time between the check and the (insert) action someone else has decided to create a row with the values you checked. Therefore, you should only rely on constraints when it comes to prevention of duplicate records.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create or replace package body department_api is
   procedure ins(in_r_department in departments%rowtype) is
      l_count pls_integer;
   begin
      select count(*)
        into l_count
        from departments
       where department_id = in_r_department.department_id;

      if l_count = 0 then
         insert into departments
         values in_r_department;
      end if;
   end ins;
end department_api;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create or replace package body department_api is
   procedure ins(in_r_department in departments%rowtype) is
   begin
      insert into departments
      values in_r_department;
   exception
      when dup_val_on_index then
         null; -- handle exception
   end ins;
end department_api;
/