PL / SQL Evaluation Order

Question

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> 

Note that the results of the OR statements are order dependent. If you first place a function call, then the function is executed regardless of the meaning of another term. It looks like the OR instruction is evaluated from left to right until TRUE is received, after which the processing stops and the result is TRUE.

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

Answer 1

Yes. PL / SQL short-circuits logical expressions from left to right.

Answer 2

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

If you need a function to be verified even when it is logically redundant to do this, it means that it does something other than just return TRUE or FALSE, for example. possibly updating the table. It is not considered good practice for PL / SQL functions to have such "side effects."

Answer 3

The documentation states that the evaluation of a short circuit refers to the expressions IF, CASE, and CASE: I would argue that it also applies to the example you are quoting, but not technically documented, that it does. It might be worth collecting a ticket with Oracle for this behavior to confirm it.

Answer 4

This is called a “short circuit assessment,” and is the norm in most languages, including PL / SQL .

Answer 5

It computes the OR operators from left to right and the AND operators from right to left. I did not have documentation about this.

Answer 6

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

In the following example, note that when the valid value is FALSE, the whole expression gives FALSE regardless of the done value:

valid AND done

you can check the order in the following example:

DECLARATION
b1 BOOLEAN,
b2 BOOLEAN,

FUNCTION checkit (v NUMBER)
RETURN MORE

IS
TO BEGIN
DBMS_OUTPUT.put_line ('inside checkit:' || v);
RETURN TRUE
END checkit,

PROCEDURE outp (n VARCHAR2, p BOOLEAN)
IS
TO BEGIN
IF p
THEN
DBMS_OUTPUT.put_line (n || 'is true'); ELSE
DBMS_OUTPUT.put_line (n || 'is false'); END IF,
END
TO 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 true