Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 4.2 Scalar Datatypes Chapter 4
Variables and Program Data
Next: 4.4 Variable Declarations

4.3 NULLs in PL/SQL

Wouldn't it be nice if everything was knowable, and known? Hmmm. Maybe not. The question, however, is moot. We don't know the answer to many questions. We are surrounded by the Big Unknown, and because Oracle Corporation prides itself on providing database technology to reflect the real world, it supports the concept of a null value.

When a variable, column, or constant has a value of NULL, its value is unknown -- indeterminate. "Unknown" is very different from a blank or a zero or the Boolean value FALSE. "Unknown" means that the variable has no value at all and so cannot be compared directly with other variables. The following three rules hold for null values:

4.3.1 NULL Values in Comparisons

In general, whenever you perform a comparison involving one or more null values, the result of that comparison is also a null value -- which is different from TRUE or FALSE -- so the comparison cannot help but fail.

Whenever PL/SQL executes a program, it initializes all locally declared variables to null (you can override this value with your own default value). Always make sure that your variable has been assigned a value before you use it in an operation.

You can also use special syntax provided by Oracle to check dependably for null values, and even assign a null value to a variable. PL/SQL provides a special reserved word, NULL, to represent a null value in PL/SQL. So if you want to actually set a variable to the null value, you simply perform the following assignment:

my_string := NULL; 

If you want to incorporate the possibility of null values in comparison operations, you must perform special case checking with the IS NULL and IS NOT NULL operators. The syntax for these two operators is as follows:

<identifier> IS NULL
<identifier> IS NOT NULL

where <identifier> is the name of a variable, a constant, or a database column. The IS NULL operator returns TRUE when the value of the identifier is the null value; otherwise, it returns FALSE. The IS NOT NULL operator returns TRUE when the value of the identifier is not a null value; otherwise, it returns FALSE.

4.3.2 Checking for NULL Values

Here are some examples describing how to use operators to check for null values in your program:

4.3.3 Function Results with NULL Arguments

While it is generally true that functions which take a NULL argument return the null value, there are several exceptions:

Although there are some exceptions to the rules for null values, nulls must generally be handled differently from other data. If your data has NULLS, whether from the database or in local variables, you will need to add code to either convert your null values to known values, or use the IS NULL and IS NOT NULL operators for special case null value handling.

Previous: 4.2 Scalar Datatypes Oracle PL/SQL Programming, 2nd EditionNext: 4.4 Variable Declarations
4.2 Scalar Datatypes Book Index4.4 Variable Declarations

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.