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;
It means 'not equal to'.
The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
[NOT] IN and [NOT] EXISTS operators are processed differently. [NOT] IN is processed more like a join whereas [NOT] EXISTS is processed more like a loop with IF condition.
Description An EXISTS condition tests for existence of rows in a subquery. If at least one row returns, it will evaluate as TRUE. NOT EXISTS evaluates as TRUE if 0 rows are returned and can be used to validate the absence of a condition.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With