CODEWITHSUNDEEP
oracle-databaseplsql
DCookie
DCookie

Reputation: 43523

PL/SQL Evaluation Order

Howdy. Consider the following:

SQL> DECLARE
  2     b1   BOOLEAN;
  3     b2   BOOLEAN;
  4     FUNCTION checkit RETURN BOOLEAN IS
  5     BEGIN
  6        dbms_output.put_line('inside checkit');
  7        RETURN TRUE;
  8     END checkit;
  9  
 10     PROCEDURE outp(n VARCHAR2, p BOOLEAN) IS
 11     BEGIN
 12        IF p THEN
 13           dbms_output.put_line(n||' is true');
 14        ELSE
 15           dbms_output.put_line(n||' is false');
 16        END IF;
 17     END;
 18  BEGIN
 19     b1 := TRUE OR checkit;
 20     outp('b1',b1);
 21     b2 := checkit OR TRUE;
 22     outp('b2',b2);
 23  END;
 24  /

b1 is true
inside checkit
b2 is true

PL/SQL procedure successfully completed

SQL>

Notice that the results of the OR statements are order dependent. If I place the function call first, then the function is executed regardless of the value of the other term. It appears that an OR statement is evaluated left to right until a TRUE is obtained, at which point processing stops and the result it TRUE.

My question is, is this something I can rely on? Or could this behavior change in future releases of PL/SQL? If it could change, is there a way to force the function to be evaluated that I can rely on (without creating another variable and using a separate assignment statement)?

Upvotes: 5

Views: 2600

Answers (6)

mutoss
mutoss

Reputation: 217

what exactly do you mean by "..and AND statements from right to left"?
this is from the oracle documentation=>

In the following example, notice that when valid has the value FALSE, the whole expression yields FALSE regardless of the value of done:

valid AND done

you can check the order in the following example:

DECLARE
b1 BOOLEAN;
b2 BOOLEAN;

FUNCTION checkit (v NUMBER)
RETURN BOOLEAN
IS
BEGIN
DBMS_OUTPUT.put_line ('inside checkit:' || v);
RETURN TRUE;
END checkit;

PROCEDURE outp (n VARCHAR2, p BOOLEAN)
IS
BEGIN
IF p
THEN
DBMS_OUTPUT.put_line (n || ' is true');
ELSE
DBMS_OUTPUT.put_line (n || ' is false');
END IF;
END;
BEGIN
b1 := checkit (1) AND checkit (2);
outp ('b1', b1);
b2 := checkit (3) AND checkit (4);
outp ('b2', b2);
END;

inside checkit:1
inside checkit:2
b1 is true
inside checkit:3
inside checkit:4
b2 is true

Upvotes: 0

Denis
Denis

Reputation:

It evaluates OR statements from left to right and AND statements from right to left. I did not find myself any documentation of it.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

In the documentation it states that short circuit evaluation applies to IF, CASE and CASE expressions: I'd bet that it also applies in the example that you quote but it's technically not documented that it does so. It might be worth raising a ticket with Oracle on this behaviour to get it confirmed.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

If it could change, is there a way to force the function to be evaluated that I can rely on (without creating another variable and using a separate assignment statement)?

If you require that the function must be evaulated even when it is logically superfluous to do so, that implies that it does something other than simply return TRUE or FALSE, e.g. perhaps it updates a table. It isn't considered good practice for PL/SQL functions to have such "side effects".

Upvotes: 1

JosephStyons
JosephStyons

Reputation: 58685

This is called "short-circuit evaluation", and it is the norm in most languages, including PL/SQL.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

Yes. PL/SQL performs short circuit evaluation of logical expressions from left to right.

Upvotes: 9

Related Questions