Oracle ROUND Function (Number): A Simple Guide

The ROUND function in Oracle SQL is one of the most common mathematical functions. It is used to round a number to a specified number of decimal places.

You can use it to round to the nearest whole number, to a specific number of places after the decimal point, or even to the left of the decimal point (e.g., to the nearest 10 or 100).

What is the ROUND Function in Oracle?

The ROUND(n, integer) function answers the question: "What is the number n rounded to integer decimal places?"

• If integer is positive, it rounds to the right of the decimal point.
• If integer is negative, it rounds to the left of the decimal point.
• If integer is omitted, it defaults to 0 and rounds to the nearest whole number.

The function follows standard rounding rules: .5 and higher rounds up (away from zero), and anything less than .5 rounds down (toward zero).

ROUND Function Syntax

The syntax for ROUND is as follows:

ROUND(n [, integer])

Let's break that down:

• n (the number): The number you want to round.
• integer (optional): The number of decimal places to round to. This must be an integer.
  • If omitted, it defaults to 0.

Oracle ROUND Function Examples

Here are two practical examples of how to use ROUND.

Example 1: Rounding to a Decimal Place using ROUND

This example rounds a number to one decimal place.

Query:

SELECT 
  ROUND(15.193, 1) AS "Round_1_Place",
  ROUND(15.143, 1) AS "Round_Down"
FROM DUAL;

Result:

Round_1_Place Round_Down
------------- ----------
         15.2       15.1

In the first case, .19 rounds up to .2. In the second, .14 rounds down to .1.

Example 2: Rounding to the Left of the Decimal (Negative Integer) using ROUND

This example rounds a number to the nearest "tens" place by using -1 as the integer.

Query:

SELECT 
  ROUND(117.25, -1) AS "Round_to_Tens",
  ROUND(112.99, -1) AS "Round_Down_Tens"
FROM DUAL;

Result:

Round_to_Tens Round_Down_Tens
------------- ---------------
          120             110

117.25 is closer to 120, and 112.99 is closer to 110.