Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-9. Rounding a Number

Problem

You are interested in rounding a given number. For example, let's say you are working on employee timecards, and you want to round to the nearest tenth of an hour for every given hour amount.

Solution

Use the Oracle built-in ROUND function to return the result that you desire. For this solution, you are working with hours on employee timecards. To round to the nearest tenth, you would write a small PL/SQL function that uses the ROUND function and returns the result. The following example demonstrates this technique:

CREATE OR REPLACE FUNCTION emp_labor_hours(time IN NUMBER) RETURN NUMBER IS BEGIN   RETURN ROUND(time, 1); END;

The time will be rounded to the nearest tenth in this example because a 1 is passed as ...