資料庫科學計數法轉換
1. oracle怎樣將科學計數法還原為原數字
文章主要描述NUMBER類型的數據和如何在資料庫中存儲的。Oracle的NUMBER類型最多由三個部分構成,這三個部分分別是最高位表示位、數據部分、符號位。其中負數包含符號位,正數不會包括符號位。另外,數值0比較特殊,它只包含一個數值最高位表示位80,沒有數據部分。
正數的最高位表示位大於80,負數的最高位表示位小於80。其中一個正數的最高位是個位的話,則最高位表示位為C1,百位、萬位依次為C2、C3,百分位、萬分為依次為C0、BF。
一個負數的最高位為個位的話,最高位表示位為3E,百位、萬位依次為3D、3C,百分位、萬分位依次為3F、40。
數據部分每一位都表示2位數。這個兩位數可能是從0到99,如果是數據本身是正數,則分別用二進制的1到64表示,如果數據本身是負數,則使用二進制65到2表示。
oracle怎樣將科學計數法還原為原數字
符號位用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 行。
oracle怎樣將科學計數法還原為原數字
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怎樣將科學計數法還原為原數字
因此,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表示。
oracle怎樣將科學計數法還原為原數字
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。
oracle怎樣將科學計數法還原為原數字
最高位個位,用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。
oracle怎樣將科學計數法還原為原數字
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 al;
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 al;
TO_NUMBER(C1,XXX)-TO_NUMBER(80,XXX)
-------------------------------------------
65
最高位相當於65×2=130,因此正數的最小值為1×10^-130。
負數和正數在各使用了一半的編碼,因此具有相同的極值范圍。
2. 如何把科學計數法的數字變成數值
在EXCEL中，將科學計數法的數字變成數值的方法如下：
1、以2007版Excel為例，打開任意EXCEL工作表，在A1單元格輸入11位以上的數字，如下圖所示，數值變成了科學計數格式。
3. 資料庫直接讀出來的數據在界面上顯示科學計數法怎麼辦
你是用excel導入的吧，excel的表格默認是數值型，所以導入到資料庫里的數據都是數值型
而數值型過長的話會轉變成科學計數法
如果要用excel導入到資料庫，需要先把表格那列換成文本型，而且一定要出現文本型的綠色小三角號
4. sql列內容科學計數法需要轉換成標准數值
這個問題我遇到過。辦法就是資料庫建表的時候把對應列定義成字元型，如果需要計算或判斷的時候記得轉換格式，比如 oracle 中用 to_number，其他的可能是 cast。