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 preventioin 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 department%rowtype) is
      l_count pls_integer;
   begin
      select count(*)
        into l_count
        from department
       where department_id = in_r_department.department_id;

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

Example (good)

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