oracle的number類型是oracle的內置類型之一，是oracle的最基礎數值數據類型。在9iR2及其以前的版本中只支持一種適合存儲數值數據的固有數據類型，在10g以后，才出現了兩種新的數值類型，即推出本地浮點數據類型(Native Floating-Point Data Types): BINARY_FLOAT(單精度32位)和BINARY_DOUBLE(雙精度64位). 這些新數據類型都是基于IEEE二進制浮點運算標準,ANSI/IEEE Std 754-1985 [IEEE 754]，使用這些類型時要加上文字f(BINARY_FLOAT)或者d(BINARY_DOUBLE)，比如2.07f、3.000094d。
number數據類型
number類型的語法很簡單：number(p,s)：
p：精度位，precision，是總有效數據位數，取值范圍是38，默認是38，可以用字符*表示38。
s：小數位，scale，是小數點右邊的位數，取值范圍是-84~127，默認值取決于p，如果沒有指定p，那么s是最大范圍，如果指定了p，那么s=0。
p：is the precision,or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
s：is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.
number類型的p和s，與其底層存儲完全沒有關系，根本不會影響數據在磁盤上如何存儲，它只會影響允許哪些值以及數值如何舍入，你可以認為其是對數據的“編輯”。簡單的說，精度位p表示數值最多能有多少個有效數字，而小數位s表示最多能有多少位小數。換句話說，p表示一共有多少位有效數字（即小數點左邊最多有p-s位有效數字），s表示小數點右邊有s位有效數字。如number(5,2)類型的數據，就表示小數點左邊最多有3位有效數字，右邊最多有2位有效數字，加起來就是最多有5位有效數字，超過這個范圍的數字就不能正確的存儲下來，注意這里說的是不能正確存儲，但并不是不能存儲。
最高整數位數＝p-s
s正數，小數點右邊指定位置開始四舍五入
s負數，小數點左邊指定位置開始四舍五入
s是0或者未指定，四舍五入到最近整數
當p小于s時候，表示數字是絕對值小于1的數字，且從小數點右邊開始的前s-p位必須是0，保留s位小數。
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)
|
Error
|
里面發(fā)生錯誤的行有的是因為源數據超過了可以表示的范圍，有的是因為進行小數四舍五入后超過了可以表示的范圍。
以下是一些例子
1. s>0
精確到小數點右邊s位，并四舍五入。然后檢驗有效數位是否<=p；
ZWF.YUDONG>create table t_n(id number(5,2));
Table created.
ZWF.YUDONG>insert into t_n values(123.45);
1 row created.
ZWF.YUDONG>insert into t_n values(123.455);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
123.45
123.46
2 rows selected.
ZWF.YUDONG>insert into t_n values(1.234);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
123.45
123.46
1.23
3 rows selected.
ZWF.YUDONG>insert into t_n values(.001);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
123.45
123.46
1.23
0
4 rows selected.
ZWF.YUDONG>insert into t_n values(1234.56);
insert into t_n values(1234.56)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
如果s>p，小數點右邊至少有s-p個0填充。
ZWF.YUDONG>create table t_n(id number(4,5));
Table created.
ZWF.YUDONG>insert into t_n values(1);
insert into t_n values(1)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(.1);
insert into t_n values(.1)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(.01);
1 row created.
ZWF.YUDONG>commit;
Commit complete.
ZWF.YUDONG>select * from t_n;
ID
----------
.01
1 row selected.
ZWF.YUDONG>insert into t_n values(.001);
1 row created.
ZWF.YUDONG>insert into t_n values(.0001);
1 row created.
ZWF.YUDONG>insert into t_n values(.00001);
1 row created.
ZWF.YUDONG>insert into t_n values(.000001); --超過刻度存儲0
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
.01
.001
.0001
.00001
0
10 rows selected.
ZWF.YUDONG>col dp for a50
ZWF.YUDONG>select id,dump(id) dp,length(id),vsize(id) from t_n; --vsize和dump的是字節(jié)數，length是數值實際位數（含小數點）
ID DP LENGTH(ID) VSIZE(ID)
---------- -------------------------------------------------- ---------- ----------
.01 Typ=2 Len=2: 192,2 3 2
.001 Typ=2 Len=2: 191,11 4 2
.0001 Typ=2 Len=2: 191,2 5 2
.00001 Typ=2 Len=2: 190,11 6 2
0 Typ=2 Len=1: 128 1 1
5 rows selected.
2. s<0
精確到小數點左邊s位，并四舍五入。然后檢驗有效數位是否<=p+|s|
ZWF.YUDONG>create table t_n(id number(5,-2));
Table created.
ZWF.YUDONG>insert into t_n values(12345);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
12300
1 row selected.
ZWF.YUDONG>insert into t_n values(123456);
1 row created.
ZWF.YUDONG>insert into t_n values(1234567);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
12300
123500
1234600
3 rows selected.
ZWF.YUDONG>insert into t_n values(12345678);
insert into t_n values(12345678)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
oracle的number類型存儲結構
oracle采用變長存儲number數據類型（按一定規(guī)則進行轉換成2進制編碼格式存儲）。
oracle數據庫中存儲的number類型包含3個部分: HEAD部分, DATA部分, 符號位。
對正數來說, 符號位省略, 對0來說, oracle存儲的是X80（128）。
ZWF.YUDONG>select dump(0) from dual;
DUMP(0)
----------------
Typ=2 Len=1: 128
1 row selected.
ZWF.YUDONG>select dump(1) from dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2
1 row selected.
ZWF.YUDONG>select dump(-1) from dual;
DUMP(-1)
-----------------------
Typ=2 Len=3: 62,100,102
1 row selected.
HEAD部分為一個字節(jié)8位, 就是前面看到的128, 193，62。由該部分我們可以看出number類型的基本信息，因為設計這種存儲格式的時候, oracle希望以十六進制00-FF來表示所有
的number, 所以為了編碼的對稱, 首先將number分為正負, 所以以00-FF的中間位置80, 也就是十進制的128來表示0, HEAD部分小于80,即為負數,大于80即為正數。ORACLE再次對
00-80, 80-FF進行對分：
00-3E 表示: number <= -1
3F-7F 表示: -1 < number < 0
81-C0 表示: 0 < number < 1
C1-FF 表示：number >= 1
從HEAD部分我們可以也看出數據的位數信息，是否含有小數，可以根據HEAD的信息判斷小數點的位置。由于數據部分低位2的n次方位個0是不被存儲的，數據展現的時候oracle
根據HEAD的信息給補充末位的0。
ZWF.YUDONG>select dump(123456789) from dual;
DUMP(123456789)
------------------------------
Typ=2 Len=6: 197,2,24,46,68,90 --197（C5）的含義：表示數字123456789大于1，197-193(數字1占用2個字節(jié)該值為193) = 4 ，所以該數字占用6（2+4）個字節(jié)。
1 row selected.
然后,我們再來看數據部分, ORACLE對十進制的數字(整數部分,小數部分正好相反)是兩位兩位進行存儲的(從右往左的順序), 例如對1234, ORACLE會分別對12, 34進行存儲.
所以只需要對(+-)1-99進行編碼
1 --- 99 分別用十六進制2-64表示，就是2-100,
-1--- -99 用十六進制64-2表示，就是100-2
ZWF.YUDONG>select dump(12345) from dual;
DUMP(12345)
------------------------
Typ=2 Len=4: 195,2,24,46 --數據部分2,24,46 表示 （2-1=1，24-1=23，46-1=45）；HEAD部分表示12345 >= 1,占用195-193+2=4字節(jié)。
1 row selected.
SYS.YUDONG>select dump(1100) from dual;
DUMP(1100)
-------------------
Typ=2 Len=2: 194,12 --如果從右邊起，連續(xù)2的n次方位為0，oracle一次排觸（不存儲）只是位數加1。可以對比dump(11)的情況看看。
1 row selected.
SYS.YUDONG>select dump(11) from dual;
DUMP(11)
-------------------
Typ=2 Len=2: 193,12 --這里數據部分和1100是一樣的，末位的2個0沒有實際存儲，長度193比194小1。
1 row selected.
--對于含小數(負數、整數2種情況)的情況：
1、負數
SYS.YUDONG>select dump(-1.2) from dual;
DUMP(-1.2)
--------------------------
Typ=2 Len=4: 62,100,81,102 --HEAD=62（3E）表示該數值小于等于-1;數據部分：整數部分的-1存儲為100，小數部分從左往右2位一結合，不足2位后邊補一個1。
對應關系變?yōu)?，8...1表示1，2...9，看下面幾個例子，如果足2位，還是按照上邊說的規(guī)律（-1--- -99 用十六進制64-2表示，就是100-2）。
1 row selected.
ZWF.YUDONG>select dump(-2.1) from dual;
DUMP(-2.1)
-------------------------
Typ=2 Len=4: 62,99,91,102
1 row selected.
ZWF.YUDONG>select dump(-2.2) from dual;
DUMP(-2.2)
-------------------------
Typ=2 Len=4: 62,99,81,102
1 row selected.
ZWF.YUDONG>select dump(-2.9) from dual;
DUMP(-2.9)
-------------------------
Typ=2 Len=4: 62,99,11,102
1 row selected.
ZWF.YUDONG>select dump(-2.12) from dual;
DUMP(-2.12)
-------------------------
Typ=2 Len=4: 62,99,89,102
1 row selected.
ZWF.YUDONG>select dump(-2.13) from dual;
DUMP(-2.13)
-------------------------
Typ=2 Len=4: 62,99,88,102
1 row selected.
ZWF.YUDONG>select dump(-2.123) from dual;
DUMP(-2.123)
----------------------------
Typ=2 Len=5: 62,99,89,71,102
1 row selected.
2、正數
SYS.YUDONG>select dump(1.222) from dual;
DUMP(1.222)
------------------------
Typ=2 Len=4: 193,2,23,21 --HEAD=193(C1)表示該數字大于等于1;數據部分:整數部分存儲2(2-1=1),小數部分從左往右2位一結合,23(23-1=22)表示22,后邊還剩下一個2,
不足2位的末尾補充一個1,也就是等于1.2220
1 row selected.
ZWF.YUDONG>select dump(1.2220) from dual;
DUMP(1.2220)
------------------------
Typ=2 Len=4: 193,2,23,21
1 row selected.
符號位： 用的是(+-)1-99都不可能用到的編碼66(102)來表示，有資料說為了處理排序問題（未加考證）。根據HEAD部分可以做初步判斷，根據我們說的HEAD部分的四個范圍，
如果2個數值不在一個范圍，立即可以看出大小，如果在一個范圍其實也可以根據其正負+絕對值來進行排序了，正數絕對值大的就大，負數則相反，為何還要用到這個符號位？
本地浮點類型
另外再說一下兩個數值類型本地浮點數據類型（binary_float與binary_double）
本地浮點數據類型最大的特點就是比NUMBER類型效率更高
硬件運算/數學運算快 5– 10 倍
占用更少的內存/磁盤空間（5/9 字節(jié)與 1 – 22 字節(jié)）
BINARY_DOUBLE 值范圍更大（e308 與 e125）
無需類型轉換（使用與字節(jié)順序無關的存儲格式）
下面程序是使用歐拉級數計算圓周率∏:
∏ = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + ... ) )
方法一：使用NUMBER類型
create or replace procedure Euler_Pi_Number is
subtype My_Number is number;
zero constant My_Number := 0.0;
one constant My_Number := 1.0;
two constant My_Number := 2.0;
six constant My_Number := 6.0;
toler constant My_Number := 0.00000000001;
root_toler constant My_Number := toler/1000.0;
root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;
begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Number;
/
方法二：使用BINARY_DOUBLE類型
create or replace procedure Euler_Pi_Binary is
subtype My_Number is binary_double;
zero constant My_Number := 0.0d;
one constant My_Number := 1.0d;
two constant My_Number := 2.0d;
six constant My_Number := 6.0d;
toler constant My_Number := 0.00000000001d;
root_toler constant My_Number := toler/1000.0d;
root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;
begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Binary;
/
SQL> set timing on
SQL> exec Euler_Pi_Number;
PL/SQL 過程已成功完成。
已用時間: 00: 00: 11.59
SQL> exec Euler_Pi_Binary;
PL/SQL 過程已成功完成。
已用時間: 00: 00: 02.09
上面例子中近似300,000次迭代計算,NUMBER類型花費11.59秒,BINARY_DOUBLE類型花費約2.09秒,性能提高大約5.5倍.
結論：在版本10g之后寫一些偏數字科學運行量巨大的存儲過程、函數時,對于浮點數字類型要優(yōu)先考慮使用本地浮點數據類型
相關鏈接
http://it.chinawin.net/database/article-4193.html
http://ilinux.javaeye.com/blog/289550
http://yaanzy.itpub.net/post/1263/200971