Oracle ROUND(number) Function

Oracle ROUND(number) is a built-in function that rounds a given number to a given number of decimal places.

Oracle ROUND(number) syntax

Here is the syntax for the Oracle ROUND(number) function:

ROUND(number[, d])

Parameters

number

Required. Numbers that need to be rounded.

d

Optional. The number of decimal places to keep. The default value is 0. It can be any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

Return Value

The Oracle ROUND(number) function returns a rounded number. It is implemented using the following rules:

1. If number is 0, then ROUND always returns 0, regardless whatever d is.
2. If number is negative, then ROUND(number, d) returns -ROUND(-number, d).
3. If number is positive, then ROUND(number, d) equals to FLOOR(number * POWER(10, d) + 0.5) * POWER(10, -d).

If any parameter is NULL, ROUND(number) will return NULL.

Oracle ROUND(number) Examples

Here are some examples that demonstrate the usage of the Oracle ROUND(number) function.

Basic Usage

SELECT
    ROUND(1.2345),
    ROUND(1.2345, 1),
    ROUND(1.2345, 3)
FROM dual;

Output:

   ROUND(1.2345)    ROUND(1.2345,1)    ROUND(1.2345,3)
________________ __________________ __________________
               1                1.2              1.235

Negative numbers

The Oracle ROUND(number) function allows you to provide a negative number for the second parameter to round the integer part.

SELECT
    ROUND(12345, -1),
    ROUND(12345, -2)
FROM dual;

Output:

   ROUND(12345,-1)    ROUND(12345,-2)
__________________ __________________
             12350              12300

NULL Parameters

If any parameter is NULL, ROUND() will return NULL.

SET NULL 'NULL';
SELECT
    ROUND(1, NULL),
    ROUND(NULL, 1),
    ROUND(NULL, NULL)
FROM dual;

Output:

   ROUND(1,NULL)    ROUND(NULL,1)    ROUND(NULL,NULL)
________________ ________________ ___________________
            NULL             NULL                NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle ROUND(number) is a built-in function that rounds a given number to a given number of decimal places.