ANSI SQL Extensions  «Prev  Next»

Lesson 1

Using Oracle Extensions for ANSI SQL

As we discussed in the previous module, Oracle supplies a host of extensions to the ANSI standard, known as built-in functions, to allow easier access and translation of data. While there are dozens of built-in functions, our concern here is the impact of built-in functions upon Oracle performance, and the use of built-in functions to improve the performance of SQL queries.
While this is not internal database tuning in the strictest sense, the prudent use of Oracle SQL extensions can dramatically improve the processing speed of programs that contain embedded SQL.
In fact, many programmatic functions such as translating characters, re-formatting numbers and dates, and editing text can very often be done right in the SQL query. By placing the bulk of the work in the Oracle query, the programs that contain the SQL become both faster and more elegant.
This module will show you the most common Oracle built-in functions (BIFs), and show how they can assist with Oracle performance by quickly transforming row data. By the end of this module, you will be able to:

Module Objectives

  1. Describe the purpose of Oracle built-in functions
  2. Use query overrides with Oracle SQL
  3. Use the NVL built-in function with Oracle SQL
  4. Use the DECODE built-in function with Oracle SQL
  5. Describe the purpose of some advanced built-in functions
Next we will look at the Oracle built-in functions.

NULL Values as Arguments to Built-In Functions

If a NULL argument is passed to a built-in function, a NULL value is returned except in the following cases. The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be NULL. If a search is successful, the corresponding result is returned. In Example 3-1, if the column manager_id is NULL, DECODE returns the value 'nobody'.

Example 3-1 NULL Value as Argument to DECODE Function

SQL> DECLARE
2 manager VARCHAR2(40);
3 name employees.last_name%TYPE;
4 BEGIN
5 -- NULL is a valid argument to DECODE.
6 -- In this case, manager_id is NULL
7 -- and the DECODE function returns 'nobody'.
8
9 SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
10 INTO manager, name
11 FROM employees
12 WHERE employee_id = 100;
13
14 DBMS_OUTPUT.PUT_LINE
15 (name || ' is managed by ' || manager);
16 END;
17 /
King is managed by nobody
PL/SQL procedure successfully completed.