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
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.
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.