The NUMBER
data type stores fixed and floating-point numbers. The database can store numbers of virtually any magnitude(大小). This data is guaranteed to be portable among different operating systems running Oracle Database. The NUMBER
data type is recommended for most cases in which you must storenumeric(数) data.
You specify a fixed-point number in the form NUMBER
(
p
,
s
)
, where p
and s
refer to the following characteristics:
-
Precision
The precision specifies the total number of digits. If a precision is not specified, then the column stores the values exactly as provided by the application without any rounding.
-
Scale
The scale specifies the number of digits from the decimal(小数) point to the least significant digit. Positive scale counts digits to the right of the decimal point up to and including the least significant digit. Negative scale counts digits to the left of the decimal point up to but not including the least significant digit. If you specify a precision without a scale, as in
NUMBER(6)
, then the scale is 0.
In Example 2-1, the salary
column is type NUMBER(8,2)
, so the precision is 8 and the scale is 2. Thus, the database stores a salary of 100,000 as 100000.00
.
Number having precision p
and scale s
. The precision p
can range from 1 to 38. The scale s
can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER
value requires from 1 to 22 bytes.
最高整数位数 = p - s # The precision specifies the total number of digits.
s正数,小数点右边指定位置开始四舍五入
s负数,小数点左边指定位置开始四舍五入
s是0或者未指定,四舍五入到最近整数
当p小于s时候,表示数字是绝对值小于1的数字,且从小数点右边开始的前s-p 位必须是0,保留s位小数。
p 效数位:从左边第一个不为0的数算起
如 0.01234567 NUMBER(4,5) 其实存储结果为 0.01235
p>0,对s分2种情况:
1. s>0
精确到小数点右边s位,并四舍五入。然后检验有效数位是否<=p;如果s>p,小数点右边至少有s-p个0填充。
2. s<0
精确到小数点左边s位,并四舍五入。然后检验有效数位是否<=p+|s|
Value Datatype Stored Value
123.2564 NUMBER 123.2564
1234.9876 NUMBER(6,2) 1234.99
12345.12345 NUMBER(6,2) Error
1234.9876 NUMBER(6) 1235
12345.345 NUMBER(5,-2) 12300
1234567 NUMBER(5,-2) 1234600
12345678 NUMBER(5,-2) Error
123456789 NUMBER(5,-4) 123460000
1234567890 NUMBER(5,-4) Error
12345.58 NUMBER(*, 1) 12345.6
0.1 NUMBER(4,5) Error
0.01234567 NUMBER(4,5) 0.01235
0.09999 NUMBER(4,5) 0.09999
0.099996 NUMBER(4,5) <>