Ø These functions accept numeric input
and return numeric values as output.
Ø Many functions return values that
are accurate to 38 decimal point.
Round Function:
Syntax: Round(n,m)
Ø It returns ‘n’ rounded to ‘m’ places
right of the decimal point.
Ø If ‘m’ is omitted, ‘n’ is round to 0
places.
Ø ‘m’ can be negative and rounds off
the digits to the left of the decimal point.
Ø ‘m’ must be an integer.
Ex:
- Select 15.293 num1,
round(15.193,1) rounded from dual;
- Select 15.193 num1,
round(15.193, -1) rounded from dual;
- Select 45.923 num1,
round(45.923,2) rounded,round(45.923,0) rounded , round(45.923,-1) rounded
from dual;
Truncate Function:
Syntax: Trunc(n,m)
Ø It returns ‘n’ truncated to ‘m’
decimal places.
Ø If ‘m’ is omitted, ‘n’ is truncated
to 0 decimal places.
Ø ‘n’ can be negative to truncate ‘m’
digits left of the decimal point.
Ex:
- Select 15.79 num1,
trunc(15.79,1) truncated from dual;
- Select 15.79 num1,
trunc(15.79,-1) truncated from dual;
- Select 15.79 num1,
trunc(45.923,2) truncated, trunc(42.923) truncated, trunk(45.923,-1)
truncated from dual;
Ceil Function:
Syntax: Ceil(n)
Ø Returns the smallest integer greater
than or equal to ‘n’.
Ø The adjustment is done to the
highest nearest decimal value.
Ex:
- Select 15.7 num1,ceil(15.7)
ceiled from dual;
- Select 14.27 num1, ceil(14.27)
ceiled, ceil(14.2) ceiled, ceil(14) ceiled, ceil(0.22) ceiled from dual;
Floor Function:
Syntax: Floor(n)
Ø Returns the largest integer less
than or equal than ‘n’.
Ø The adjustment is done to the lowest
nearest decimal values.
Ex:
- Select 15.7 num1, floor(15.7)
floor from dual;
- Select 14.27 num1, floor(14.27)
floor, floor(14.2) floor, floor(14) floor, floor(0.002) floor from dual;
Modulus Function:
Syntax: MOD(m,n)
Ø It returns remainder of ‘m’ divided
by ‘n’.
Ø It returns ‘m’ if ‘n’ is 0.
Ex:
- Select mod(11,4), mod(10,2)
from dual;
Power Function:
Syntax: power(m,n)
Ø Returns ‘m’ raised to the ‘n’th
power.
Ø The base ‘m’ and the exponent ‘n’
can be any numbers.
Ø If ‘m’ is negative, ‘n’ must be an
integer.
Ex:
- Select power(3,2), power(-3,2)
from dual;
- Select power(3,-2),
power(-3,-2) from dual;
- Select power(-3.5,-2),
power(3,-2.5) from dual;
- Select power(-3.5,2.5),
power(3.5,-2.5) from dual;
Square Root Function:
Syntax: Sqrt(n)
Ø It returns square root of ‘n’ as
real value.
Ø The value of ‘n’ cannot be negative.
Ex:
- Select sqrt(25) from dual;
Absolute Function:
Syntax: ABS(n)
Ø It returns the absolute value of
‘n’.
Ex:
- Select abs(-15) from dual;
- Select sal, comm, sal-comm,
abs(sal-comm) from emp where comm = 1400;
Sign function:
Syntax: sign(n)
Ø It returns the sign specification of
a number.
Ø If n < 0, Returns -1
Ø If n = 0, Returns 0
Ø If n < 0, Returns 1
Ex:
- Select sign(-15), sign(15),
sign(0) from dual;
- Select sal, comm,
sign(sal-comm) from emp where sign(sal-comm) = -1;