IF NOT EXISTING IN PLSQL Function

Question

I have a function that has three If / Then statements before opening the cursor. If / Then statements validate before the cursor opens.

I would like to add another If / Then check, however, this is a little more complicated than the others. Below is a sample, and I have a block that 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 came from the T-SQL world and I am in PL / SQL, if the command does not exist, it does not work. Is there a way that I can, from within a function, have an If NO_DATA_FOUND statement where I use SELECT?

Is there a way to insert another function into it, so I can:

 begin SELECT .... FROM .... WHERE .... if NO_DATA_FOUND then return variable2; end if; end; 

Answer 1

Exists condition can only be used in SQL statements; it cannot be used directly in PL / SQL. There are several options:

• Using a case expression with an Exists clause 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 required to ensure that only one record will be returned if multiple records satisfy the 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