1. What can we do with Numeric Functions ?
We can do the following:
(i) Make a negative no positive.
(ii) Get the sign of the Number.
(iii) Find the power of required no.
(iv) Find the Sqrt of a number.
(v) Get the remainder of 2 numbers.
(vi) Get the customized Apprx value.
(i) Make a negative number positive.
=> ABS( -10) 10
=> ABS( 20) 20
=> ABS( -30) 30
=> ABS( -10) 10
Mostly used in case of date functions.
Ex: Select ABS ( MONTHS_BETWEEN ( SYSDATE - HIREDATE ) ) from Emp;
(ii) Get the sign of the Number.
=> SIGN( 10 ) = 1;
=> SIGN( -10 ) = -1;
=> SIGN( 0 ) = 0;
(iii) Find the power of required no.
=> POWER( 3,2) = 9
=> POWER(12,2) = 144
=> POWER(101,0) = 1
=> POWER(2, -1) = 0.5
(iv) Find the Sqrt of a number.
=> SQRT( 4 ) = 2
=> SQRT( 16 ) = 4
=> SQRT( 9 ) = 3
(v) Get the remainder of 2 numbers.
=> MOD( 10,5 ) = 0
=> MOD( 10,3 ) = 1
=> MOD( 10,4 ) = 2
=> MOD( 10,7 ) = 3
(vi) Get the customized Apprx value.
CEIL:
=> CEIL( 10.1 ) = 11
=> CEIL( 9.9 ) = 10
FLOOR:
=> FLOOR( 10.1 ) = 10
=> FLOOR( 9.9 ) = 9
ROUND:
If the decimal lies in [0,4] then Oracle Uses FLOOR function.
If the decimal lies in [5,9] then Oracle Uses CEIL function.
=> ROUND( 101.2 ) = 101
=> ROUND( 101.234, 2) = 101.23
=> ROUND( 101.2,-2 ) = 100
=> ROUND( 101.2,-3 ) = 0
=> ROUND( 103.73 ) = 104
If MODE = 'year', date lies in [JAN - JUN] then Oracle sets to "01-JAN of current year".
date lies in [JUL - DEC] then Oracle sets to "01-JAN of next year".
If MODE = 'month', date lies in [01 - 15] then Oracle sets to "01-current month".
date lies in [16 - end] then Oracle sets to "01-next month".
If MODE = 'day', date lies in [SUNDAY - WEDNESDAY] then Oracle sets to "Current Sunday of the Week".
date lies in [THRUSDAY-SUNDAY] then Oracle sets to "Next Sunday of the Week".
=> ROUND( '12-FEB-12', 'year') = 01-JAN-12
=> ROUND( '12-OCT-12', 'year') = 01-JAN-13
=> ROUND( '30-JUN-12', 'year') = 01-JAN-12
=> ROUND( '01-JUL-12', 'year') = 01-JAN-13
=> ROUND( '12-FEB-12', 'month') = 01-FEB-12
=> ROUND( '18-OCT-12', 'month') = 01-NOV-12
=> ROUND( '15-JUN-12', 'month') = 01-JUN-12
=> ROUND( '16-JUN-12', 'month') = 01-JUL-12
=> ROUND( '13-FEB-12', 'day') [monday] = 12-FEB-12
=> ROUND( '17-FEB-12', 'day') [friday] = 19-FEB-12
=> ROUND( '12-FEB-12', 'day') [sunday] = 12-FEB-12
TRUNC:
=> TRUNC(10.2) = 10
=> TRUNC(10.234,2) = 10.23
=> TRUNC(1123.923,-3) = 1000
If MODE = 'year', Oracle sets date to "01-JAN- current year"
If MODE = 'month', Oracle sets date to "01-current month"
If MODE = 'day', Oracle sets date to "starting day of the current week"
=> TRUNC(to_date('31-DEC-12'),'year') = 01-JAN-12
=> TRUNC(to_date('30-DEC-12'),'month') = 01-DEC-12
=> TRUNC(to_date('14-FEB-12'),'day') = 12-FEB-12