PL/SQL User's Guide and Reference

Contents Index Home Previous Next

Expressions and Comparisons

Expressions are constructed using operands and operators. An operand is a variable, constant, literal, or function call that contributes a value to an expression. An example of a simple arithmetic expression follows:

-X / 2 + 3

Unary operators such as the negation operator (-) operate on one operand; binary operators such as the division operator (/) operate on two operands. PL/SQL has no ternary operators.

The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates (finds the current value of) an expression by combining the values of the operands in ways specified by the operators. This always yields a single value and datatype. PL/SQL determines the datatype by examining the expression and the context in which it appears.

Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Table 2 - 3 shows the default order of operations from first to last (top to bottom).

Operator Operation
**, NOT exponentiation, logical negation
+, - identity, negation
*, / multiplication, division
+, -, || addition, subtraction, concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN comparison
AND conjunction
OR inclusion
Table 2 - 3. Order of Operations

Operators with higher precedence are applied first. For example, both of the following expressions yield 8 because division has a higher precedence than addition:

5 + 12 / 4
12 / 4 + 5

Operators with the same precedence are applied in no particular order.

You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence: 

(8 + 6) / 2

In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:

100 + (20 / 5 + (7 - 3))

The following example shows that you can always use parentheses to improve readability, even when they are not needed:

(salary * 0.05) + (commission * 0.25)

Logical Operators

The logical operators AND, OR, and NOT follow the tri-state logic of the truth tables in Figure 2 - 3. AND and OR are binary operators; NOT is a unary operator.

NOT TRUE FALSE NULL
FALSE TRUE NULL
AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL
OR TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL
Figure 2 - 3. Truth Tables

As the truth tables show, AND returns the value TRUE only if both its operands are true. On the other hand, OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.

NOT NULL returns NULL because nulls are indeterminate. It follows that if you apply the NOT operator to a null, the result is also indeterminate. Be careful. Nulls can cause unexpected results; see "Handling Nulls" [*].

Order of Evaluation

When you do not use parentheses to specify the order of evaluation, operator precedence determines the order. Compare the following expressions: 

NOT (valid AND done)     |     NOT valid AND done

If the Boolean variables valid and done have the value FALSE, the first expression yields TRUE. However, the second expression yields FALSE because NOT has a higher precedence than AND; therefore, the second expression is equivalent to

(NOT valid) AND done

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

Likewise, in the next example, when valid has the value TRUE, the whole expression yields TRUE regardless of the value of done:

valid OR done

Usually, PL/SQL stops evaluating a logical expression as soon as the result can be determined. This allows you to 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.

Comparison Operators

Comparison operators compare one expression to another. The result is always TRUE, FALSE, or NULL. Typically, you use comparison operators in the WHERE clause of SQL data manipulation statements and in conditional control statements.

Relational Operators

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator:

Operator Meaning
= is equal to
<>, !=, ~= is not equal to
< is less than
> is greater than
<= is less than or equal to
>= is greater than or equal to

IS NULL Operator

The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. Therefore, to test for nullity (the state of being null), do not use the statement 

IF variable = NULL THEN ...

Instead, use the following statement:

IF variable IS NULL THEN ...

LIKE Operator

You use the LIKE operator to compare a character value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the character patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression yields TRUE: 

ename LIKE 'J%SON'

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression yields FALSE: 

45 BETWEEN 38 AND 44

IN Operator

The IN operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. For example, the following statement does not delete rows in which the ename column is null: 

DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');

Furthermore, expressions of the form

value NOT IN set

yield FALSE if the set contains a null. For example, instead of deleting rows in which the ename column is not null and not 'KING', the following statement deletes no rows:

DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');

Concatenation Operator

The concatenation operator (||) appends one string to another. For example, the expression 

'suit' || 'case'

returns the value 'suitcase'.

If both operands have datatype CHAR, the concatenation operator returns a CHAR value. Otherwise, it returns a VARCHAR2 value.

Boolean Expressions

PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called Boolean expressions, consist of simple or complex expressions separated by relational operators. Often, Boolean expressions are connected by the logical operators AND, OR, and NOT. A Boolean expression always yields TRUE, FALSE, or NULL.

In a SQL statement, Boolean expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, Boolean expressions are the basis for conditional control. There are three kinds of Boolean expressions: arithmetic, character, and date.

Arithmetic

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments

number1 := 75;
number2 := 70;

the following expression yields TRUE:

number1 > number2

Character

Likewise, you can compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. A collating sequence is an internal ordering of the character set, in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. For example, given the assignments

string1 := 'Kathy';
string2 := 'Kathleen';

the following expression yields TRUE:

string1 > string2

However, there are semantic differences between the CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, refer to Appendix C.

Date

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments

date1 := '01-JAN-91';
date2 := '31-DEC-90';

the following expression yields TRUE:

date1 > date2

Guidelines

In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. So, for example, the following IF condition might not yield TRUE:

count := 1;
IF count = 1.0 THEN ...

It is a good idea to use parentheses when doing comparisons. For example, the following expression is illegal because 100 < tax yields TRUE or FALSE, which cannot be compared with the number 500:

100 < tax < 500  -- illegal

The debugged version follows:

(100 < tax) AND (tax < 500)

A Boolean variable is itself either true or false. So, comparisons with the Boolean values TRUE and FALSE are redundant. For example, assuming the variable done has the datatype BOOLEAN, the IF statement

IF done = TRUE THEN ...

can be simplified as follows:

IF done THEN ...

Handling Nulls

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

x := 5;
y := NULL;
...
IF x != y THEN  -- yields NULL, not TRUE
   sequence_of_statements;  -- not executed
END IF;

In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

a := NULL;
b := NULL;
...
IF a = b THEN  -- yields NULL, not TRUE
   sequence_of_statements;  -- not executed
END IF;

NOT Operator

Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent: 

IF x > y THEN     |     IF NOT x > y THEN
   high := x;     |         high := y;
ELSE              |     ELSE
   high := y;     |         high := x;
END IF;           |     END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. So, if either or both x and y are null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high. If neither x nor y is null, both IF statements assign the same value to high.

Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

null_string := TO_VARCHAR2('');
zip_code := SUBSTR(address, 25, 0);
valid := (name != '');

So, use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

Concatenation Operator

The concatenation operator ignores null operands. For example, the expression 

'apple' || NULL || NULL || 'sauce'

returns the value 'applesauce'.

Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000: 

SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000)
   INTO credit_limit FROM accts WHERE acctno = my_acctno;

The function NVL returns the value of its second argument if its first argument is null. In the example below, if hire_date is null, NVL returns the value of SYSDATE. Otherwise, NVL returns the value of hire_date: 

start_date := NVL(hire_date, SYSDATE);

The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For instance, after the assignment 

new_string := REPLACE(old_string, NULL, my_string);

the values of old_string and new_string are the same.

If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, after the assignments

syllabified_name := 'Gold-i-locks';
name := REPLACE(syllabified_name, '-', NULL);

the value of name is 'Goldilocks'.

If its second and third arguments are null, REPLACE simply returns its first argument.

Contents Index Home Previous Next