Oracle SQL Functions


SQL functions manipulate values and return a single value. If possible SQL functions will perform implicit datatype conversion. Most functions called with NULL will return NULL.

Function (argument1, argument2, ...)

Functions are scalar or aggregate in nature.

ScalarAggregate
Act on a single rowAct on a group of rows
Return a single value for each row queriedReturn a single value for a group of rows
Can appear in a SELECT statement if the SELECT statement does not contain a GROUP BY clauseCan be used in select lists and HAVING clauses
Can appear in WHERE, START WITH and CONNECT BY clausesIf used in a SELECT with a GROUP BY clause then the GROUP BY must include all columns not affected by the function
Can be nested 

Some functions do not return NULL values.

CONCATString concatenation
DECODEExplicit conversions, translates values into specified codes.
DUMPDumps a value
NVLNULL value substitution where if a NULL is found it is replaced with the given value
REPLACEString replacement

DECODE

DECODE translates a column value pr expression into some other value. Certain values which the column value or expression may have can be specified. For each value list a code into which the value can be translated. If the column value or expression's value is not in the list of values then SQL uses the default code for translation.

DECODE (<column_name/expression>, <value_1>, <code_1>, <value_2>, <code_2>, ..., <value_n>, <code_n>, <default code>);

DECODE is neither a character nor a numeric function because it can return values of any type. DECODE could be used to determine a range in whicha value falls. The application of the DECODE function can present a code for that range. DECODE can effectively serve the same purpose as multiple IF-THEN_ELSE statements or a CASE or SWITCH statement if PL/SQL has a CASE or SWITCH statement.

SELECT	 NAME "Name"
	,SALARY "Salary"
	,DECODE
	(
		 TRUNC(SALARY/1000,0)
		,50,1
		,60,2
		,70,3
		,80,4
		,90,5
		,100,6
		,110,7
		,"Too Expensive"
	) "Shares"
	FROM EMPLOYEES;

DUMP

select owner, dump(table_name) from all_tables;

OWNER
------------------------------
DUMP(TABLE_NAME)
----------------------------------------------------------------------------------
SYS
Typ=1 Len=4: 68,85,65,76

SYS
Typ=1 Len=20: 83,89,83,84,69,77,95,80,82,73,86,73,76,69,71,69,95,77,65,80

SYS
Typ=1 Len=19: 84,65,66,76,69,95,80,82,73,86,73,76,69,71,69,95,77,65,80

SYS
Typ=1 Len=21: 83,84,77,84,95,65,85,68,73,84,95,79,80,84,73,79,78,95,77,65,80

Function Expression Precedence

SQL Numeric Functions

Character Value Returning Character Functions

Numeric Value Returning Character Functions

Date and Time Functions

Subtraction of one date value from another when both dates are passed through the TO_DATE function gives the number of days between the two dates. Use of dates in queries requires use of the TO_DATE() function in order to convert between external strings and internal Oracle date representation.

Oracle stores dates internally in Oracle7 as a seven-byte field containing two decimal digits per byte.

yearmonthdayhourminutesecond
YYYY (2 bytes)MMDDhhmiss

New Year's eve 1999 would be stored in Oracle internally as shown below.

BytesValue
119
299
312
431
523
645
700

Conversion Functions

In general implicit datatype conversions are performed between compatible datatypes. Explicit datatype conversions can help performance.

Grouping Functions

Group functions act on groups of values and not individual items. Use group functions in conjunction with SELECT, INSERT, UPDATE and DELETE statements.

Various Other Functions

Expression (expr) Clauses

Expressions can exist in many forms and can be used in the following situations.