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)

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)

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;
/