Oracle round to nearest decimal places

I am trying to find out if there is any function in oracle that will round off the decimal places, we have a column called price precision and users will enter a value like 0.1 and in calculations the programmer needs to use this value when he does units times unit price and then round off to nearest price precision number

for example let us say the units are 2.22 and unit price is 2 and precision price column is 0.1 then
unit times unit price is 4.44 and by having precision column as 0.1 the result should be 4.50
Who is Participating?
joebednarzConnect With a Mentor Commented:
I think you outlined what you need in the function already...

FUNCTION customRound( initValue NUMBER, precision NUMBER )
  v_return NUMBER;
   WHEN precision = .1THEN
      v_return := initValue;

      -- some math functions, etc...

   WHEN precision = .01 THEN
      v_return := initValue;

      -- some math functions, etc...

      v_return := initValue;

  RETURN v_return;


How you fill in the "math functions" will be determined by your own rules and needs...
No rounding function that is built into a database product will likely give you 4.50 from 4.44.  Basic rounding functions will give you 4.4.  Apart from that, there should be a round() and trunc() function, where the truncate function never 'rounds up' but merely 'chops off'.  The round() function would round 4.46 to 4.5 however, but I doubt it would add the 0 at the end.

I would suggest writing a user defined function to do what you want to do instead.  It's a fairly simple function that would take two parameters, one for the precision to round to, the second for the precision to return from the function

float specialround(float input, int precision, int returnprecision)

Dealing with floats in databases can be dangerous though - and you may want to deal with numbers as integers (decimals) with a defined decimal place - where the database internally stores the numbers as '446' with a column definition of decimal(10,2), for example.

Floating point precision can end up screwing you if you don't handle the numbers carefully.
I think it will involve a special case function that you always return for your price.  Create your function as rgautier suggested, then create a view on the table that returns:

SELECT special_fn( unit_price_column, precision_column ) returnValue, unit_price_column, precision_column, <other_columns>
FROM your_table;

I would then grant SELECT on the view to your developers and revoke select on the table, so no one gets confused or incorrect data.
mahjagAuthor Commented:
I see there is no inbuilt oracle function for this to accomplish, coud you guys outline what the special round function will do?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.