Oracle SQL - Function ROUND function

Introduction

ROUND Returns the number rounded to nearest value (precision adjustable).

The near value functions will round off a value in different ways.

Demo

SQL>
SQL> drop table my_table;

Table dropped.--   w  w  w.j  a  v  a  2 s .  com
SQL> CREATE TABLE my_table (
  2      LINENO NUMBER(2) not null,
  3      VALUE NUMBER(6,2) not null
  4  );

Table created.
SQL>
SQL> insert into my_table values(1,1.2);
SQL> insert into my_table values(2,123.34);
SQL> insert into my_table values(3,-12.2);
SQL> insert into my_table values(4,100);
SQL> insert into my_table values(5,48);
SQL> insert into my_table values(6,-90);
SQL> insert into my_table values(7,0.19);
SQL>
SQL> SELECT lineno, value, ROUND(value), TRUNC(value), CEIL(value),
  2  FLOOR(value)
  3  FROM my_table;

    LINENO      VALUE ROUND(VALUE) TRUNC(VALUE) CEIL(VALUE) FLOOR(VALUE)
---------- ---------- ------------ ------------ ----------- ------------
         1        1.2            1            1           2            1
         2     123.34          123          123         124          123
         3      -12.2          -12          -12         -12          -13
         4        100          100          100         100          100
         5         48           48           48          48           48
         6        -90          -90          -90         -90          -90
         7        .19            0            0           1            0

7 rows selected.
SQL>

ROUND will convert 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 appropriate absolute value of the negative number; e.g., ROUND(-6.8) = -7.

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

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,1), TRUNC(value,1)
  2  FROM my_table
  3-- from   w  w  w . ja va  2s .c o  m

The second argument defaults to 0.

The following query may be compared with previous versions, which have no second argument:

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,0), TRUNC(value,0)
  2  FROM my_table;

   LINENO |     VALUE | ROUND(VALUE,0) | TRUNC(VALUE,0)
--------- | --------- | -------------- | --------------
 00001.00 |  00001.20 |       00001.00 |       00001.00
 00002.00 |  00123.34 |       00123.00 |       00123.00
 00003.00 | -00012.20 |      -00012.00 |      -00012.00
 00004.00 |  00100.00 |       00100.00 |       00100.00
 00005.00 |  00048.00 |       00048.00 |       00048.00
 00006.00 | -00090.00 |      -00090.00 |      -00090.00
 00007.00 |  00000.19 |       00000.00 |       00000.00

7 rows selected.-- from w  w w .  ja v  a  2  s. c om

SQL>

The second argument, precision, may be negative, which means displacement to the left of the decimal point:

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,-1), TRUNC(value,-1)
  2  FROM my_table;

   LINENO |     VALUE | ROUND(VALUE,-1) | TRUNC(VALUE,-1)
--------- | --------- | --------------- | ---------------
 00001.00 |  00001.20 |        00000.00 |        00000.00
 00002.00 |  00123.34 |        00120.00 |        00120.00
 00003.00 | -00012.20 |       -   1     |       -00010.00
 00004.00 |  00100.00 |        00100.00 |        00100.00
 00005.00 |  00048.00 |        00050.00 |        00040.00
 00006.00 | -00090.00 |       -00090.00 |       -00090.00
 00007.00 |  00000.19 |        00000.00 |        00000.00

7 rows selected.-- from   ww w.ja  va  2 s  .c  om

SQL>

In this example, with -1 for the precision argument, values less than 5 will be truncated to 0, and values of 5 or greater will be rounded up to 10.

• Previous
• Next