IF NOT EXISTS 在函数 PLSQL 中

2021-12-24 00:00:00 oracle plsql

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

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 有效性检查,但是,它比其他的要复杂一些.下面是一个示例,我已经阻止评论了我想添加的内容:

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;

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

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;

推荐答案

Exists 条件只能在 SQL 语句中使用,不能在 PL/SQL 中直接使用.有几个选项:

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

  • select 语句中使用case 表达式和exists 条件:

  • 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,保证多条记录满足匹配条件,只返回一条记录):

  • 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
    

  • 相关文章