Oracle PL/SQL: Check if record exists

Below are the examples to check if a record exists using PL/SQL in Oracle.

Check if record exists using the Count() function

The following Oracle PL/SQL block will use the count() function in implicit cursor to count the records for particular criteria. If the count is greater than 0 means, the records exist else not exist.

Declare
  n_count number;
Begin
  Select count(1) into n_count 
   from emp
   Where job = 'CLERK';

  if n_count > 0 then
    -- do something here if exists
    dbms_output.put_line('record exists.');
  else
    -- do something here if not exists
    dbms_output.put_line('record does not exists.');
  end if;
End;

Using Oracle PL/SQL Explicit Cursor to check if record exists

In the following example, it will check using the explicit cursor:

Declare
  Cursor c_emp
  is
  Select count(1) 
   from emp
  Where job = 'CLERK';

  n_count number;
Begin
  open c_emp;
  fetch c_emp into n_count;
  close c_emp;

  if n_count > 0 then
    -- do something here if exists
    dbms_output.put_line('record exists.');
  else
    -- do something here if not exists
    dbms_output.put_line('record does not exists.');
  end if;
End;

Using Oracle PL/SQL Implicit Cursor to check if record exists

The following example will check using the SQL query to get the specific value, and if the value exists then, it will continue to process; if not, it will be handled by the exception block.

Declare
  v_ename emp.ename%type;
Begin
  Select ename into v_ename
   from emp where empno = 7369;

  -- if the record exist you can continue with your code here 
  dbms_output.put_line('record exists.');

Exception
  When others then
  -- no_data_found error will raise if the record does not exists.
  dbms_output.put_line('record does not exists.'); 
End;