IF NOT EXISTS In Function PLSQL

oracle, plsql

Solution

`Exists` condition can be used only in SQL statement, it cannot be used directly in PL/SQL. There are several options:

Using `case` expression with `exists` condition inside a `select` statement:

SQL> declare
  2    l_exists number(1);
  3  begin
  4    select case
  5             when exists(select 1
  6                           from employees
  7                          where department_id = 1)
  8             then 1
  9             else 0
 10           end into l_exists
 11      from dual;
 12  
 13     if (l_exists = 1)
 14     then
 15       dbms_output.put_line('exists');
 16     else
 17       dbms_output.put_line(q'[doesn't exist]');
 18     end if;
 19  end;
 20  /

 doesn't exist

 PL/SQL procedure successfully completed

Or (`rownum` is needed to guarantee that the only one record will be returned if there are several records meet matching condition):

SQL> declare
  2    l_exists number;
  3  begin
  4  
  5    select 1
  6      into l_exists
  7      from employees
  8     where department_id = 100
  9       and rownum = 1;
 10  
 11     dbms_output.put_line('exists');
 12  
 13  exception
 14    when no_data_found
 15    then dbms_output.put_line(q'[doesn't exist]');
 16  end;
 17  /

 exists

 PL/SQL procedure successfully completed

Problem

I have a function which has three If/Then statements before opening a cursor. The If/Then statements check validity prior to opening the cursor. I would like to add one more If/Then validity check, however, it is a bit more complicated than the others. Below is a sample, and I have block commented what I would like to add: ``` begin if not procedure.validation_function (<variable>, <condition>=TRUE) then return variable2; end if; /* if not exists ( SELECT 'x' FROM table1 WHERE table1_id = variable1_id AND trunc(sysdate) < trunc(table1_date + 60) ) then return variable2; end if; */ open cursor(<argument>); fetch cursor into <variable>; close cursor; return <variable>; end; ``` My problem is that I come from a T-SQL world, and I am finding in PL/SQL the if not exists command does not work. Is there a way I can, from within the function, have an If NO_DATA_FOUND statement where I utilize SELECT? Is there a way to nest another function within that, so I can: ``` begin SELECT .... FROM .... WHERE .... if NO_DATA_FOUND then return variable2; end if; end; ```

Original source