Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IF NOT EXISTS In Function PLSQL

Tags:

oracle

plsql

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;
like image 489
Phoenix Avatar asked Nov 21 '12 22:11

Phoenix


People also ask

What does <> mean in Plsql?

It means 'not equal to'.

Can we use if exists in Oracle?

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.

Which is faster not in or not exists in Oracle?

[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.

How does not exists work in Oracle?

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.


1 Answers

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
    
like image 60
Nick Krasnov Avatar answered Oct 11 '22 09:10

Nick Krasnov