How To Use ROUND In Oracle SQL

This help is based on examples so it would be easier to understand. Oracle Round function allows to round a number value to a “N” places up before decimal place or after it. The syntax of Oracle Round is following:

ROUND (<number> [,<rounding_integer>]);

The first Oracle Round example has input value 12.3333333 and it will round the number up to the decimal place and the function will return an integer value (number 12). This way works the Oracle rounding function when the “rounding integer” has left out and it does remove all numbers after decimal place by default mode.

 SELECT ROUND (12.3333333) AS rounded
   FROM dual;

To leave some numbers after the decimal place use a positive number in the “rounding integer” place. The following example has the same input number as on the last example and the rounding integer is set 2. The output shows the 12.33 value that is exactly two numbers after the decimal place.

 SELECT ROUND (12.3333333,2) AS rounded
   FROM dual;

To round up before the decimal place use a negative number in the “rounding integer” place. This example we will round only 1 place up of value 12.3333333 and the output value has become number 10. If the input values would have been 16 then the output would have number 20 now.

 SELECT ROUND (12.3333333,-1) AS rounded
   FROM dual;

The following example has almost the same query as the last one – only this time we will round up 2 places before decimal place. The “rounding integer” has set “-2” and the number 12.3333333 has become 0.

 SELECT ROUND (12.3333333,-2) AS rounded
   FROM dual;

The SQL query below does rounding with a dynamic value and it comes from pseudo-column named “Rownum“. Oracle Rownum value grows with every next line and that since it has been used as “rounding integer” than leaves more decimal places to available with every next line until it becomes a full value.

 SELECT ROUND (12.3333333,rownum) AS rounded
   FROM dual
    CONNECT BY rownum < 8;

The output shows how the rounding leaves more number available after the decimal place with growing Oracle Rownum value.

See Also:
Oracle Select Oracle Rownum Oracle Home