建表时对于number类型字段定义，指定大小和不指定大小会有什么影响？
如：
(1)“大”的数字
id number,
id number(1000000000);
(2)“小”的数字
id number,
id number(2);
除去大小超过限制外，还有什么特别的含义或区别吗？
因为经常看到有些人在建表时，对于number类型的数据不指定大小。才有此疑惑。
还望高人解惑之。
如：
(1)“大”的数字
id number,
id number(1000000000);
(2)“小”的数字
id number,
id number(2);
除去大小超过限制外，还有什么特别的含义或区别吗？
因为经常看到有些人在建表时，对于number类型的数据不指定大小。才有此疑惑。
还望高人解惑之。
create table test (id number(39))
*
第 1 行出现错误:
ORA-01727: 数字精度说明符超出范围 (1 到 38)
SQL> create table test (id number(38));表已创建。SQL>oracle的number类型精度、刻度范围 number(p,s) p:1---38
s:-84---127 有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数。
11g Release 2 (11.2)
Part Number E10592-04
NUMBER [ (p [, s]) ]
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.NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.Specify a fixed-point number using the following form:NUMBER(p,s)
where:p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.Scale can be greater than precision, most commonly when e notation is used. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.Specify an integer using the following form:NUMBER(p)
This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).Specify a floating-point number using the following form:NUMBER
The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.See Also:"Floating-Point Numbers"
Table 3-2 show how Oracle stores data using different precisions and scales.Table 3-2 Storage of Scale and PrecisionActual Data Specified As Stored As
123.89
NUMBER
123.89
123.89
NUMBER(3)
124
123.89
NUMBER(3,2)
exceeds precision
123.89
NUMBER(4,2)
exceeds precision
123.89
NUMBER(5,2)
123.89
123.89
NUMBER(6,1)
123.9
123.89
NUMBER(6,-2)
100
.01234
NUMBER(4,5)
.01234
.00012
NUMBER(4,5)
.00012
.000127
NUMBER(4,5)
.00013
.0000012
NUMBER(2,7)
.0000012
.00000123
NUMBER(2,7)
.0000012
1.2e-4
NUMBER(2,5)
0.00012
1.2e-5
NUMBER(2,5)
0.00001