set serveroutput on;
declare
l_pies number := 0.42;
l_serving_size number := 1/8;
l_servings_left number;
l_approximate_pies number;
begin
l_servings_left := trunc(l_pies/l_serving_size);
dbms_output.put_line('Still ' || l_servings_left || ' pieces of pie');
l_approximate_pies := floor(l_pies/l_serving_size) * l_serving_size;
dbms_output.put_line('Based on servings, there are ' || l_approximate_pies || ' pies left');
end;
/- Getting Started
- Rounding To The Nearest Increment
- Creating A Normal Function
- Converting to a Scalar Macro
- Comparing functions to scalar macros in SQL
- Note: Round Function uses increments of powers of 10
- Incremental Rounding: Upwards
- Incremental Rounding: Always Down
- Incremental Rounding: Toward Zero
- Example: Making Change
- Incremental Rounding: Away From Zero
- Unit Testing Functions and Macros
- Unified Incremental Rounding Functionality: To_Increments
- Summary
In the real world, rounding a value does not always mean rounding to powers of 10. We round time to increments of 5 or 10 minutes, pies to pieces, shop for eggs in dozens even if we only need 7, estimate inches to the nearest 1/8 or 1/16. If pennies go out of circulation we have to round prices to the nearest nickel or tuppence. Given the current prices of espresso, it helps to round prices to the nearest 7 dollars when budgeting for a trip to the coffeeshop with a group.
In Oracle, the round function will round numbers, but only to different powers of 10. We can round to thousands, hundreds, tens, integers, tenths, hundredths, thousandths; however, there is no built in function to round a number to arbitrary increments. On analysis, rounding to increments follows an algorithm that can be applied to any type of rounding: rounding up at halfway, always rounding downwards, always rounding upwards, rounding toward zero or rounding away from zero.
Since there is no builtin function available that implements this algorithm, we couold create a pl/sql function to provide this functionality. In this post we will look at the process of creating functions to round by increments, as well as creating sql macro functions.
Getting Started
The source code for all articles here on practicalplsql.org is available in the practicalplsql public repository on github in the scripts/articles directory organized by concept and article. You can use create.user.practicalplsql.sql to create the practicalplsql schema. Sample data for articles can be found in the scripts/examples directory organized by example name.
Scripts for this post are located in sql-macros/scalar-macros/incremental-rounding-numbers
Rounding To The Nearest Increment
Rounding a number to the nearest increment is a three step process: a) divide the number by the size of the increment, b) apply a rounding method, c) multiply this rounded value by the increment size for the final result. The result will always be a multiple of the increment sze. We can review a few simple examples to be sure this logic is correct.
Rounding 8 in increments of 5 would be 10: a) 8 divided by 5 is 1.6, b) 1.6 rounds to 2, c) 2 increments of 5 equals 10.
Rounding 18 in increments of 12 would be 24: a) 18 divided by 12 is 1.5, b) 1.5 rounds up to 2, c) 2 multiplied by 12 equals 24.
Rounding 80 in increments of 25 is 75: a) 80 divided by 25 is 3.2, b) 3.2 rounds down to 3, c) 3 multipled by 25 equals 75.
Rounding 1.3 in increments of 0.5 is 1.5: a) 1.3 divided by 0.5 is 2.6, b) 2.6 rounds up to 3, c) 3 multiplied by 0.5 is 1.5.
Applying this logic in Pl/sql or SQL makes it easy to see that the logic is correct. We can also verify that the logic works for negative numbers.
--1.0-round-increments-examples.sql
set serveroutput on;
declare
procedure test(n in number, i in number)
is
begin
dbms_output.put('round ' || n || ' in increments of ' || i || ' = ');
dbms_output.put_line( round( n/i ) * i );
end test;
begin
test(8.5, 5);
test(13, 5);
test(0.42, 0.25);
test(1.08, 0.25);
test(-8.5, 5);
test(-13, 5);
test(-0.42, 0.25);
test(-1.08, 0.25);
end;
/
round 8.5 in increments of 5 = 10
round 13 in increments of 5 = 15
round .42 in increments of .25 = .5
round 1.08 in increments of .25 = 1
round -8.5 in increments of 5 = -10
round -13 in increments of 5 = -15
round -.42 in increments of .25 = -.5
round -1.08 in increments of .25 = -1
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25),
(-8.5, 5), (-13, 5), (-0.42, 0.25), (-1.08, 0.25)
)
select
n as "number"
, i as "increment"
, round( n/i ) * i as "result"
from base
/
number increment result
---------- ---------- ----------
8.5 5 10
13 5 15
.42 .25 .5
1.08 .25 1
-8.5 5 -10
-13 5 -15
-0.42 .25 -0.5
-1.08 .25 -1The above examples repeat the basic incremental rounding algorithm with every use. This results in code that may be cryptic to anyone not familiar with this logic. We may also forget the formula after not using it for a while and possibly get it mixed up sometimes. The solution is simple: encapsulate the logic in a function.
Creating A Normal Function
Putting the logic into a function makes it easy to understand the intent of any code that calls the function, and assures us that the logic will always be correctly implemented.
--1.1-round-increments-function.sql
create or replace function round_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return round( p_value/p_increment ) * p_increment;
end round_increments;
/Now that this logic is implemented as a function we can reuse it anwhere in our code without needing to remember the exact logic. We can also call the function from SQL directly, but there will always be a performance hit from calling a PL/SQL function. Adding the pragma UDF to the function makes the situation a little better, but it’s still not optimal.
Converting to a Scalar Macro
Since Oracle 21, we can rewrite a simple function like this as a scalar macro. The macro will substitute the correct logic into the SQL statement instead of calling a Pl/sql function from SQL. The macro cannot be used in Pl/sql, so we end up with two nearly identical functions to encapsulate this logic for both SQL and Pl/sql usage with no performance penalties. Naming the macro form with a suffix to represent the macro makes it easy to see which form to call from SQL.
--1.2-round-increments-macro.sql
create or replace function round_increments_sqm(
p_value in number
, p_increment in number
) return varchar2 sql_macro(scalar)
is
begin
return 'round( p_value/p_increment ) * p_increment';
end round_increments_sqm;
/Comparing functions to scalar macros in SQL
We can look at trace files for the final sql after transformation to see the actual SQL statement that is executed when a scalar macro is called. A quick way to see how a macro function compares to regular function in SQL is to use the function in the where clause as a predicate. Then we can examine the predicates section of the explain plan to see if the function was called.
Lets create a table to see this in action:
--1.4-compare-function-to-macro.sql
create table if not exists test_increments as
with base(n, i) as (
values
(1.217, 1/4), (1.08, 1/4)
, (13, 5), (11, 5)
, (19, 12), (14, 12)
)
select
n as "number"
, i as "increment"
from base;Looking a the predicates section of the explain plan when the macro is used to filter a query shows that the macro function was called during actual query execution, the logic has been implemented completely in SQL.
explain plan for
select * from test_increments
where round_increments_sqm("number","increment") = 10;
select *
from dbms_xplan.display ( format => 'BASIC +PREDICATE' );
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST_INCREMENTS |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROUND("number"/"increment")*"increment"=10)If we use the regular function in SQL, we can see that the function will be called to apply the predicate. There is a cost here of switching from the context of the SQL engine to the Pl/sql engine during the execution of the SQL statement for every row.
alter session set sql_transpiler = off;
explain plan for
select * from test_increments
where round_increments("number","increment") = 10;
select *
from dbms_xplan.display ( format => 'BASIC +PREDICATE' );
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST_INCREMENTS |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROUND_INCREMENTS"("number","increment")=10)In Oracle 23ai, the sql transpiler can convert simple Pl/sql functions to pure SQL when the SQL statement is compiled, removing the need to call the function during the execution of the SQL statement. Successful transpilation is basically equivalent to using a scalar sql macro. At this time there are many restrictions for successful transpilation. It is not listed in the restrictions, but compiling with pragma UDF to inform the SQL engine that this is a Pl/sql function that will be called from SQL will actually prevent the transpiler from working.
create or replace function round_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return round( p_value/p_increment ) * p_increment;
end round_increments;
/
alter session set sql_transpiler = on;
explain plan for
select * from test_increments
where round_increments("number","increment") = 10;
select *
from dbms_xplan.display ( format => 'BASIC +PREDICATE' );
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST_INCREMENTS |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROUND_INCREMENTS"("number","increment")=10)If we recompile the function without pragma udf, transpilation is successful. We can see that the predicates no longer include a call to the function, just like when we used the sql macro.
create or replace function round_increments(
p_value in number
, p_increment in number
) return number
is
begin
return round( p_value/p_increment ) * p_increment;
end round_increments;
/
alter session set sql_transpiler = on;
explain plan for
select * from test_increments
where round_increments("number","increment") = 10;
select *
from dbms_xplan.display ( format => 'BASIC +PREDICATE' );
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST_INCREMENTS |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROUND("number"/"increment")*"increment"=10)Transpilation for simple functions like this allows the creation of a single function to implement logic that is equally useable in Pl/sql or SQL with no efficiency penalties. In general this promises that we can leverage the performance gains of sql macros without changing existing code or creating two forms of functionality that mirror the same functionality between SQL and Pl/sql.
The restriction on functions compiled with pragma UDF will require the functions that we already know are frequently used in SQL to be recompiled without this pragma. Hopefully this situation will change as 23AI comes into broader use.
At present there are many limitations to the sql transpiler, but future versions should expand the situations where this optimization is available.
Note: Round Function uses increments of powers of 10
The built in round function accepts a numeric value and an integer argument. Using round(n, integer) will keep integer digits to the right of the decimal point when integer is positive. When integer is negative, rounding eliminates integer digits to the left of the decimal point, leaving zeroes.
This is equivalent to the concept of scale when defining a fixed point number. Positive scale is how many digits to the right of the decimal point can be assigned to the number without rounding. Negative scale is the same as the negative integer argument for round, representing how many zeros will be to the left of the decimal point.
The round function documentation is stated in terms of digits to the left or the right of the decimal point, but this is somewhat confusing when trying to teach this function to others, especially the functionality with a negative argument. In my opinion, looking at rounding as incremental rounding to different powers of ten is more clear.
Round(1.234, 2) will keep 2 digits to the right of the decimal point, returning 1.23. The answer has been rounded to one and twenty three hundredths. Instead of viewing 2 as the number of digits to the right of the decimal point to keep, we can see that the integer 2 here represents rounding in increments of 1/100. Round(1.234, 2) is equivalent to incremental_round(1.234, 1/100). 1/100 is ten to the negative second power. The integer argument to the round function, multiplied by negative one, represents the power of ten to be used as an increment for the rounding operation.
Round(1234.5, -2) will round to hundreds, returning 1200. The answer has been rounded to twelve hundreds. Instead of viewing -2 as the number of digits to be rounded on the left of the decimal point, we can see that -2 represents rounding in increments of 100.
The integer argument for the round function is the same as scale for a fixed point number. This is also the same as rounding by increments of 10-1*integer, where integer * -1 is the power of 10 being rounded to.
--1.3-round-increments-as-scale.sql
set serveroutput on;
declare
l_value number := 12345.6789;
begin
dbms_output.put_line('round(n, i) = round_increments(n, power(10, i * -1)');
for i in reverse -2..2 loop
dbms_output.put( 'round(n, ' || i || ') = ' );
dbms_output.put_line( round(l_value, i) );
end loop;
dbms_output.put_line('round(n, e * -1) = round_increments(n, power(10, e))');
for e in -2..2 loop
dbms_output.put( 'round_increments(n, ' || power(10, e) || ') = ' );
dbms_output.put_line( round_increments(l_value, power(10, e)) );
end loop;
end;
/
round(n, i) = round_increments(n, power(10, i * -1)
round(n, 2) = 12345.68
round(n, 1) = 12345.7
round(n, 0) = 12346
round(n, -1) = 12350
round(n, -2) = 12300
round(n, e * -1) = round_increments(n, power(10, e))
round_increments(n, .01) = 12345.68
round_increments(n, .1) = 12345.7
round_increments(n, 1) = 12346
round_increments(n, 10) = 12350
round_increments(n, 100) = 12300Incremental Rounding: Upwards
If we want to always round up, we can use the ceil function. The same logic we used to convert round to an incremental round can be applied to the ceil function to support rounding upward in increments.
--2.0-ceil-increments-examples.sql
set serveroutput on;
declare
procedure test(n in number, i in number)
is
begin
dbms_output.put('ceil ' || n || ' in increments of ' || i || ' = ');
dbms_output.put_line( ceil( n/i ) * i );
end test;
begin
test(8.5, 5);
test(13, 5);
test(0.42, 0.25);
test(1.08, 0.25);
test(-8.5, 5);
test(-13, 5);
test(-0.42, 0.25);
test(-1.08, 0.25);
end;
/
ceil 8.5 in increments of 5 = 10
ceil 13 in increments of 5 = 15
ceil .42 in increments of .25 = .5
ceil 1.08 in increments of .25 = 1.25
ceil -8.5 in increments of 5 = -5
ceil -13 in increments of 5 = -10
ceil -.42 in increments of .25 = -.25
ceil -1.08 in increments of .25 = -1
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25),
(-8.5, 5), (-13, 5), (-0.42, 0.25), (-1.08, 0.25)
)
select
n as "number"
, i as "increment"
, ceil( n/i ) * i as "result"
from base
/
number increment result
---------- ---------- ----------
8.5 5 10
13 5 15
.42 .25 .5
1.08 .25 1.25
-8.5 5 -5
-13 5 -10
-0.42 .25 -0.25
-1.08 .25 -1The ceil_increments function is nearly identical to round_increments:
--2.1-ceil-increments-function.sql
create or replace function ceil_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return ceil( p_value/p_increment ) * p_increment;
end ceil_increments;
/Again, we can create an alternative form to use as a scalar macro:
--2.2-ceil-increments-macro.sql
create or replace function ceil_increments_sqm(
p_value in number
, p_increment in number
) return varchar2 sql_macro(scalar)
is
begin
return 'ceil( p_value/p_increment ) * p_increment';
end ceil_increments_sqm;
/Incremental Rounding: Always Down
Rounding down to the nearest increment can be accomplished the same way by using the FLOOR function:
--3.0-floor-increments-examples.sql
declare
procedure test(n in number, i in number)
is
begin
dbms_output.put('floor ' || n || ' in increments of ' || i || ' = ');
dbms_output.put_line( floor( n/i ) * i );
end test;
begin
test(8.5, 5);
test(13, 5);
test(0.42, 0.25);
test(1.08, 0.25);
test(-8.5, 5);
test(-13, 5);
test(-0.42, 0.25);
test(-1.08, 0.25);
end;
/
floor 8.5 in increments of 5 = 5
floor 13 in increments of 5 = 10
floor .42 in increments of .25 = .25
floor 1.08 in increments of .25 = 1
floor -8.5 in increments of 5 = -10
floor -13 in increments of 5 = -15
floor -.42 in increments of .25 = -.5
floor -1.08 in increments of .25 = -1.25
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25),
(-8.5, 5), (-13, 5), (-0.42, 0.25), (-1.08, 0.25)
)
select
n as "number"
, i as "increment"
, floor( n/i ) * i as "result"
from base
/
number increment result
---------- ---------- ----------
8.5 5 5
13 5 10
.42 .25 .25
1.08 .25 1
-8.5 5 -10
-13 5 -15
-0.42 .25 -0.5
-1.08 .25 -1.25The normal function to round up in increments is just like round_increments, except for the use of the ceil function.
--3.1-floor-increments-function.sql
create or replace function floor_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return floor( p_value/p_increment ) * p_increment;
end floor_increments;
/Again, converting this function to a scalar macro is a simple thing. The formula uses functionality that is compatible with SQL, so we just return the calculation as a string:
--3.2-floor-increments-macro.sql
create or replace function floor_increments_sqm(
p_value in number
, p_increment in number
) return varchar2 sql_macro(scalar)
is
begin
return 'floor( p_value/p_increment ) * p_increment';
end floor_increments_sqm;
/Incremental Rounding: Toward Zero
Using floor to always round down is the same as rounding toward zero for positive numbers. Floor would actually round away from zero for negative numbers. To always round toward zero we can use the trunc function.
--4.0-trunc-increments-examples.sql
declare
procedure test(n in number, i in number)
is
begin
dbms_output.put('trunc ' || n || ' in increments of ' || i || ' = ');
dbms_output.put_line( trunc( n/i ) * i );
end test;
begin
test(8.5, 5);
test(13, 5);
test(0.42, 0.25);
test(1.08, 0.25);
test(-8.5, 5);
test(-13, 5);
test(-0.42, 0.25);
test(-1.08, 0.25);
end;
/
trunc 8.5 in increments of 5 = 5
trunc 13 in increments of 5 = 10
trunc .42 in increments of .25 = .25
trunc 1.08 in increments of .25 = 1
trunc -8.5 in increments of 5 = -5
trunc -13 in increments of 5 = -10
trunc -.42 in increments of .25 = -.25
trunc -1.08 in increments of .25 = -1
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25),
(-8.5, 5), (-13, 5), (-0.42, 0.25), (-1.08, 0.25)
)
select
n as "number"
, i as "increment"
, trunc( n/i ) * i as "result"
from base
/
number increment result
---------- ---------- ----------
8.5 5 5
13 5 10
.42 .25 .25
1.08 .25 1
-8.5 5 -5
-13 5 -10
-0.42 .25 -0.25
-1.08 .25 -1Converting this to a Pl/sql function is again straightforward.
--4.1-trunc-increments-function.sql
create or replace function trunc_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return trunc( p_value/p_increment ) * p_increment;
end trunc_increments;
/Again, since we kept the normal function as a single return expression, it converts easily to an sql macro.
--4.2-trunc-increments-macro.sql
create or replace function trunc_increments_sqm(
p_value in number
, p_increment in number
) return varchar2 sql_macro(scalar)
is
begin
return 'trunc( p_value/p_increment ) * p_increment';
end trunc_increments_sqm;
/Example: Making Change
A simple activity like making change for a cash purchase is actually based on rounding down or towards zero for several increment sizes. The denominations used for currency units can each be considered a separate increment. Making change is a matter of rounding down starting with the largest denomination, subtracting this value from the total change needed and rounding down the next denomination.
--4.3-trunc-increments-change.sql
set serveroutput on;
declare
function make_change(
p_purchase in number
, p_payment in number
) return varchar2
is
l_balance number(6,2);
l_change varchar2(100);
type curr_r is record(
unit_count integer
, unit_value number
, unit_name varchar2(20)
);
type curr_aa is table of curr_r index by pls_integer;
l_change_aa curr_aa;
procedure how_many(
p_unit in number
, p_total in out number
, p_count out number)
is
begin
p_count := incremental_trunc(p_total, p_unit) / p_unit;
p_total := p_total - (p_count * p_unit);
end how_many;
begin
l_balance := p_payment - p_purchase;
if l_balance < 0 then l_change := 'No change, balance due is ' || l_balance;
elsif l_balance = 0 then l_change := 'No Change, payment is exact amount';
else
l_change_aa := curr_aa(
curr_r(0, 10, 'ten')
, curr_r(0, 5, 'five')
, curr_r(0, 1, 'one')
, curr_r(0, 25/100, 'quarter')
, curr_r(0, 10/100, 'dime')
, curr_r(0, 5/100, 'nickel')
, curr_r(0, 1/100, 'cent'));
for i in 1..l_change_aa.count loop
how_many(l_change_aa(i).unit_value, l_balance, l_change_aa(i).unit_count);
l_change := l_change
|| case when l_change_aa(i).unit_count > 0
then l_change_aa(i).unit_count || ' ' || l_change_aa(i).unit_name
|| case when l_change_aa(i).unit_count > 1 then 's' end
|| ', ' end;
end loop;
l_change := rtrim(l_change, ', ');
end if;
return p_payment || ' paid - ' || p_purchase || ' due = ' || (p_payment - p_purchase)
|| '. Change: ' || l_change || '.';
end make_change;
begin
dbms_output.put_line(make_change(42.42, 50));
dbms_output.put_line(make_change(7.13, 20));
dbms_output.put_line(make_change(3.51, 10));
dbms_output.put_line(make_change(11.17, 20));
dbms_output.put_line(make_change(3.50, 3.50));
dbms_output.put_line(make_change(7.77, 5));
end;
/
50 paid - 42.42 = 7.58. Change: 1 five, 2 ones, 2 quarters, 1 nickel, 3 cents.
20 paid - 7.13 = 12.87. Change: 1 ten, 2 ones, 3 quarters, 1 dime, 2 cents.
10 paid - 3.51 = 6.49. Change: 1 five, 1 one, 1 quarter, 2 dimes, 4 cents.
20 paid - 11.17 = 8.83. Change: 1 five, 3 ones, 3 quarters, 1 nickel, 3 cents.In the previous example, the currency record (curr_a) could easily be modified to keep track of how many units were available at the moment. This could then be instantiated as a cash drawer variable which could calculate change based on availability of different denominations.
Incremental Rounding: Away From Zero
Rounding away from zero is sometimes called rounding toward infinity. This method of rounding always rounds up for positive numbers and always down for negative numbers. Here we first need to evaluate the sign of the number, using floor for negative numbers and ceil for positive numbers. Aside from a decision on whether to use floor or ceil, the same incremental rounding logic applies.
--5.0-round-from-zero-increments-examples.sql
declare
procedure test(n in number, i in number)
is
begin
dbms_output.put('round ' || n || ' away from zero in increments of ' || i || ' = ');
dbms_output.put_line(
case sign(n)
when -1 then floor( n/i )
else ceil( n/i )
end * i
);
end test;
begin
test(8.5, 5);
test(13, 5);
test(0.42, 0.25);
test(1.08, 0.25);
test(-8.5, 5);
test(-13, 5);
test(-0.42, 0.25);
test(-1.08, 0.25);
end;
/
round 8.5 away from zero in increments of 5 = 10
round 13 away from zero in increments of 5 = 15
round .42 away from zero in increments of .25 = .5
round 1.08 away from zero in increments of .25 = 1.25
round -8.5 away from zero in increments of 5 = -10
round -13 away from zero in increments of 5 = -15
round -.42 away from zero in increments of .25 = -.5
round -1.08 away from zero in increments of .25 = -1.25
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25),
(-8.5, 5), (-13, 5), (-0.42, 0.25), (-1.08, 0.25)
)
select
n as "number"
, i as "increment"
, case sign(n)
when -1 then floor( n/i )
else ceil( n/i )
end * i as "result"
from base
/
number increment result
---------- ---------- ----------
8.5 5 10
13 5 15
.42 .25 .5
1.08 .25 1.25
-8.5 5 -10
-13 5 -15
-0.42 .25 -0.5
-1.08 .25 -1.25We could use an if then statement to decide which function to use, but this would not translate cleanly to a macro. Using a case expression allows us to use syntax that translates directly to SQL.
--5.1-round-from-zero-increments-function.sql
create or replace function round_from_zero_increments(
p_value in number
, p_increment in number
) return number
is
pragma udf;
begin
return
case sign(p_value)
when -1 then floor(p_value/p_increment)
else ceil(p_value/p_increment)
end * p_increment;
end round_from_zero_increments;
/All we have to do to convert to a macro is to surround the case expression with quotes, change the return type of the function and add the macro annotation.
--5.2-round-from-zero-increments-macro.sql
create or replace function round_from_zero_increments_sqm(
p_value in number
, p_increment in number
) return varchar2
sql_macro(scalar)
is
begin
return
'case sign(p_value)
when -1 then floor(p_value/p_increment)
else ceil(p_value/p_increment)
end * p_increment';
end round_from_zero_increments_sqm;
/Unit Testing Functions and Macros
First, create a view with some good test cases to look at.
--6.0-test-data-view.sql
create or replace view test_data as
with base(n, i) as (
values
(8.5, 5), (13, 5), (0.42, 0.25), (1.08, 0.25)
)
select n, i from base
union all
select -n, i from base
/Now we can test all of these functions and evaluate the differences.
--6.1-test-functions.sql
select
n as "number"
, i as "increment"
, round_increments(n,i) as "round_n_i"
, ceil_increments(n,i) as "ceil_n_i"
, floor_increments(n,i) as "floor_n_i"
, trunc_increments(n,i) as "trunc_n_i"
, round_from_zero_increments(n,i) as "round_from_zero_n_i"
from test_data
order by i, abs(n), n
/
number increment round_n_i ceil_n_i floor_n_i trunc_n_i round_from_zero_n_i
---------- ---------- ---------- ---------- ---------- ---------- -------------------
-0.42 .25 -0.5 -0.25 -0.5 -0.25 -0.5
.42 .25 .5 .5 .25 .25 .5
-1.08 .25 -1 -1 -1.25 -1 -1.25
1.08 .25 1 1.25 1 1 1.25
-8.5 5 -10 -5 -10 -5 -10
8.5 5 10 10 5 5 10
-13 5 -15 -10 -15 -10 -15
13 5 15 15 10 10 15We should also test the macros to see that they come up with the same results.
--6.2-test-macros.sql
select
n as "number"
, i as "increment"
, round_increments_sqm(n,i) as "round_n_i"
, ceil_increments_sqm(n,i) as "ceil_n_i"
, floor_increments_sqm(n,i) as "floor_n_i"
, trunc_increments_sqm(n,i) as "trunc_n_i"
, round_from_zero_increments_sqm(n,i) as "round_from_zero_n_i"
from test_data
order by i, abs(n), n
/
number increment round_n_i ceil_n_i floor_n_i trunc_n_i round_from_zero_n_i
---------- ---------- ---------- ---------- ---------- ---------- -------------------
-0.42 .25 -0.5 -0.25 -0.5 -0.25 -0.5
.42 .25 .5 .5 .25 .25 .5
-1.08 .25 -1 -1 -1.25 -1 -1.25
1.08 .25 1 1.25 1 1 1.25
-8.5 5 -10 -5 -10 -5 -10
8.5 5 10 10 5 5 10
-13 5 -15 -10 -15 -10 -15
13 5 15 15 10 10 15We have created functions and parallel macros to encapsulate the logic of incremental logic, but it took five different functions and five macros. The transpiler eventually would make the macros unnecessary, but we should look closer to see if we can unify this logic and its variants into a single function.
Unified Incremental Rounding Functionality: To_Increments
Now that we know all of the logic works as normal Pl/sql functions and sql macros we may want to wrap everything up into a single function that contains this incremental rounding logic and all of its variants.
Instead of conditionally executing different statements based on the desired variant, we can wrap all the variants within a single case expression. This will simplify translation to a macro. If the function is later moved to a package, we can create constants for each of these different modes.
--7.1-increments-function.sql
create or replace function to_increments(
p_value in number
, p_increment in number
, p_mode in varchar2 default 'round'
) return number
is
pragma udf;
begin
return
case p_mode
when 'round' then
round(p_value/p_increment) * p_increment
when 'ceil' then
ceil(p_value/p_increment) * p_increment
when 'floor' then
floor(p_value/p_increment) * p_increment
when 'trunc' then
trunc(p_value/p_increment) * p_increment
else
case sign(p_value)
when -1 then floor(p_value/p_increment)
else ceil(p_value/p_increment)
end * p_increment
end;
end to_increments;
/Since we contained all of the logic in a case expression, the function translates directly to SQL and the macro form of to_increments is simple to create.
--7.2-increments-macro.sql
create or replace function to_increments_sqm(
p_value in number
, p_increment in number
, p_mode in varchar2 default 'round'
) return varchar2 sql_macro(scalar)
is
begin
return
q'!
case p_mode
when 'round' then
round(p_value/p_increment) * p_increment
when 'ceil' then
ceil(p_value/p_increment) * p_increment
when 'floor' then
floor(p_value/p_increment) * p_increment
when 'trunc' then
trunc(p_value/p_increment) * p_increment
else
case sign(p_value)
when -1 then floor(p_value/p_increment)
else ceil(p_value/p_increment)
end * p_increment
end
!';
end to_increments_sqm;
/The value for the p_mode parameter will be null at query parse time because varchar2 parameters for macros are not known until execution time. This is not a problem because we are not trying to change the way we build the sql fragment based on the value of the p_mode parameter.
Testing the unified function gives the right results.
--7.3-test-functions.sql
select
n as "number"
, i as "increment"
, to_increments(n,i, 'round') as "round_n_i"
, to_increments(n,i, 'ceil') as "ceil_n_i"
, to_increments(n,i, 'floor') as "floor_n_i"
, to_increments(n,i, 'trunc') as "trunc_n_i"
, to_increments(n,i, 'round-from-zero') as "round_from_zero_n_i"
from test_data
order by i, abs(n), n
/
number increment round_n_i ceil_n_i floor_n_i trunc_n_i round_from_zero_n_i
---------- ---------- ---------- ---------- ---------- ---------- -------------------
-0.42 .25 -0.5 -0.25 -0.5 -0.25 -0.5
.42 .25 .5 .5 .25 .25 .5
-1.08 .25 -1 -1 -1.25 -1 -1.25
1.08 .25 1 1.25 1 1 1.25
-8.5 5 -10 -5 -10 -5 -10
8.5 5 10 10 5 5 10
-13 5 -15 -10 -15 -10 -15
13 5 15 15 10 10 15Testing the unified macro also looks good.
--7.4-test-macros.sql
select
n as "number"
, i as "increment"
, to_increments_sqm(n,i, 'round') as "round_n_i"
, to_increments_sqm(n,i, 'ceil') as "ceil_n_i"
, to_increments_sqm(n,i, 'floor') as "floor_n_i"
, to_increments_sqm(n,i, 'trunc') as "trunc_n_i"
, to_increments_sqm(n,i, 'round-from-zero') as "round_from_zero_n_i"
from test_data
order by i, abs(n), n
/
number increment round_n_i ceil_n_i floor_n_i trunc_n_i round_from_zero_n_i
---------- ---------- ---------- ---------- ---------- ---------- -------------------
-0.42 .25 -0.5 -0.25 -0.5 -0.25 -0.5
.42 .25 .5 .5 .25 .25 .5
-1.08 .25 -1 -1 -1.25 -1 -1.25
1.08 .25 1 1.25 1 1 1.25
-8.5 5 -10 -5 -10 -5 -10
8.5 5 10 10 5 5 10
-13 5 -15 -10 -15 -10 -15
13 5 15 15 10 10 15Summary
Writing these functions for incremental rounding was really an excercise in creating Pl/sql functions that can be easily converted to sql macros without any modification of the syntax. All operations translate directly to SQL and conversion to macros takes no effort. Additionally, this approach lets us create functions that will be accessible to the sql transpiler, allowing transparent support for the same logic without writing additional macro functions.
The next article in this series will look into incremental rounding with datetime datatypes. There are additional complexities because of datetimes that will make those functions a bit more complex to achieve.
–Anthony Harper
Practical Pl/Sql 
Leave a comment