IF EXISTS 条件不适用于 PLSQL

本文介绍了IF EXISTS 条件不适用于 PLSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图在条件为真时打印文本.选择代码完全正常.当我只运行选择代码时，它显示 403 值.但是当条件存在时我必须打印一些文本.以下代码有什么问题.

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.

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;

错误报告如下:

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: %s" *Cause: Usually a PL/SQL compilation error. *Action:

推荐答案

IF EXISTS() 在语义上不正确.EXISTS 条件只能在 SQL 语句中使用.因此，您可以按如下方式重写 pl/sql 块:

IF EXISTS() is semantically incorrect. EXISTS condition can be used only inside a SQL statement. So you might rewrite your pl/sql block as follows:

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;

或者你可以简单地使用count函数来确定查询返回的行数，以及rownum=1谓词——你只需要知道一条记录是否存在:

Or you can simply use count function do determine the number of rows returned by the query, and rownum=1 predicate - you only need to know if a record exists:

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;