SQL IF EXISTS 条件不适用于 PLSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13217600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IF EXISTS condition not working with PLSQL
提问by nirmalgyanwali
I am trying to print the TEXT when condition is TRUE. The select code is perfectly working fine. It's showing 403 value when i only run select code. But I have to print some text when condition exists. What's the problem with following code.
当条件为 TRUE 时，我试图打印 TEXT。选择代码完全正常。当我只运行选择代码时，它显示 403 值。但是当条件存在时我必须打印一些文本。以下代码有什么问题。
BEGIN
IF EXISTS(
SELECT CE.S_REGNO FROM
COURSEOFFERING CO
JOIN CO_ENROLMENT CE
ON CE.CO_ID = CO.CO_ID
WHERE CE.S_REGNO=403 AND CE.COE_COMPLETIONSTATUS = 'C' AND CO.C_ID = 803
)
THEN
DBMS_OUTPUT.put_line('YES YOU CAN');
END;
Here is the error report:
这是错误报告：
Error report:
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:
) , with group having intersect minus start union where
connect
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
回答by Nick Krasnov
IF EXISTS()is semantically incorrect. EXISTScondition can be used only inside a SQL statement. So you might rewrite your pl/sql block as follows:
IF EXISTS()在语义上是不正确的。EXISTS条件只能在 SQL 语句中使用。因此，您可以按如下方式重写 pl/sql 块：
declare
l_exst number(1);
begin
select case
when exists(select ce.s_regno
from courseoffering co
join co_enrolment ce
on ce.co_id = co.co_id
where ce.s_regno=403
and ce.coe_completionstatus = 'C'
and ce.c_id = 803
and rownum = 1
)
then 1
else 0
end into l_exst
from dual;
if l_exst = 1
then
DBMS_OUTPUT.put_line('YES YOU CAN');
else
DBMS_OUTPUT.put_line('YOU CANNOT');
end if;
end;
Or you can simply use countfunction do determine the number of rows returned by the query, and rownum=1predicate - you only need to know if a record exists:
或者您可以简单地使用count函数来确定查询返回的行数，并使用rownum=1谓词 - 您只需要知道记录是否存在：
declare
l_exst number;
begin
select count(*)
into l_exst
from courseoffering co
join co_enrolment ce
on ce.co_id = co.co_id
where ce.s_regno=403
and ce.coe_completionstatus = 'C'
and ce.c_id = 803
and rownum = 1;
if l_exst = 0
then
DBMS_OUTPUT.put_line('YOU CANNOT');
else
DBMS_OUTPUT.put_line('YES YOU CAN');
end if;
end;
回答by HiltoN
Unfortunately PL/SQL doesn't have IF EXISTSoperator like SQL Server. But you can do something like this:
不幸的是，PL/SQL 没有IF EXISTS像 SQL Server这样的运算符。但是你可以做这样的事情：
begin
for x in ( select count(*) cnt
from dual
where exists (
select 1 from courseoffering co
join co_enrolment ce on ce.co_id = co.co_id
where ce.s_regno = 403
and ce.coe_completionstatus = 'C'
and co.c_id = 803 ) )
loop
if ( x.cnt = 1 )
then
dbms_output.put_line('exists');
else
dbms_output.put_line('does not exist');
end if;
end loop;
end;
/
