By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
/
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
| 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 |