Below are the numeric functions in Oracle with detailed explanations and examples:
1. ABS: Returns the absolute value of a number.
SELECT ABS(-15) AS abs_value FROM DUAL; -- Returns 15
SELECT ABS(15) AS abs_value FROM DUAL; -- Returns 15
SELECT ABS(0) AS abs_value FROM DUAL; -- Returns 0
2. CEIL: Returns the smallest integer greater than or equal to a number.
SELECT CEIL(3.7) AS ceil_value FROM DUAL; -- Returns 4
SELECT CEIL(-3.7) AS ceil_value FROM DUAL; -- Returns -3
SELECT CEIL(3) AS ceil_value FROM DUAL; -- Returns 3
3. FLOOR: Returns the largest integer less than or equal to a number.
SELECT FLOOR(3.7) AS floor_value FROM DUAL; -- Returns 3
SELECT FLOOR(-3.7) AS floor_value FROM DUAL; -- Returns -4
SELECT FLOOR(3) AS floor_value FROM DUAL; -- Returns 3
4. ROUND: Rounds a number to a specified number of decimal places.
SELECT ROUND(123.456, 2) AS rounded_value FROM DUAL; -- Returns 123.46
SELECT ROUND(123.456, 0) AS rounded_value FROM DUAL; -- Returns 123
SELECT ROUND(123.456, -1) AS rounded_value FROM DUAL; -- Returns 120
5. TRUNC: Truncates a number to a specified number of decimal places.
SELECT TRUNC(123.456, 2) AS truncated_value FROM DUAL; -- Returns 123.45
SELECT TRUNC(123.456, 0) AS truncated_value FROM DUAL; -- Returns 123
SELECT TRUNC(123.456, -1) AS truncated_value FROM DUAL; -- Returns 120
6. MOD: Returns the remainder of a division operation.
SELECT MOD(10, 3) AS mod_value FROM DUAL; -- Returns 1
SELECT MOD(10, -3) AS mod_value FROM DUAL; -- Returns 1
SELECT MOD(-10, 3) AS mod_value FROM DUAL; -- Returns -1
7. POWER: Raises a number to the power of another number.
SELECT POWER(2, 3) AS power_value FROM DUAL; -- Returns 8
SELECT POWER(10, 2) AS power_value FROM DUAL; -- Returns 100
SELECT POWER(5, -1) AS power_value FROM DUAL; -- Returns 0.2
8. SQRT: Returns the square root of a number.
SELECT SQRT(16) AS sqrt_value FROM DUAL; -- Returns 4
SELECT SQRT(2) AS sqrt_value FROM DUAL; -- Returns 1.414213562
9. EXP: Returns e raised to the power of a number.
SELECT LN(1) AS ln_value FROM DUAL; -- Returns 0
SELECT LN(10) AS ln_value FROM DUAL; -- Returns 2.302585093
SELECT LN(EXP(1)) AS ln_value FROM DUAL; -- Returns 1
10. LN: Returns the natural logarithm of a number.
SELECT LN(1) AS ln_value FROM DUAL; -- Returns 0
SELECT LN(10) AS ln_value FROM DUAL; -- Returns 2.302585093
SELECT LN(EXP(1)) AS ln_value FROM DUAL; -- Returns 1
11. LOG: Returns the logarithm of a number to a specified base.
SELECT LOG(10, 100) AS log_value FROM DUAL; -- Returns 2
SELECT LOG(2, 8) AS log_value FROM DUAL; -- Returns 3
SELECT LOG(10, 10) AS log_value FROM DUAL; -- Returns 1
12. SIGN: Returns the sign of a number (-1 for negative, 0 for zero, 1 for positive).
SELECT SIGN(-15) AS sign_value FROM DUAL; -- Returns -1
SELECT SIGN(0) AS sign_value FROM DUAL; -- Returns 0
SELECT SIGN(15) AS sign_value FROM DUAL; -- Returns 1
13. GREATEST: Returns the greatest value in a list of expressions.
SELECT GREATEST(1, 3, 5, 7, 9) AS greatest_value FROM DUAL; -- Returns 9
SELECT GREATEST(-1, -3, -5, -7, -9) AS greatest_value FROM DUAL; -- Returns -1
14. LEAST: Returns the least value in a list of expressions.
SELECT LEAST(1, 3, 5, 7, 9) AS least_value FROM DUAL; -- Returns 1
SELECT LEAST(-1, -3, -5, -7, -9) AS least_value FROM DUAL; -- Returns -9
These numeric functions in Oracle can help perform various calculations and manipulations on numerical data, making them extremely useful for data processing and analysis tasks.
Tags:
Oracle