oracle中的數值資料儲存格式分析(ZT)

　Oracle的NUMBER型別最多由三個部分構成，這三個部分分別是最高位表示位、資料部分、符號位。其中負數包含符號位，正數不會包括符號位。另外，數值0比較特殊，它只包含一個數值最高位表示位80，沒有資料部分。

　　正數的最高位表示位大於80，負數的最高位表示位小於80。其中一個正數的最高位是個位的話，則最高位表示位為C1，百位、萬位依次為C2、C3，百分位、萬分為依次為C0、BF。一個負數的最高位為個位的話，最高位表示位為3E，百位、萬位依次為3D、3C，百分位、萬分位依次為3F、40。
　　

資料部分每一位都表示2位數。這個兩位數可能是從0到99，如果是資料本身是正數，則分別用二進位制的1到64表示，如果資料本身是負數，則使用二進位制65到2表示。

　　符號位用66表示。

　　上面的這些是我透過DUMP結果總結出來的，對於上面提到的這些關係常數，Oracle之所以這樣選擇是有道理的，我們後面根據例子也可以推匯出來，而且會進一步說明為什麼會採用這種方式表示。這裡列出的意思是使大家先對NUMBER型別資料有一個大概的瞭解。

　　下面我們透過一個例子詳細說明：

SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);

表已建立。

SQL> INSERT INTO TEST_NUMBER VALUES (0);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (1);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (2);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (25);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (123);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (4100);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (132004078);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (2.01);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (0.3);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (-1);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (-5);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (-20032);

已建立 1 行。

SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> COL D_NUMBER FORMAT A50
SQL> SELECT NUMBER_COL, DUMP(NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;

NUMBER_COL D_NUMBER
---------- --------------------------------------------------
0 Typ=2 Len=1: 80
1 Typ=2 Len=2: c1,2
2 Typ=2 Len=2: c1,3
25 Typ=2 Len=2: c1,1a
123 Typ=2 Len=3: c2,2,18
4100 Typ=2 Len=2: c2,2a
132004078 Typ=2 Len=6: c5,2,21,1,29,4f
2.01 Typ=2 Len=3: c1,3,2
.3 Typ=2 Len=2: c0,1f
.00000125 Typ=2 Len=3: be,2,1a
115.200003 Typ=2 Len=6: c2,2,10,15,1,4
-1 Typ=2 Len=3: 3e,64,66
-5 Typ=2 Len=3: 3e,60,66
-20032 Typ=2 Len=5: 3c,63,65,45,66
-234.432 Typ=2 Len=6: 3d,63,43,3a,51,66

已選擇15行。

下面根據例子得到的結果，對每行進行說明。首先說明兩點基本的。DUMP函式返回的TYPE＝2表示DUMP的資料型別是NUMBER，LENGTH＝N表示數值在資料庫中儲存的長度是N。

　　1.DUMP(0)的結果是0x80，在前面已經提到，0只有高位表示位，沒有資料位。由於0的特殊，既不屬於正數，也不屬於負數，因此使用高位表示位用80表示就足夠了，不會和其它資料衝突，Oracle出於節省空間的考慮將後面資料部分省掉了。但是為什麼Oracle選擇0x80表示0呢？我們知道正數和負數互為相反數，每個正數都有一個對應的負數。因此如果我們要使用編碼表示數值，則表示正數和負數的編碼應該各佔一半，這樣才能保證使Oracle表示資料範圍是合理的。而0x80的二進位制編碼是1000 0000，正好是一個位元組編碼最大值的一半，因此，Oracle選擇0x80來表示0，是十分有道理的。

　　2.DUMP(1)的結果是0xc102，0xc1表示了最高位個位，0x2表示數值是1。首先，Oracle為什麼用C1表示個位呢？其實，道理和剛才的差不多。採用科學計數法，任何一個實數S都可以描述為A.B×10n，A表示整數部分，B表示小數部分，而N表示10的指數部分。當S大於1時，N大於等於0，S小於1時，N小於0。也就是說，採用指數的方式表示，N大於0和N小於0的情況各佔一半左右時，Oracle所表示的範圍最廣。因此，Oracle選擇了C1表示個位是最高位的情況。

SQL> SELECT TO_CHAR(ROUND(TO_NUMBER('81', 'XXX') + (TO_NUMBER('FF', 'XXX') - TO_NUMBER('81', 'XXX') + 1)/2), 'XX') FROM DUAL;

TO_
---
C1

　　為什麼ORACLE使用0x2表示1，而不直接使用0x1表示1呢？Oracle每個位元組表示2位數，因此對於這個2位數，出現的可能是0～99共100種可能，問題出在0這裡。Oracle底層是用C語言實現的，我們知道二進位制0在C語言中用作字串終結符，Oracle為了避免這個問題，因此使用了0x1表示0，並依次類推，使用0x64表示99。

　　3.DUMP(2)的結果是0xc103。

　　4.DUMP(25)的結果是0xc11a。前面提到，資料部分是以2位為最小單位儲存的。因此對於25來說，最高位表示位仍然是個位，個位上的值是25，根據上面推出的規則，25在儲存為0xc11a。

SQL> SELECT TO_CHAR(25 + 1, 'xx') FROM DUAL;

TO_
---
1a

　　5.DUMP(123)的結果是0xc20218。由於123最高為是百位，所以最高位表示位為0xc2，百位上是1，用0x02表示，個位上是23，用0x18表示。

　　6.DUMP(4100)的結果是0xc22a。

　　注意一點，如果數字最後數位上如果是0，Oracle出於節省空間的考慮不會儲存。比如：4100只儲存百位上的41，12000000只儲存百位位上的12，512000只儲存萬位上的51和百位上的20。

　　7.DUMP(132004078)的結果是0xc5022101294f。最高位是億位，因此用0xC5表示，億位上是1用0x02表示，百位位上是32用0x21表示，萬位上是0用0x01表示，百位上是40用0x29表示，個位上78用0x4F表示。

　　注意：中間數位上的0不能省略。

　　8.DUMP(2.01)的結果是0xc10302。最高位是個位用0xC1表示，個位上是2用0x03表示，百分位上是1用0x02表示。

　　注意：個位下面一位是百分位不是十分位。

　　9.DUMP(0.3)的結果是0xc01f。最高位是百分位，使用0xC0表示，百分位上是30用0x1F表示。

　　10.DUMP(0.00000125)的結果是0xbe021a。最高位是百萬分位，用0xBE表示，最高位上的1用0x02表示，25用0x1a表示。

　　11.DUMP(115.200003)的結果是0xc20210150104。

　　12.DUMP(-1)的結果是0x3e6466。最高位個位，用0x3E表示，64表示個位上是1，66是符號位，表示這個數是負數。

　　負數和正數互為相反數，負數的最高位表示位和它對應的相反數的最高位相加的值是FF。1的最高位表示位是C1，-1的最高位表示位是3E。負數中1用64表示。負數中的數值和它相反數的資料相加是0x66，也就是符號位。正數1用0x02表示，負數1用0x64表示，二者相加是0x66。負數多個一個標識位，用0x66表示。由於正數的表示範圍是0x01到0x64，負數的表示範圍是0x65到0x02。因此，不會在表示數字時出現的0x66表示。

　　13.DUMP(-5)的結果是0x3e6066。0x3e表示最高位是個位，0x60表示個位上是5，0x66是符號標識位。0x3E加0xC1是0xFF。0x60加0x06的結果是0x66。

　　14.DUMP(-20032)的結果是0x3c63654566。最高位是萬位，正數的萬位是0xC3，因此負數的萬位是0x3C。萬位上是2，正數用0x03表示，負數為0x63，百位上是0，正數用0x01表示，負數使用0x65表示，個位上是32，正數用0x21表示，負數使用0x45表示。0x66是負數表示位。

　　15.DUMP(-234.432)的結果是0x3d63433a5166。

　　根據Oracle的儲存特性，還可以推出Oracle的number型別的取值範圍。

　　Oracle的concept上是這樣描述的：

The following numbers can be stored in a NUMBER column:

Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits.

Negative numbers from -1 x 10^-130 to 9.99...99 x 10^125 with up to 38 significant digits.

Zero.

　　下面來推匯出取值範圍。

來看符號位，0xC1表示個位。

SQL> select to_number('ff', 'xxx') - to_number('c1', 'xxx') from dual;

TO_NUMBER('FF','XXX')-TO_NUMBER('C1','XXX')
-------------------------------------------
62

　　由於Oracle是兩位、兩位儲存的，因此最高位相當於62×2=124，而且最高位上最大值是99，因此正數的最大值為9.999……×10^125。

SQL> select to_number('c1', 'xxx') - to_number('80', 'xxx') from dual;

TO_NUMBER('C1','XXX')-TO_NUMBER('80','XXX')
-------------------------------------------
65

　　最高位相當於65×2=130，因此正數的最小值為1×10^-130。

　　負數和正數在各使用了一半的編碼，因此具有相同的極值範圍。