how to perform oracle ceil with decimal points-CodePudding

In round function we can use (4,512,1) but the same is not true for ceil and floor. I want to ceil and my floor the value 4,512 as 4,5 and 4,6 but the decimal point 1 is not always constant. it may vary. For Floor with decimal points the trunc function can be used. Is there a way to perform ceil with decimal points ?

CodePudding user response：

Adapting how the round function is defined (for positive numbers):

ROUND(n, integer) = FLOOR(n * POWER(10, integer)   0.5) * POWER(10, -integer)

... for a general case of a variable ceiling you might want something like:

ceil(n * power(10, integer)) * power(10, -integer)

So you could define your own functions:

create or replace function my_ceil(p_number number, p_decimals pls_integer)
return number as
begin
  return ceil(p_number * power(10, p_decimals)) * power(10, -p_decimals);
end;
/

create or replace function my_floor(p_number number, p_decimals pls_integer)
return number as
begin
  return floor(p_number * power(10, p_decimals)) * power(10, -p_decimals);
end;
/

Then with some sample data:

with t (n) as (
  select 4.512 from dual union all
  select 5.12345 from dual union all
  select 6 from dual union all
  select 0 from dual union all
  select -1.23 from dual
)
select n, 0 as d, my_ceil(n, 0) as my_ceil, my_floor(n, 0) as my_floor from t
union all
select n, 1 as d, my_ceil(n, 1), my_floor(n, 1) from t
union all
select n, 2 as d, my_ceil(n, 2), my_floor(n, 2) from t
union all
select n, 3 as d, my_ceil(n, 3), my_floor(n, 3) from t
union all
select n, 4 as d, my_ceil(n, 4), my_floor(n, 4) from t
order by n, d

you get:

N	D	MY_CEIL	MY_FLOOR
-1.23	0	-1	-2
-1.23	1	-1.2	-1.3
-1.23	2	-1.23	-1.23
-1.23	3	-1.23	-1.23
-1.23	4	-1.23	-1.23
0	0	0	0
0	1	0	0
0	2	0	0
0	3	0	0
0	4	0	0
4.512	0	5	4
4.512	1	4.6	4.5
4.512	2	4.52	4.51
4.512	3	4.512	4.512
4.512	4	4.512	4.512
5.12345	0	6	5
5.12345	1	5.2	5.1
5.12345	2	5.13	5.12
5.12345	3	5.124	5.123
5.12345	4	5.1235	5.1234
6	0	6	6
6	1	6	6
6	2	6	6
6	3	6	6
6	4	6	6

db<>fiddle

You might need to look at negative values to check they behave as you expect/want, and adjust the functions to mimic round if necessary. You also said the decimal might be zero, 1 or more; if that could be negative then it will need more work...

CodePudding user response：

CEIL and FLOOR return the closest integer and don't accept any arguments (but the number itself).

So, if you want to do that up to the 1st decimal, first multiply the number by 10, CEIL/FLOOR that value, and then divide it by 10.

Something like this:

SQL> with test (col) as (select 4.521 from dual)
  2  select col,
  3         --
  4         ceil(col * 10) / 10 c_ceil,
  5         floor(col * 10) / 10 c_floor
  6  from test;

       COL     C_CEIL    C_FLOOR
---------- ---------- ----------
     4,521        4,6        4,5

SQL>