G-8120: Never check existence of a row to decide whether to create it or not.
Critical
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(*) -- NOSONAR: a violation of G-8110 is a prerequisite for G-8120
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;
/
|