Oracle PL/SQL: how is AND evaluated?

Coders used in C#, Java etc. know there are two ways to evaluate a logical AND. In C# you can do either

if (test1) & (test2)
{
  // whatever
}

or

if (test1) && (test2)
{
  // whatever
}

The difference, of course, is that in the first case (&) BOTH test1 and test2 are evaluated. This doesn’t matter much if test1 and test2 are variables, but it matters a lot if they’re methods. This of the following example:

if (reserveItemsForOrder()) && (sendOrderToErp())
{
  // whatever
}

In this fictional case, && means that the order will be sent to the ERP system only if items can be reserved. If the single & is used, however, it will be sent anyway –even if not enough stock can be found.

This is well known in languages like C, C++, C#, Java etc. But how is AND evaluated in Oracle?

In short, it’s the same as &&. But for a more complete explanation, let’s read it from Oracle itself:

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error. Consider the following OR expression:

DECLARE

on_hand INTEGER;
on_order INTEGER;
BEGIN
..
IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN

END IF;
END;

When the value of on_hand is zero, the left operand yields TRUE, so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the OR operator, the right operand would cause a division by zero error. In any case, it is a poor programming practice to rely on short-circuit evaluation.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s