 |
|
Miscellaneous Functions
Oracle Tips by
Burleson
|
There are some commonly used functions that don’t fall neatly into
any of the other categories—namely, decode() and nvl().
decode()
Of all the functions provided by SQL*Plus, decode() is
perhaps the most useful. A call to decode() can accept up to
255 parameters. Calls to decode() must follow this basic
syntax:
decode (expression, value, new value, default value);
The decode() call can only have one expression and one
default value. Value and new value must be a pair of parameters.
Consider the following call to decode():
SELECT decode (course_grade, 'A', 4,
'B', 3,
'C', 2,
'D', 1, 0)
FROM SCHEDULED_CLASSES
WHERE ssn = '999999999'
AND course_number = 2103;
This function is the closest equivalent to a case statement
that can be found in SQL. The value and new value parameters don’t
have to be literal values; however, they can be expressions such as
mathematical operations and function calls.
In the event that you try to port SQL statements between Oracle and
another relational database, you should be aware that decode()
is not an ANSI standard SQL function.
nvl()
The nvl() function is used to substitute a NULL value
with another value. The most common example of a call to nvl()
looks like this:
SELECT nvl (base_salary, 20000)
FROM EMPLOYEES;
This would replace any NULL value retrieved by the query
with the number 20000.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
