Numeric functions in oracle

Numeric Functions: - These functions work on numeric data types. We can pass any column with numeric data type as arguments in these functions. In some functions we require positive value, for that we use ABS function combined with other functions.

To study these functions we take “product” table as example.

Selecting product table



They are divided as below:

1. ABS:- This function returns absolute value, it means it converts negative value to positive value.

Syntax
ABS(n)

In above syntax “n” is any constant value or column with numeric value.

Use of ABS function



2. SQRT:- This function returns square root of any value, but value cannot be negative. If value “n” is negative then first use ABS function to convert it into positive value.

Syntax
SQRT(n)

In above syntax “n” is any constant value or column with numeric value.

Use of SQRT function



In above query we have used ABS function to first convert value to positive then calculate square root of positive value.

3. POWER:- This function returns value raised by another value.

Syntax
POWER(m,n)

In above syntax value “m” is raised by “n” where “m” and “n” is constant or value of any column.

Use of POWER function



4. MOD:- This function will returns remainder when a value divided by another value.

Syntax
MOD(m,n)

In above syntax we get remainder after “m” is divided by “n” where “m” and “n” is constant or value of any column.

Use of MOD function



5. CEIL:- This function will returns greater integer value than the value passed in argument.

Syntax
CEIL(n)

In above syntax “n” is any constant value or column with numeric value.

Use of CEIL function



6. FLOOR:- This function will returns smaller integer value than the value passed in argument.

Syntax
FLOOR(n)

In above syntax “n” is any constant value or column with numeric value.

Use of FLOOR function



7. ROUND:- This function will round first argument up to second argument after decimal point.

Syntax
ROUND(m,n)

In above syntax value “m” is rounded up to “n” places to the right of decimal point where “m” and “n” is constant or value of any column.

Use of ROUND function



8. TRUNC:- This function will truncate first argument up to second argument after decimal point.

Syntax
TRUNC(m,n)

In above syntax value “m” is truncate up to “n” places to the right of decimal point where “m” and “n” is constant or value of any column.

If “n” is positive then m is truncated to n places to the right of decimal point.
If “n” is zero or not used then m is truncated to zero.
If “n” is negative then m is truncated to n places to the left of decimal point.

Use of TRUNC function



9. EXP:- This function will raised “e” by first argument value where “e” = 2.071828183.

Syntax
EXP(n)

In above syntax “n” is any constant value or column with numeric value.

Use of EXP function



10. LN:- This function will calculate logarithm value of argument with base “e”.

Syntax
LN(n)

In above syntax “n” is any constant value or column with numeric value.

Use of LN function



In above query we have used ABS function to first convert value to positive then calculate Logarithm of positive value.

11. LOG:- This function will calculate logarithm value of second argument with base as first argument.

Syntax
LOG(m,n)

In above syntax we calculate logarithm of n with base value b where “m” and “n” is any constant value or column with numeric value.

Use of LOG function



In above query we have used ABS function to first convert value to positive then calculate Logarithm of positive value.

12. SIGN:- This function will return 1 for positive value, 0 for zero and -1 for negative value.

Syntax
SIGN(n)

In above syntax “n” is any constant value or column with numeric value.

Use of SIGN function



I hope this article will help you