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
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
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.
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
<identifier> IS NULL <identifier> IS NOT NULL
where <identifier> is the
Here are some examples describing how to use operators to check for null values in your program:
In the following example, the validation rule for the hire_date is that it cannot be later than the current date and it must be entered. If the
IF hire_date >= SYSDATE OR hire_date IS NULL THEN DBMS_OUTPUT.PUT_LINE (' Date required and cannot be in future.'); END IF;
In the following example, a bonus generator rewards the hard-working support people (not the salespeople). If the employee's commission is over the target compensation plan target, then send a thank you note. If the commission is under target, tell them to work harder, darn it! But if the person has no commission at all (that is, if the commission IS NULL), give them a bonus recognizing that everything they do aids in the sales effort. (You can probably figure out what my job at Oracle Corporation was.) If the commission is a null value, then
IF :employee.commission >= comp_plan.target_commission THEN just_send_THANK_YOU_note (:employee_id); ELSIF :employee.commission < comp_plan.target_commission THEN send_WORK_HARDER_singing_telegram (:employee_id); ELSIF :employee.commission IS NULL THEN non_sales_BONUS (:employee_id); END IF;
PL/SQL treats a string of zero-length as a NULL. A
my_string := NULL; my_string := '';
While it is generally true that functions which take a NULL argument return the null value, there are several exceptions:
Concatenation. There are two ways to concatenate strings: the CONCAT function (described in Chapter 11) and the concatenation operator (double vertical bars:
). In both cases, concatenation ignores null values, and simply
CONCAT ('junk', NULL) ==> junk 'junk' NULL ' ' NULL 'mail' ==> junk mail
Of course, if all the individual strings in a concatenation are NULL, then the result is also NULL.
The NVL function. The NVL function (described in Chapter 13) exists
new_description := NVL (old_description, 'Not Applicable');
The REPLACE function. The REPLACE function (described in Chapter 11) returns a string in which all occurrences of a specified match string are
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.
|4.2 Scalar Datatypes||4.4 Variable Declarations|
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.