ROUND ORACLE SQL - Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle Round function allows rounding a number value to “x” places up after or before the decimal point. The Oracle Round syntax is following:

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

This Oracle Round example has input value 12.3333333 and it will round the number up to a decimal place and it returns an integer value (number 12). This way rounding is done when the “rounding integer” has left out and by default the Round function removes all numbers after decimal place.

 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.