Quick understanding of Oracle Number functions
Particularly in business everything is guided by numbers.
•Fortunately Oracle, provides functions which deal with numbers.
•Oracle functions deal with three classes of numbers single values,group
values and list values.
ü A single value is one number, such as 24.45
üA group of values is all numbers in a column.
üA list of values is a series of numbers
Single Value Functions
Function Definition
value1 + value2 Addition
value1 – value2 Substraction
value1 * value2 Multiplication
value1 / value2 Division
ABS(value) ABSolute value
SIGN(value) 1 if value positive -1 if value negative 0 if value zero
CEIL(value) Smallest integer larger or equal to value
FLOOR(value) Largest integer smaller or equal to value
MOD(value,divisor) Returns modulus
NVL(value,substitute) Substitute for value if value is null.
POWER(value,exponent) Value raised to an exponent
ROUND(value,precision) Rounding value to precision
TRUNC(value, precision) Value truncated to precision
SQRT(value) Square root of value
NULLs in a single value functions
•Note that any arithmetic operation including a NULL value has NULL as result.
•NULL is’nt zero.
•Think of NULL as a value that is unknown.
•To be precise NULL is unavailable data.
GROUP VALUE FUNTIONS
Function Description
AVG(ColumnName) Average value of all values in the column.
COUNT(ColumnName) COUNT for rows for column.
SUM(ColumnName) Sum of all values in the column.
MIN(ColumnName) Returns least of all values in that column.
MAX(ColumnName) Returns highest of all values in that column.
LIST VALUE FUNCTIONS
Function Description
GREATEST(value1,value2,value3,..) Returns greatest value in the list
LEAST(value1,value2,value3,..) Returns least value in the list
ØNULLs in GROUP – Value Functions :
Group value functions treat NULL values differently than single value functions.
Group value functions ignore NULL values and go ahead with the result
Ø NULLs in List– Value Functions :
List value functions treat NULL values same as that of single value functions.
list value functions produce null result if any value in the list is NULL.
That is don’t ignore NULL values
This is a brief description about functions dealing with number data
About the Author:is a trainer in oracle and Java
Article Source: ArticlesBase.com - Quick understanding of Oracle Number functions