Oracle Number Function - Oracle/PLSQL ROUND Function

This Oracle tutorial explains how to use the Oracle/PLSQL ROUND function applying to numeric values).

The Oracle/PLSQL ROUND function returns a number rounded to a certain number of decimal places.

ROUND converts a decimal value to the next highest absolute value if the value is 0.5 or greater. "Next highest absolute value" for negative numbers rounds to the negative value of the absolute value of the negative number; e.g., ROUND(-6.8) = -7.

Syntax

The syntax for the Oracle/PLSQL ROUND function is:

ROUND( number, [ decimal_places ] )

number is the number to round.

decimal_places is the number of decimal places rounded to. This value must be an integer, and it is default to 0 decimal places.

Example


SQL> select ROUND(1.1) from dual;
-- from  w  ww. j  ava 2  s.  c  o m
ROUND(1.1)
----------
         1

SQL> select ROUND(1.9) from dual;

ROUND(1.9)
----------
         2

SQL> select ROUND(-1.1) from dual;

ROUND(-1.1)
-----------
         -1

SQL> select ROUND(-1.9) from dual;

ROUND(-1.9)
-----------
         -2

SQL> select ROUND(0) from dual;

  ROUND(0)
----------
         0

SQL>

Decimal places

ROUND function may have a second argument to handle precision, which means the distance to the right of the decimal point. The second argument defaults to 0.


SQL> select ROUND(1.99999) from dual;
--  w  w  w .j a v  a 2  s .  c  om
ROUND(1.99999)
--------------
             2

SQL> select ROUND(1.99999,0) from dual;

ROUND(1.99999,0)
----------------
               2

SQL> select ROUND(1.9,1) from dual;

ROUND(1.9,1)
------------
         1.9

SQL> select ROUND(1.99,2) from dual;

ROUND(1.99,2)
-------------
         1.99

SQL> select ROUND(1.999,3) from dual;

ROUND(1.999,3)
--------------
         1.999

SQL>
SQL>

Minor decimal places

In addition, the second argument, precision, may be negative, which means displacement to the left of the decimal point.


SQL> select ROUND(19999.99999,0) from dual;
-- w  w w.  j  av  a2  s .  co  m
ROUND(19999.99999,0)
--------------------
               20000

SQL> select ROUND(9.9,-1) from dual;

ROUND(9.9,-1)
-------------
           10

SQL> select ROUND(19.99999,-2) from dual;

ROUND(19.99999,-2)
------------------
                 0

SQL> select ROUND(199.99999,-3) from dual;

ROUND(199.99999,-3)
-------------------
                  0

SQL>
SQL>