Previous | Table of Contents | Next

Page 91

ANALYSIS
Listing 4.9 is a somewhat contrived example, but it illustrates very well the effects of nulls on comparison expressions. Take a close look at what is going on here. The first IF statement in line 10 tests for a = n. As you might expect, this is not true, but it is important to understand that it is not false either. The second IF statement in line 16 proves this. The test there is for a <> n, the exact opposite of the previous comparison, and it also is not true. Line 23 shows an extreme case, an expression which many people at first glance would expect to always be true. However, because the value of n is unknown, the truth of this expression is also unknown and it evaluates to NULL.

There are three basic things to remember when dealing with nulls:

In an expression, null values propagate. For the most part, any arithmetic, date, string, or boolean expression containing even one null value will also evaluate to NULL. There are some exceptions to this rule, which are described shortly.

The logical operators AND, OR, and NOT are often used to link together comparison expressions. Table 4.11 shows how these operators function in expressions with null values.

Table 4.11. Three-valued logic truth table.

Operator Expression Result
AND TRUE AND TRUE TRUE
TRUE AND FALSE FALSE
TRUE AND NULL NULL
FALSE AND NULL FALSE
NULL AND NULL NULL
OR TRUE OR TRUE TRUE
TRUE OR FALSE TRUE
TRUE OR NULL TRUE
FALSE OR NULL FALSE
NULL OR NULL NULL
NOT NOT TRUE FALSE
NOT FALSE TRUE
NOT NULL NULL

Page 92

Lines 28 through 43 in Listing 4.9 contain some IF statements that demonstrate how each of the logical operators operate on null values.

The IF statement is the fundamental decision-making structure of PL/SQL. Give it a boolean expression, and it evaluates that expression and makes a decision as to which piece of code to execute. However, boolean expressions can have three values: true, false, and NULL. An IF statement has only two parts: the code to be executed when an expression is true and the code to be executed when it isn't. There is a mismatch here, and it's very important to keep in mind that the ELSE portion will be executed when the result of an expression is unknown, or, in other words, when the expression is null.

TIP
Lines 53 through 61 of Listing 4.9 show a way to construct an IF statement that has separate execution paths for true, false, and NULL.

There are some exceptions to the general rule that nulls propagate in expressions. Null character strings are sometimes handled as if they were zero-length strings, and PL/SQL does have some functions and operators that have been specifically designed to help you work with nulls.

You can concatenate strings, even if one is null, and get the results you would expect. This is because the concatenation operator simply ignores any null strings. However, if all the strings are null, then the result will be null. Also bear in mind that PL/SQL treats a zero-length VARCHAR2 string as a null value.

You can use the IS NULL operator to see whether or not a particular variable or expression is null. It allows your code to detect and act on null values. You saw an example of this earlier in Listing 4.5. The IS NULL operator returns only a true or false value, never a NULL.

WARNING
Always use the IS NULL operator when checking for null values. Do not use the equality or inequality operators to compare a variable to NULL. You can code a statement such as IF some_var = NULL, but you won't get the results you might expect. Use IF some_var IS NULL instead.

The built-in NVL function allows you to specify an alternate value to be used when its argument is null. Lines 44 through 52 of Listing 4.9 show an interesting use of this function to account for the possibility of the variable n being null. Appendix B describes this useful function.

The built-in DECODE function actually treats NULL as a specific value instead of an unknown value. It might seem contradictory, but it's useful. DECODE is also described in Appendix B.

Page 93
Do Don't
Do initialize all your variables in order to eliminate the possibility of null values.
Do use NVL where feasible when retrieving values from that database in order to replace null values with an acceptable alternative.
Don't forget to think through the possible implications of null values in every expression you write, especially the boolean, including comparison, expressions.

Datatype Conversions

Sometimes you need to convert a value of one datatype to another. This is frequently true with dates and numbers, which are often converted to and from character strings. For example, you might want to display a date, so you must first convert it to a character string of the desired format. There are two ways of approaching the issue of conversion. One is to rely on Oracle to implicitly convert datatypes, which it will do automatically when it makes sense. The second and more preferred method is to code your conversions explicitly.

Implicit Conversion

NEW TERM
When you mix different datatypes in an expression, Oracle will convert them for you when it makes sense to do so. This is referred to as implicit conversion. Listing 4.10 shows several examples of implicit conversion.

INPUT
Listing 4.10. Implicit conversion examples. 

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   d1    DATE;
 4:   cd1   VARCHAR2(10);
 5:   cd2   VARCHAR2(10);
 6:   n1    NUMBER;
 7:   cn1   VARCHAR2(10);
 8:   cn2   VARCHAR2(10);
 9: BEGIN
10:   --Assign a value to this string which represents a date.
11:   cd1 := '15-Nov-61';
12:   --Now assign the string to a date variable.
13:   --The conversion is implicit.
14:   d1 := cd1;
15:   --Now assign that date variable to another string.
16:    --Again the conversion
17:   --is implicit, but this time the conversion is
18:    --from a date to a string.
19:   cd2 := d1;

                                                 continues

Previous | Table of Contents | Next

Используются технологии uCoz