SQL functions are built into ORACLE and are available for use in various appropriate SQL statements.
1. The SQL functions can be used to

• Perform calculations on data.
• Modify individual data items.
• Manipulate output for groups of rows.
• Format dates and numbers for display.
• Convert column data types.

2. SQL functions may accept arguments are always return a value, and can be nested.
3. If an SQL function is called with a NULL argument, then a NULL is returned.


SQL FUNCTION TYPES :

SQL identifies two types of functions

• SINGLE row functions.
• MULTIPLE row functions.

SINGLE ROW FUNCTIONS :

• These functions return a single result for every row of a queried table or view.

MULTIPLE ROW FUNCTIONS :               

1. These functions manipulate groups of rows and return one result per group of rows
2. The single row functions can appear in

• SELECT list.
• WHERE clause and ORDER BY clause.
• START WITH clause.
• CONNECT BY clause.

3. The single row functions are categorized as

• CHARACTER functions.
• NUMBER functions.
• DATE  functions.
• CONVERSION functions.

4. SINGLE row functions are used to manipulate date items.
5. They can accept one or more arguments and return one value for each row returned by the query.
6. The argument for a single row function can be

• User supplied constant.
• Variable value.
• Column name.
• Expression.

Syntax :

FUNCTIONNAME(Column / Expr, [Arg1,Arg2,...])

Single Row Functions Features :

•  Acts on each row returned in the query.
• Returns one result per row.
• May return a data value of  a different data type than that referenced.
• May expect one or more arguments.
• Can be used in select, where and order by clauses.
• It can be nested.

Specification Behaviour Of Functions :

Character Functions :

• Accept character input and can return both character and number values.

Number Functions :             

• Accept numeric input and return numeric values.

Date Functions :  

• Operate on values of date date type and can return both date and number.

Conversion Function :  

• Convert a value from one data type to another data type.

General Function :

• NVL - Operates on NULL values.
• DECODE - Operates on any data type and can return any data type.