Rounding numbers: negative scale

Recently I was looking up some information on Oracle Data Types in the Oracle manual, when I noticed a remark in the text about using negative values for scale when defining a number. As a refresher: a number is defined as

NUMBER(precision, scale)

The precision holds the total number of digits, the scale holds the number of decimals. Well enough, all is clear there. But the thing I didn’t know was that if you supply a negative value for scale, it causes the number you store to be rounded.

Example:

Suppose I have a variable defined as a number(5, -1). Watch what happens if I supply the values 72, 58, 35 and 24.9:

declare
  roundnum number(5,-1);
begin
  roundnum := 72;
  DBMS_OUTPUT.PUT_LINE(roundnum);
  roundnum := 58;
  DBMS_OUTPUT.PUT_LINE(roundnum);
  roundnum := 35;
  DBMS_OUTPUT.PUT_LINE(roundnum);
  roundnum := 24.9;
  DBMS_OUTPUT.PUT_LINE(roundnum);
end;

Running this results in:

70
60
40
20

Just a nice little ‘trick’ I thought I’d share with you.

Follow

Get every new post delivered to your Inbox.