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)

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