«Back to Home

Oracle Jump Start
Topics

How To Use Arithmetic function in Oracle

Arithmetic Functions Type

  • ABS(n)
  • CEIL(n)
  • FLOOR(n)
  • MOD(m, n)
  • SIGN(n)
  • SQRT(n)
  • TRUNC(m, [n])
  • ROUND(m, [n])
  • EXP(n)
ABS(n)

 

This function returns an absolute value of the column or the passed value.
 
Example
  1. Select ABS(-55)  
  2. from dual;  
1
 
CEIL(n)

This function finds the smallest integer greater than or equal to n. n can be a column name.

Example
  1. Select CEIL(salary) “CEIL(Salary)”  
  2. from Employee  
  3. where salary between 30000 to 50000;  
2

FLOOR(n)

This function finds the largest integer less than or equal to the value n. n can be either a column or an expression.

Example
  1. Select FLOOR(salary), CEIL(88.9)  
  2. from Employee  
  3. where salary Between 30000 AND 50000;  
3

MOD(m, n)

This function returns the reminder of m divided by n or m, if n = 0.

Example
  1. Select MOD(300, 45)  
  2. from dual;  
4
 
Power(m, n)

This function returns m raised to the power n. The second argument n must be an integer.

Example
  1. Select salary, power(salary, 2)  
  2. from employee  
  3. where dept_no = 104;  
5

SIGN(n)

This function returns -1, if n is negative. It returns 1, if n is positive and it returns 0, if n is 0.

Example
  1. Select salary, SIGN(salary)  
  2. from employee  
  3. where dept_no = 101;  
6

SQRT(n)

This function returns the square root of n. If the value n is NULL or negative, Null is returned.

Example
  1. Select Salary, SQRT(salary)  
  2. from employee  
  3. where dept_no = 105;  
7

TRUNC(m, [n])

This function truncates the m or column to n decimal places. If n is omitted, it is truncated to no decimal places. If n is negative, numbers left of decimal places are truncated to 0.

Example
  1. Select trunc(80.345, 1), trunc(80.345, -1), trunc(80.345)  
  2. from dual;  
8
  1. Select salary, TRUNC ( SQRT (salary),2)  
  2. From employee  
  3. Where dept_no = 103;  
9

ROUND(m, [n])

This function rounds the column, expression or value m to n decimal places. If n is omitted, it is round off. If n is a negative number to left, the number is round off.

Example
  1. Select ROUND(80.345, 1), ROUND(80.345, -1), ROUND(80.345)  
  2. from dual;  
10
  1. Select salary, ROUND(SQRT(salary), 2)  
  2. from employee  
  3. where dept_no=102;  
11

Exp(n)

This function returns e raised to the nth power.

Example

  1. Select EXP(4)  
  2. from dual;  
12 

Summary

Thus, we learnt, how to use arithmetic functions in Oracle with the examples.