IT Articles : Oracle Functions

IT Articles : 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??????????????????????????????? ?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.

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.


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

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *