NUMBER : Number « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. Number

The NUMBER datatype is used for declaring both fixed-point and floating-point numbers.

The NUMBER datatype can be used to represent numbers in the range 1.0E-123 through 9.99E125.

The NUMBER datatype allows for up to 38 decimal digits of precision.

The Syntax for the NUMBER Datatype

variable_name NUMBER [(precision[,scale])]

variable_name is whatever name you want to give this variable.

precision specifies the number of decimal digits used to represent the value internally.

precision range is 1 to 38.

the default for precision rangeis 38.

scale indicates where the decimal point is and where rounding occurs.

The range for scale is -84 to 127, and the default is zero.

precision as telling you how many digits are used to represent the number.

scale tells you where the decimal point is.

Both precision and scale are optional.

The NUMBER datatype is overloaded to include three different numeric groups:

Integers ( NUMBER with only precision) are between -10^38 and 10^38 not including either of the bounds.

Fixed-point values (NUMBER both precision and scale) are between -10^122 and 10^122, not including either of the bounds and can be as small as 10^ C127.

Floating-point values (you don't specify anything) are between -10^130 and 10^130, not including either of the bounds and can be as small as 10^ C127.

For example,

dollar_amount NUMBER (5,2);

1. The dollar_amount variable, defined as NUMBER(5,2), would be precise to five digits, two of which would be to the right of the decimal.
2. All amounts would be rounded to the nearest hundredth.
3. The dollar_amount variable could store values such as 123.45, -999.99, and so on.
4. Assigning it a value of 123.456 would result in the value being rounded off to 123.46.

myNumber NUMBER (3);

1. myNumber variable would take the default scale of zero.
2. myNumber could store no digits to the right of the decimal.
3. All values will be rounded to the nearest whole number.
4. Assigning it a value of -123.45 would result in it being rounded off to -123.

myNumber2 NUMBER(5,-2);

1. myNumber2 stores five digits of precision.
2. All values are in hundreds.
3. myNumber2 could store values ranging from 0 to 9,999,900, but all values would be rounded to the nearest hundred.
4. Assign it a value of 100, and it will store 100.
5. Assign it a value of 327, and it will be rounded off to 300.
6. Why use a variable like this?
7. It saves a bit of space and allows you to use the 38 digits to represent some very large numbers without making excessive demands on memory.

myNumber3 NUMBER (1,6)

1. myNumber3 specified a scale that is larger than the precision.
2. myNumber3 will store values of one millionth, two millionths, and so on up to nine millionths.
3. All values will be rounded to the nearest millionth.
4. If you assigned it a value of 0.00000016, you would get 0.0000002.
5. Because the precision is only one, trying to assign a value of 0.000001 would result in an error.