 |
|
Oracle Concepts by Burleson
Consulting |
Understanding Oracle SQL
Built-In Functions
Ever since the advent of relational databases
and the SQL language, database vendors have been at odds with the
concept of standardization. Every database vendor desires to be
ANSI compliant with regard to their relational database and the
structure of their SQL, but vendors also have a proprietary interest
in extending the functionality of their database so that they can be
better than their competition.
This market reality has led to the development
of SQL extensions. Within the Oracle 10g database, these SQL
extensions are called built-in functions, or BIFs. Oracle BIFs
have been introduced to extend the functionality of SQL, provide date
arithmetic functions, and also allow for the creation of customized
functions within SQL statements.
Oracle has introduced over 50 different
built-in functions into their database engine, and many of these
powerful functions allow for automatic data transformation inside the
SQL query.
Using decode and case functions
The most powerful of all of the BIFs are the
decode and case functions. The decode and case functions are
used within the Oracle database to transform data values for one value
to another.
One of the most amazing features of the case
the decode statements is that they allow us to create an index on data
column values that do not exist in the Oracle database.
Oracle started with the decode statement and
later refined it in Oracle 10g, morphing it into the case statement.
Let’s take a look at how the decode statement
works. The decode statement was developed to allow us to
transform data values at retrieval time. For example, say we
have a column named REGION, with values of N, S, W and E. When
we run SQL queries, we want to transform these values into North,
South, East and West. Here is how we do this with the decode
function:
select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;
Note that decode starts by specifying the column name, followed by set
of matched-pairs of transformation values. At the end of the
decode statement we find a default value. The default value
tells decode what to display if a column values is not in the paired
list.
We can also use the decode statement to count
the number of distinct values within a data column, such as the report
below:
PUBLISHER_NAME
COMPUTER FICTION MANAGEMENT MISCELLANEOUS
MUSIC
-------------------- ---------- ---------- ---------- -------------
----------
Big City
0 1
0
1 0
Book Press
0 0
0
1 0
Bookhouse
2 0
0
1 1
Desk Top
0 0
1
0 0
Learning Works
1 0
1
0 0
Mammoth House 1
1 0
1 0
Mountain
0 2
0
0 0
Nitpick And Sons
1 0
1
1 0
Star Books
0 0
1
1 0
Here is the SQL to create this report in your
sample database:
select
initcap(substr(pub_name,1,20))
publisher_name,
sum(decode(book_type,'computer',1,0))
computer,
sum(decode(book_type,'fiction',1,0))
fiction,
sum(decode(book_type,'management',1,0)) management,
sum(decode(book_type,'miscellaneous',1,0)) miscellaneous,
sum(decode(book_type,'music',1,0))
music
from
publisher p,
book b
where
p.pub_key = b.pub_key
group by
pub_name
;
This is a very important SQL statement because
it demonstrates the nesting of BIFs and the use of decode for counting
values. Let’s take a closer look.
1 – Transformation of publisher name - In the
above SQL we see that we are selecting the first 20 characters of the
pub_name (substr(pub_name,1,20)) and then passing this result to the
initcap function to make the words display in title case.
initcap(
<== change to title case
substr( <== take the
sub-string
pub_name, <== of the pub_name column
1,
<== starting at column 1
20
<== for 20 columns
)
)
2 – Transformation of book type - When we decode book_type, note
that we are transforming the value to a 1 if the column is found, and
a 0 if it is not found.
decode(
book_type, <==
Column name
‘computer, 1 <== Matched pair –
if computer, then 1
0
<== default value if not computer
)
Once we have converted the column to a 0-1
numeric value, we pass the entire clause to the sum function, and
add-up the numbers.
As we can see, the decode function is
convoluted and hard to write. Oracle added the case function to
SQL starting in Oracle 10g to simplify this type of data
transformation. The case statement is an easier for of the decode
statement. In its simplest form, the case statement is used to
return a value when a match is found:
SELECT
last_name,
commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM
employees
ORDER BY
last_name;
In the above example, we see that we display
“Low” when the commission percent is .1, “Average” when it is .15, and
“High” when it is .2. This syntax is far more readable than the
cryptic decode function.
The case statement can also be used to perform
complex Boolean matches. A more complex version is the Searched CASE
expression where a comparison expression is used to find a match:
SELECT
last_name,
job_id,
salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN
'10%'
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM
employees;
In this example we see that we test for specific job titles and
salaries, and display a percentage based upon these values.
Custom BIFs
One of the most exciting features of SQL is
the ability to create your own BIFs. In an exercise in this tutorial,
you will create a custom BIF and use it in your SQL statements.
This ability to create your own BIFs is extremely powerful because it
give you the ability to extend SQL in any way that you desire.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
