Oracle Functions

 

cularly 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??????????????????????????????? ?Definitionvalue1 + 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 FUNTIONSFunction???????????????????????????????? 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???????????????????????????????????? DescriptionGREATEST(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