Take a look at the last example in Table 4.9. The comment notes that it is actually two expressions joined together, but which two? What value should Oracle return for this expression? The answer to both these questions can be found in the rules governing operator precedence.

Operator Precedence

When evaluating an expression consisting of different values, datatypes, and operators, Oracle follows a specific set of rules that determine which operations are done first. Each operator has an assigned precedence. Operators with a higher precedence are evaluated first. Operators of the same precedence level are evaluated from left to right. Table 4.10 shows these precedence levels for each of the various operators.

Table 4.10. Operator precedence.

Take another look at the expression referred to in the previous section. The following list shows the steps Oracle would take to evaluate it:

1. 1-5**2<=10*4-20
2. 1-25<=10*4-20

3. 1-25<=40-20
4. -24<=20
5. true

You can control the order in which Oracle evaluates an expression by using parentheses. Oracle will evaluate any part of an expression in parentheses first. If parentheses are nested, Oracle will always evaluate the innermost expression first and then move outwards. Here is what happens to the preceding expression if you add some parentheses:

1. (1-5)**2<=10*(4-20)
2. (-4)**2<=10*(-16)
3. 16<=-160
4. false

Types of Expressions

One way of classifying expressions is by the datatype of the resulting value. Using this scheme, expressions can be classified as one of these types:

• Arithmetic or numeric
• Boolean
• String
• Date

Any expression returning a numeric value is referred to as an arithmetic expression, or sometimes as a numeric expression.

A boolean expression is any expression that returns a true or false value. Comparison expressions are really special cases of this type, but they are not the only way to get a true/false value. A boolean variable—or several boolean variables linked together with the logical operators AND, OR, and NOT—will also return a boolean result.

String expressions are those that return character strings as results, and date expressions are those that result in a datetime value.

Generally speaking, you can use an expression of the appropriate datatype anywhere in your PL/SQL code where a value is required. The exception to this would be in function and procedure calls that modify their arguments.

Null Values in Expressions

Until now, the discussion has ignored the effect of nulls in expressions. This was done in order to concentrate on the normal function of each of the operators and also because nulls pretty much have the same effect regardless of the operation being done.

What is a null? The term is best understood as referring to an "unknown value." Any variable or expression is considered null when the value of that variable or expression is unknown. This situation can occur if you declare a variable and use it in an expression without first assigning a value. Because the variable has no assigned value, the result of the expression can't be known. More commonly, nulls are encountered when reading data from a database. Oracle, like any other relational database, does not force you to store a value for each column in a table. When no specific value is stored, the contents of that column are considered "unknown," and the column is referred to as being null.

NEW TERM
The effects of nulls are particularly insidious when writing boolean expressions, such as the WHERE clause in a SQL SELECT statement. SQL uses what is called three-valued logic. Three-valued logic says that the result of a boolean expression can be either true, false, or NULL. Many a programmer has felt the sting of an IF statement gone awry because of an unexpected null value, and some consider three-valued logic to be more of a three-pronged pitchfork prodding them in the behind. The code in Listing 4.9 shows why nulls can cause so much grief.

INPUT
Listing 4.9. Effects of nulls on boolean expressions.

 1: --Remember to execute; SET SERVEROUTPUT ON
 2: DECLARE
 3:   a     INTEGER;
 4:   b     BOOLEAN;
 5:   n     INTEGER;     --this will be our null value.
 6: BEGIN
 7:   --Assign a value to the variable A, but leave N null.
 8:   a := 2;
 9:   --Note that the test for A=N fails.
10:   IF a = n THEN
11:     DBMS_OUTPUT.PUT_LINE(`a = n is true');
12:   ELSE
13:     DBMS_OUTPUT.PUT_LINE(`a = n is not true');
14:   END IF;
15:   --But also note that the test for a <> n fails.
16:   IF a <> n THEN
17:     DBMS_OUTPUT.PUT_LINE(`a <> n is true');
18:   ELSE
19:     DBMS_OUTPUT.PUT_LINE(`a <> n is not true');
20:   END IF;
21:   --Here is an expression that many people first
22:   --expect to always be true.
23:   IF (a = n) OR (a <> n) THEN

                                                   continues

Listing 4.9. continued

24:     DBMS_OUTPUT.PUT_LINE(`(a = n) or (a <> n) is true');
25:   ELSE
26:     DBMS_OUTPUT.PUT_LINE(`(a = n) or (a <> n) is not true');
27:   END IF;
28:   --TRUE and NULL = NULL
29:   IF (a = 2) AND (a <> n) THEN
30:     DBMS_OUTPUT.PUT_LINE(`TRUE and NULL = TRUE');
31:   ELSE
32:     DBMS_OUTPUT.PUT_LINE(`TRUE and NULL = NULL');
33:   END IF;
34:   --TRUE or NULL = TRUE
35:   IF (a = 2) OR (a <> n) THEN
36:     DBMS_OUTPUT.PUT_LINE(`TRUE or NULL = TRUE');
37:   ELSE
38:     DBMS_OUTPUT.PUT_LINE(`TRUE or NULL = NULL');
39:   END IF;
40:   --NOT NULL = NULL
41:   IF (NOT (a = n)) IS NULL THEN
42:     DBMS_OUTPUT.PUT_LINE(`NOT NULL = NULL');
43:   END IF;
44:   --TIP: try this if you want a null value to be
45:    --considered "not equal".
46:   --Be careful though, if BOTH A and N are NULL
47:   --NVL will still return TRUE.
48:   IF NVL((a <> n),true) THEN
49:     DBMS_OUTPUT.PUT_LINE(`The values are not equal.');
50:   ELSE
51:     DBMS_OUTPUT.PUT_LINE(`The values are equal.');
52:   END IF;
53:   --TIP: a three-valued IF construct.
54:   b := (a <> n);
55:   IF b THEN
56:     DBMS_OUTPUT.PUT_LINE(`a <> n is TRUE');
57:   ELSIF NOT b THEN
58:     DBMS_OUTPUT.PUT_LINE(`a <> n is FALSE');
59:   ELSE
60:     DBMS_OUTPUT.PUT_LINE(`a <> n is NULL');
61:   END IF;
62: END;
63: /

OUTPUT

a = n is not true
a <> n is not true
(a = n) or (a <> n) is not true
TRUE and NULL = NULL
TRUE or NULL = TRUE
NOT NULL = NULL
The values are not equal.
a <> n is NULL
PL/SQL procedure successfully completed.