Transcript MUCH ADO ABOUT NOTHING Walter Schenk SoluTech Consulting Services Inc AGENDA  What it is and what is not  NULL in functions, expressions, comparisons and conditional control 

MUCH ADO ABOUT
NOTHING
Walter
Schenk
SoluTech Consulting Services Inc
AGENDA

What it is and what is not
 NULL in functions, expressions,
comparisons and conditional control
 NULL in Indexes
 NULL in programming languages
WHAT A NULL IS NOT!

A NULL is NOT 0!
 A NULL is NOT Nothing!
 A NULL is NOT an empty string (although
Oracle does treat it as such but that may
change in the future!)
THEN WHAT IS A NULL?

When a column in a row has no value
 The value is not known or meaningful
WHY DO WE NEED NULL
VALUES?

Often real-world information is incomplete
 It is a way of handling this unknown
NULL IN FUNCTIONS
“Normal” scalar functions will return NULL when
given a NULL argument
 All aggregate functions except COUNT(*) and
GROUPING ignore nulls.
 You can use the NVL in the argument to an
aggregate function to substitute a value for a null.
 If a query with an aggregate function returns no
rows or only rows with nulls for the argument to
the aggregate function, the aggregate function
returns null.

FUNCTIONS THAT HANDLE
NULLS
– NVL
– CONCAT
– REPLACE
– NULLIF (9i)
– COALESCE (9i)
– DECODE
NVL
NVL ( expr1 , expr2 )

If expr1 is null, returns expr2; if expr1 is not null,
returns expr1.
 Can be any datatype
CONCAT
CONCAT ( char1 , char2 )
SELECT CONCAT(‘job’,NULL) "Job"
FROM DUAL;
Job
----------------job

Returns char1 concatenated with char2.
REPLACE
REPLACE ( char , search_string, replacement_string)

Returns char with every occurrence of
search_string replaced with
replacement_string.
 If replacement_string is omitted or null, all
occurrences of search_string are removed.
 If search_string is null, char is returned.
REPLACE
SELECT REPLACE(‘Hello there’,’l’,NULL) "Chan
FROM DUAL;
Changes
-------------Heo there
SELECT REPLACE(‘Hello there’,NULL,’xxx’) "Ch
FROM DUAL;
Changes
-------------Hello there
NULLIF
A := NULLIF(B,C);

If the values match, then the result is NULL
COALESCE
COALESCE (expr1, expr2, ..., expr n
COALESCE returns the first non-null expr in
the expression list.
 At least one expr must not be the literal NULL. If
all occurrences of expr evaluate to null, then the
function returns null.

DECODE

NULL = NULL !!!!
DECODE (deptno, 10, ’ACCOUNTING’,
20, ’RESEARC
30, ’SALES’,
NULL, ’OPERA
’NONE’)
NULLS AND COMPARISONS

Only compare NULLs with IS NULL or IS
NOT NULL
 Use of any other operator and the result will
be NULL!
 NULL <> NULL (except in the DECODE
expression and compound keys)
NULLS IN CONDITIONS

Always use: variable IS NULL
 Never user: variable = NULL
NULLS IN LOGICAL
EXPRESSIONS
x
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
NULL
NULL
NULL
y
TRUE
FALSE
NULL
TRUE
FALSE
TRUE
TRUE
FALSE
NULL
x AND y
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
NULL
FALSE
NULL
x OR y
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
TRUE
NULL
NULL
NOT x
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE
NULL
NULL
NULL
HANDLING NULLS

Avoid common mistakes by keeping the
following in mind:
– Comparisons involving NULLs always yield
NULL
– Applying the logical operator NOT to a NULL
yields NULL
– In conditional control statements, if the
condition yields NULL, its associated sequence
of statements is NOT executed!
NULL AND CONDITIONAL
CONTROL
MOST COMMON MISTAKE:
In conditional control statements, if the
condition yields NULL, its associated
sequence of statements is NOT executed!
NULL AND CONDITIONAL
CONTROL
IF x > y THEN
high := x;
ELSE
high := y;
END IF;
IF NOT x > y THEN
high := Y;
ELSE
high := x;
END IF;
PROGRAMMING GUIDELINE
Always account for NULL in applications
even if the underlying database objects are
defined as NOT NULL.
NULL AND INDEX ENTRIES

Oracle does NOT enter an index value if the
ENTIRE key is NULL
 Consequences:
– An index can NOT be used in a search criteria
for NULL values
– A UNIQUE constraint on a column that can be
NULL will allow multiple NULL values
NULLS IN PROGRAMMING
LANGUAGES

PL/SQL
 C/C++
 VB.NET and C#.NET
PL/SQL

Full support of NULL
C/C++

C/C++ does NOT support NULL
 Variables are passed on to a C/C++
application through host variables for both
input as well as output
 Host variables are prefixed with a colon
(“:”) to set them apart from Oracle objects
C/C++

Any host variable can be associated with an
indicator variable
 An indicator variable is a short integer
variable that indicates the condition of its
host variable
C/C++ host variable on Input
If indicator variable = –1 then the variable
is a NULL and Oracle ignores the value of
the host variable
 If indicator variable >=0 Oracle will assign
the value of the host variable to the column

C/C++ host variable on Output

If indicator variable = -1 then the column is
NULL and the value of the host variable is
indeterminate
 If indicator variable = 0 then value of the
host variable is assigned
C/C++
EXEC SQL SELECT SAL, COMM
INTO :salary,:commission:ind_com
FROM EMP
WHERE EMPNO = :emp_number;
if (ind_comm == -1)
pay = salary
else
pay=salary + commission;
C/C++
Set ind_comm = -1;
EXEC SQL INSERT INTO emp (empno,comm)
VALUES (:emp_number,:commision:ind_comm);
VB

In VB6 only Variant data types could
support NULL
 The NULL keyword indicated that a
variable contained the NULL value
 The IsNull function was used to test for
NULL
VB.NET

During a migration from VB6 to VB.NET:
– Null is converted to DBNull
– IsNull is converted to IsDBNull
– The Variant data type is converted to Object

In VB6 Null could be used in functions and
assignments; DBNull cannot!
 Consider using the Nothing keyword in
.NET instead of Null.
VB.NET IsDBNull function

Returns TRUE if the expression evaluates to the
DBNull type; otherwise returns FALSE
 The System.DBNull value indicates that the object
represents missing or nonexistent data
 It is NOT the same as Nothing which indicates
that a variable has not yet been initialized
VB.NET DBNull class

The DBNull class is used to indicate the
absence of a known value
 The class differentiates between a null value
and an uninitialized value
PROGRAMMING GUIDELINE

Do not circumvent the use of NULLs by
assigning “meaningless” or “out-of-range”
values
 Example: a column “EndDate” is often
assigned a far fetched date in the future to
avoid use of NULL
SQL STANDARDS AND
NULLS
FIPS 127-2 (1993)
The following features have "preliminary" syntax and semantics
available in Working Draft form as part of an on-going ANSI and
ISO/IEC standardization effort for further development of the SQL
language. Features specified in preliminary form include:
17. Multiple null states. A facility that allows user definitions for an
arbitrary number of application specific Null values, such as
"Unknown", "Missing", "Not Applicable", "Pending", etc. Each such
Null value would have a different representation in the database so
that they could be distinguished during retrieval or update.
SQL STANDARDS AND
NULLS
FIPS 193-7 (1995)
If an SQL/ERI Server implementation at the Minimal SDL level or below
chooses not to provide support for null values (see item 4 of Section 4.1), then
it may raise an implementation-defined exception in any SQL statement that
attempts to process null values.
If an SQL/ERI Server implementation at the Minimal SDL level or below
chooses not to provide support for null values (see item 4 of Section 4.1), then
it shall provide an implementation-defined conversion of would-be null values
in Information Schema tables to an appropriate non-null value.
If an SQL/ERI Server implementation at the Minimal SDL level or below
chooses not to provide support for null values (see item 4 of Section 4.1), then
it may raise an implementation- defined exception in any SQL statement that
attempts to process null values.
SQL STANDARDS AND
NULLS

The concept of NULL is subject to change!
 Various implementations may vary.
DEPARTING WORDS

Never ignore NULL
 Use NULL properly
QUESTIONS?

NoCOUG, February 20, 2003