IS NULL
The IS NULL operator is used to test a variable for the absence of a value. Variables that have no value are referred to as being null and are most commonly encountered when retrieving data from a database. Variables you declare in a PL/SQL block are also initially null, or have no value, and remain null until your code specifically assigns a value to them.

The Syntax for IS NULL

the_value IS [NOT] NULL

In this syntax, the_value is a variable, or another expression, that you are testing.

If the value you are testing is null, then the IS NULL operator returns true. You can also reverse the test by using IS NOT NULL, in which case true is returned if the variable or expression in question contains a value.

Listing 4.5 shows an example of using the IS NULL operator to demonstrate that a variable has no value until one is specifically assigned.

INPUT
Listing 4.5. The IS NULL operator in action.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   test  INTEGER;
 4: BEGIN
 5:   --The variable TEST is currently null because
 6:   --a value hasn't been assigned to it yet.
 7:   IF test IS NULL THEN
 8:     DBMS_OUTPUT.PUT_LINE(`The variable TEST is null.');
 9:   END IF;
10:   --Assign a value to TEST and display it.
11:   test := 1;
12:   DBMS_OUTPUT.PUT_LINE(`TEST = ` || TO_CHAR(test));
13:   --Test is no longer null because a value has been assigned to it.
14:   IF test IS NOT NULL THEN
15:     DBMS_OUTPUT.PUT_LINE(`The variable TEST is NOT null.');
16:   END IF;
17: END;
18: /

OUTPUT

The variable TEST is null.
TEST = 1
The variable TEST is NOT null.
PL/SQL procedure successfully completed.

ANALYSIS
The variable test is declared in line 3. Initially it has no value and is considered to be null. The IS NULL operator is used in line 7 to check for this. Because no value has yet been assigned, the comparison evaluates to true and the message is printed. In line 11, a value is assigned to the variable test, and it is no longer considered to be null. The IS NOT NULL test in line 14 proves this.

Logical Operators

PL/SQL implements three logical operators: AND, OR, and NOT. The NOT operator is typically used to negate the result of a comparison expression, whereas the AND and OR operators are typically used to link together multiple comparisons.

NOT
Use the NOT operator when you are interested in the case in which a comparison is not true.

The Syntax for the NOT Operator

NOT boolean_expression

In this syntax, boolean_expression can be any expression resulting in a boolean, or true/false, value. This is often a comparison expression such as (a = b), but can also be a variable of the BOOLEAN datatype.

Applying the NOT operator to an expression causes the expression to evaluate to the opposite of what it normally would. For example, the following expression evaluates to true:

(8 = 8)

Applying the NOT operator to that same expression results in a value of false being returned, for example:

NOT (8 = 8)

It is possible to write the preceding expression without using the NOT operator. For example, the two expressions shown next are equivalent:

NOT (8 = 8)
(8 <> 8)

In a simple case like the preceding example, using the second expression will probably result in clearer code. With more complex expressions, that decision becomes a judgment call. Sometimes it is easier to define the case you aren't interested in and then negate it.

AND
The AND operator is used to join two comparison expressions when you are interested in testing whether both expressions are true. It can also be used for the same purpose with two boolean variables—to check and see if both are equal to true.

The Syntax for the AND Operator

boolean_expression AND boolean_expression

In this syntax, boolean_expression can be any expression resulting in a boolean, or true/false, value. This is often a comparison expression such as (a = b), but can also be a variable of the BOOLEAN datatype.

The AND operator returns a value of true if both expressions each evaluate to true; otherwise, a value of false is returned. Use AND when you need to test several conditions and execute some code only when they are all true. Table 4.7 shows some sample expressions using the AND operator.

Table 4.7. Expressions using the AND operator.

OR
The OR operator is used to join two comparison expressions when you are interested in testing whether at least one of them is true. It can also be used with two boolean variables to see whether at least one is set to true.

The Syntax for the OR Operator

boolean_expression OR boolean_expression

In this syntax, boolean_expression can be any expression resulting in a boolean, or true/false, value. This is often a comparison expression such as (a = b), but can also be a variable of the BOOLEAN datatype.

The OR operator returns a value of true if any one of the expressions evaluates to true. A value of false is returned only if both the expressions evaluate to false. Table 4.8 shows some sample expressions using the OR operator.