如果函数PLSQL中不存在

本文介绍了如果函数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中发现如果不存在命令不起作用.在函数中，有没有一种方法可以让我利用SELECT的If NO_DATA_FOUND语句呢?

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语句中使用具有exists条件的case表达式:

• 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

或(需要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