oracle 报错pls 00405,PLSQL: using subquery in if-statement error PLS-00405

问题

I'm getting error pls-00405 when I try to run this code:

BEGIN

IF :P10_KAART_CODE IN (SELECT KAART_CODE FROM CADEAUKAART) THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END;

There are some similar questions about this but couldn't find a solution for this simple code. Is there another way of writing this without facing a error?

回答1:

You may try a cursor, alternatively :

DECLARE

v_flag boolean := FALSE;

BEGIN

FOR c IN ( SELECT KAART_CODE FROM CADEAUKAART )

LOOP

IF :P10_KAART_CODE = c.KAART_CODE THEN

v_flag := TRUE;

EXIT;

END IF;

EXIT WHEN NO_DATA_FOUND;

END LOOP;

RETURN v_flag;

END;

By your way using a select statement is not allowed, you might list all the members for the returning values of KAART_CODE such as

IF :P10_KAART_CODE IN ('aAA','BBb','ccC'..) THEN

but which is not preferable and nice to list all matching values .

回答2:

PL/SQL doesn't support embedded SQL in if statements. So you'll need to rewrite your code like this:

create or replace function validate_kaart_code

(P10_KAART_CODE in CADEAUKAART.KAART_CODE%type)

return boolean

is

rv boolean;

l_code CADEAUKAART.KAART_CODE%type;

BEGIN

begin

SELECT KAART_CODE into l_code

FROM CADEAUKAART

where KAART_CODE =:P10_KAART_CODE

and rownum = 1 -- only necessary if KAART_CODE is not unique

;

rv := TRUE;

exception

when no_data_found then

rv := FALSE;

end;

RETURN rv;

END;

I have attempted to reconstruct your whole functionality from the snippet you posted. If this isn't what you intended and you can't convert it to fit your needs please provide more details.

回答3:

I'd move the condition to the query itself, and catch a NO_DATA_FOUND exception:

BEGIN

SELECT * FROM CADEAUKAART WHERE kaart_code = :P10_KAART_CODE;

RETURN TRUE;

EXCEPTION WHEN NO_DATA_FOUND THEN

RETURN FALSE;

END;

回答4:

One more way:

CREATE OR REPLACE FUNCTION IS_KAART_CODE_VALID(pinKAART_CODE IN CADEAUKAART.KAART_CODE%TYPE)

RETURN BOOLEAN

IS

nCount NUMBER;

BEGIN

SELECT COUNT(*)

INTO nCount

FROM CADEAUKAART

WHERE KAART_CODE = pinKAART_CODE ;

RETURN CASE

WHEN nCount > 0 THEN

TRUE

ELSE

FALSE

END;

END IS_KAART_CODE_VALID;

来源：https://stackoverflow.com/questions/52346437/plsql-using-subquery-in-if-statement-error-pls-00405