Oracle中number型別詳解

1、Oracle總number型別的用法是 number[({p},[s])]其中p表示整個數的位數，s表示小數點的位數，比如： number(5,2)表示這個數最多有5位（不包括小數點在內），2表示小數點後有兩位小數

2、p的最大38是，s的範圍是-84到127。

3、用法：

 (1)p>s>0 總位數是p，小數位數是s，整數部分位數是p-s

 (2)p=s=0可以表示任何數，只要這個數在範圍內就行了，這個數可以是整數，浮點數都行

 (3)p>0 s=0表示整數

 (4) p>0 s<0 其中s表示在小數點右邊的0的個數，並且從這個位置開始四捨五入，例如1234567 是number(5,-2)型別，則結果是1234600

 (5)p>0 s>0 p<s則在小數點的右邊有s-p個0，同時保留小數點的個數是s個

  

Value Datatype Stored Value Explanation 123.2564 NUMBER 123.2564 The range and precision are set to the maximum, so the datatype can store any value. 1234.9876 NUMBER(6,2) 1234.99 Since the scale is only 2, the decimal part of the value is rounded to two digits. 12345.12345 NUMBER(6,2) Error The range of the integer part is only from –9999 to 9999. 123456 NUMBER(6,2) Error The precision is larger than specified; the range is only from –9999 to 9999. 1234.9876 NUMBER(6) 1235 The decimal part is rounded to the next integer. 123456.1 NUMBER(6) 123456 The decimal part is rounded. 12345.345 NUMBER(5,-2) 12300 The negative scale rounds the number <s> digits left to the decimal point. –2 rounds to hundreds. 1234567 NUMBER(5,-2) 1234600 Rounded to the nearest hundred. 12345678 NUMBER(5,-2) Error Outside the range; can have only five digits, excluding the two zeros representing hundreds, for a total of seven digits: (s – (–p) = s + p = 5 + 2 = 7). 123456789 NUMBER(5,-4) 123460000 Rounded to the nearest 10,000. 1234567890 NUMBER(5,-4) Error Outside the range; can have only five digits, excluding the four trailing zeros. 12345.58 NUMBER(*, 1) 12345.6 The use of * in the precision specifies the default limit (38). 0.1 NUMBER(4,5) Error Requires a zero after the decimal point (5 – 4 = 1). 0.01234567 NUMBER(4,5) 0.01235 Rounded to four digits after the decimal point and zero. 0.09999 NUMBER(4,5) 0.09999 Stored as it is; only four digits after the decimal point and zero. 0.099996 NUMBER(4,5) Error Rounding this value to four digits after the decimal and zero results in 0.1, which is outside the range.