oracle IF NOT EXISTS 在函数 PLSQL 中

IF NOT EXISTS In Function PLSQL

提问by Phoenix

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.

我有一个函数，它在打开游标之前具有三个 If/Then 语句。If/Then 语句在打开游标之前检查有效性。

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:

我想再添加一个 If/Then 有效性检查，但是，它比其他的要复杂一些。下面是一个示例，我已经阻止评论了我想添加的内容：

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 existscommand does not work. Is there a way I can, from within the function, have an If NO_DATA_FOUND statement where I utilize SELECT?

我的问题是我来自 T-SQL 世界，我发现在 PL/SQL 中if not exists命令不起作用。有没有一种方法可以从函数内部使用 SELECT 的 If NO_DATA_FOUND 语句？

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;

回答by Nick Krasnov

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

Existscondition 只能在SQL 语句中使用，不能在PL/SQL 中直接使用。有几种选择：

• Using caseexpression with existscondition inside a selectstatement:

  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 (rownumis 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
  

• 在语句中使用case带有exists条件的select表达式：

  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
  

• 或者（rownum需要保证如果有多条记录满足匹配条件，只会返回一条记录）：

  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