4.3 NULLS in PLSQL

Chapter 4
Variables and Program Data

 

---

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:

• A null is never equal to anything else. None of the following IF statements can ever evaluate to TRUE:

  my_string := ' '; IF my_string = NULL THEN ...--This will never be true. max_salary := 0; IF max_salary = NULL THEN ...--This will never be true. IF NULL = NULL THEN ...--Even this will never be true.

• A null is never not equal to anything else. Remember: with null values, you just never know. None of the following IF statements can ever evaluate to TRUE.

  my_string := 'Having Fun'; your_string := NULL; IF my_string != your_string THEN ..--This will never be true. max_salary := 1234; IF max_salary != NULL THEN ...--This will never be true. IF NULL != NULL THEN ...--This will never be true.

• When you apply a function to a null value, you generally receive a null value as a result (there are some exceptions, listed below). A null value cannot be found in a string with the INSTR function. A null string has a null length, not a zero length. A null raised to the 10th power is still null.

  my_string := NULL; IF LENGTH (my_string) = 0 THEN ...--This will not work. new_value := POWER (NULL, 10);--new_value is set to null value.

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.