ORACLE NUMBER資料型別

網上關於number的資料很多了，學習總結了下，如果問題及不足，歡迎指正。

一、oracle的number型別精度、刻度範圍

number(p,s)

p:1---38
s:-84---127

有效數位：從左邊第一個不為0的數算起，小數點和負號不計入有效位數。

p>0，對s分2種情況：

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的是位元組數，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資料型別（按一定規則進行轉換成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部分為一個位元組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個位元組該值為193) = 4 ，所以該數字佔用6（2+4）個位元組。

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位元組。

1 row selected.


SYS.YUDONG>select dump(1100) from dual;

DUMP(1100)
-------------------
Typ=2 Len=2: 194,12 --如果從右邊起，連續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。
對應關係變為9，8...1表示1，2...9，看下面幾個例子，如果足2位，還是按照上邊說的規律（-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個數值不在一個範圍，立即可以看出大小，如果在一個範圍其實也可以根據其正負+絕對值來進行排序了，正數絕對值大的就大，負數則相反，為何還要用到這個符號位？


三、相關bug：

時間久了，也無從考證，寫出來共享吧，也許有人會用到：

03年用9i的時候，做稅收會計餘額累計，當時遇到number型別的bug，資料結構為number(20,2),進行資料累計的時候當餘額等於1（也許是0，印象模糊了）的時候，出現03113
錯誤，每次執行都一樣，當時搜尋了資料也說是oracle一個bug，後來採取了一些迴避手段把金額先乘以100，換算完後再除以100，展現給使用者，也就是利用了number(p,0)整數
型別（不儲存小數部分，減少產生溢位等bug的機率）來解決了當時的問題。供參考！

http://zhouwf0726.itpub.net/post/9689/158068

補充一句：有些大型的計費系統在設計資料庫結構的時候採用number（p，0）來儲存資料，在資料展現的時候再除以100等（看具體需要的刻度），來換算小數部分，也是有一定的道理的，迴避了一些可能存在的bug或者其他資料溢位問題！（只是自己的一些評論，不能作為設計原則的）。