ANSI SQL Extensions  «Prev 

Frequently used Oracle SQL BIFs

  1. to_char: The to_char function is especially useful for translating DATE datatypes and converting numeric columns to character representations.
  2. upper: The upper function is often used in queries that search text columns and ensure that retrievals of case-sensitive data are properly serviced.
  3. lower: The lower function is used to convert text to a lowercase representation and is quite useful when searching for strings in text.
  4. substr: The substr function is used to extract sub-strings in a large character column. This is commonly used to extract subsets from large character datatype columns such as subsets of telephone numbers.
  5. decode: The decode function is used to translate values in an SQL statement from a cryptic abbreviation to a readable value. For example, the decode function can translate two-digit State names into the full name of the State.
We must remember that these "built in functions" will cause the SQL optimizer to perform a full-table scan unless a function-based index is created to match the BIF.
The most common example of an Oracle BIF is the use of the to_char function to translate a column with a DATE datatype. For example, the following query will display the number of STATSPACK snapshots that occurred in the month of March..
select count(*)
from 
perfstat.stats$snapshot
where
to_char(snap_time,'MON') = 'Mar';

If we were using a generic index on the snap_time column, the to_char function would not be able to utilize the DATE index. However, with the use of built-in functions, an index can be built on
to_char(snap_time,'Mon') 
and the SQL query could avoid a full-table scan.
To get the "month" from a DATE datatype you simply:
to_char( dt_column, 'mm' ) -- returns 01..12
to_char( dt_column, 'MON' ) -- returns JAN, FEB, ... DEC in your language
to_char( dt_column, 'Month' ) -- returns January, ... December in your language

GROUPING and ROLLUP BIFs

SELECT
  DECODE (GROUPING(region_name), 1, 'All Regions' , rname)
AS name'
  DECODE (GROUPING(job_title), 1, 'All Jobs', job_title)
AS job_title,
  COUNT(*) "#", AVG((sal) * 12 "Avg. Sal"
FROM
  Employee e,
  Region r
WHERE 
  r.region_name = e.region_name
GROUP BY ROLLUP (rname, job_title);
1) Grouping to summarize 2) rolled up the totals 3) determine the average 4) benefits of built-in functions
  1. GROUPING tells Oracle to summarize. In this case we have extracted region, job title, and salary information and summarized it by region.
  2. We have rolled up the totals by region_name and by job_title.
  3. AVG provides a nationwide average for the number of employees and the average salary.
  4. If this were written without BIFs, it would have become a cumbersome and challenging programming task.