Oracle 資料類型及儲存方式（二）

第二部分 數實值型別
§ 2.1  number
Number類型是oralce的數實值型別，儲存的數值的精度可以達到38位。Number是一種變長類型,長度為0-22位元組。取值範圍為:10e-130 – 10e 126(不包括)
Number(p,s)
P和s都是可選的。
P指精度(precision)，即總位元。預設情況下精度為38。精度的取值範圍為1~38.
S指小數位(scale).小數點右邊的位元。小數點位元的合法值為-48~127。小數位的預設值由精度來決定。如果沒有指定精度，小數位預設為最大的取值區間.如果指定了精度，沒有指定小數位。小數位預設為0(即沒有小數位).
精度和小數位不會影響資料如何儲存，只會影響允許哪些數值及數值如何舍入。
1.建立一個表
SQL> create table test_number(col_number number(6,2));
Table created
2.插入一些不同的資料
SQL> insert into test_number values(-1);
1 row inserted
SQL> insert into test_number values(0);
1 row inserted
SQL> insert into test_number values(1);
1 row inserted
SQL> insert into test_number values(2);
1 row inserted
SQL> insert into test_number values(11.00);
1 row inserted
SQL> insert into test_number values(11.11);
1 row inserted
SQL> insert into test_number values(1234.12);
1 row inserted
SQL> insert into test_number values(-0.1);
1 row inserted
SQL> insert into test_number values(-11.11);
1 row inserted
SQL> insert into test_number values(-1234.12);
1 row inserted
SQL> commit;
Commit complete
3.查看結果
SQL> select * from test_number;
COL_NUMBER
----------
     -1.00
      0.00
      1.00
      2.00
     11.00
     11.11
   1234.12
     -0.10
    -11.11
  -1234.12
10 rows selected
5. 查看儲存結構
SQL> select col_number, dump(col_number) from test_number;
COL_NUMBER DUMP(COL_NUMBER)
---------- --------------------------------------------------------------------------------
     -1.00 Typ=2 Len=3: 62,100,102
      0.00 Typ=2 Len=1: 128
      1.00 Typ=2 Len=2: 193,2
      2.00 Typ=2 Len=2: 193,3
     11.00 Typ=2 Len=2: 193,12
     11.11 Typ=2 Len=3: 193,12,12
   1234.12 Typ=2 Len=4: 194,13,35,13
     -0.10 Typ=2 Len=3: 63,91,102
    -11.11 Typ=2 Len=4: 62,90,90,102
  -1234.12 Typ=2 Len=5: 61,89,67,89,102
10 rows selected
由此可見：
Number類型的內部編碼為：2
根據每一行的len值可以看出，number是一個變長類型。不同的數值佔用不同的空間。
如果指定了精度，顯示結果與精度相關。
就像我插入語句寫為
insert into test_number values(0);
但是顯示結果為：0.00
如果數值是負數，在最後一位上填充一個補碼102.即表示該數值為負數。
0是一個特殊的值，它在oracle中儲存為128.
第一位為標誌位。以128為比較。如果數值大於128,則它大於0。如果小於128小於0。
-1的內部儲存為：
-1.00 Typ=2 Len=3: 62,100,102
最後一位是102，是一個負數。
第一位小於128，所以小於10.
除了第一位標誌位外，其它的都是數值為了。
如果該值是一個正數。每一位的儲存值減1為每一位的實際值。
1.0的儲存結構為：
1.00 typ=2 Len=2: 193,2
實值上1.00的儲存結果與1相同。
第一位193為標誌位，大於128，大於0.
第二位為數值為，因為是正數，實際值為儲存值減1。2-1 = 1。
如是該值是一個負數，每一位的實際值為101 減去儲存的值。
-1.00的儲存結構為：
-1.00 Typ=2 Len=3: 62,100,102
最後一位102為補位。
第一位62為標誌位，小於128。實際值小於0.
第二位為數值為，因為是負數。實際值為:101 – 100  =1.
§2.2 小數位在哪裡？
從上面的儲存結果看，對小數儲存時，它並沒有一個小數的標誌位。但是它實際上是由第一位標誌位，和數值位(第二位)來決定的。
當儲存的數是一個正數,該數值的前幾位為：第一位 * power(100 , (標誌位 - 193));
當儲存的數是一個負數，該數值的前幾位為：第一位 * power(100,(62 – 標誌位));
11.11的儲存結果為：
11.11 Typ=2 Len=3: 193,12,12
第一位元值位為：12 實際數值為11
標誌位為：193
12 * power(100, (193- 193);
   100的零次方為1.
12 乘1 等於12.
所以這個數的前幾位為：12。從這後面就是小數了。
1234.12的儲存結構為：
1234.12 Typ=2 Len=4: 194,13,35,13
第一位元值位為：13,實際值為12
標誌位為：193
13 * power(100,(194-193)) = 1300
所以前四位為整數位，後面的為小數位。
-0.10的儲存結構為：
-0.10 Typ=2 Len=3: 63,91,102
標誌位為：63
第一位元值為：91 ，實際值為：10
91 * (100,(62-63)) =-9100.
所以小數位在91之前。
-1234.12的儲存結構為：
-1234.12 Typ=2 Len=5: 61,89,67,89,102
標誌位為：61
第一位元值為：89
89*(100,(62-61)) =8900
所以小數位在67之後。
§2.3 number的精度和小數位
Number類型的精度最多可是38位。小數位-84--127位。
SQL> create table test_number1(col_number number(39));
create table test_number1(col_number number(39))
ORA-01727: numeric precision specifier is out of range (1 to 38)
指定小數位時，精度只能是1-38。不能是0
SQL> create table test_number1(col_number number(0,127));
create table test_number1(col_number number(0,127))
ORA-01727: numeric precision specifier is out of range (1 to 38)
SQL> create table test_number1(col_number number(1,128));
create table test_number1(col_number number(1,128))
ORA-01728: numeric scale specifier is out of range (-84 to 127)
精度與小數位的關係。精度並不是小數位加整數位之和。
我們先看看小數位為0的情況。
SQL> create table test_number1(col_char varchar2(200), col_num number(10));
Table created
Number(10).只定義了精度，小數位為0.
看看它可以存放的資料。
SQL> insert into test_number1 values('9999999999',9999999999);
1 row inserted
插入了10個9，沒有問題，再插入多一位看看
SQL> insert into test_number1 values('99999999991',99999999991);
insert into test_number1 values('99999999991',99999999991)
ORA-01438: value larger than specified precision allowed for this column
報錯了，精度不夠。
再看看能不能再插入小數？
SQL> insert into test_number1 values('0.9',0.9);
1 row inserted
SQL> select * from test_number1;
Col_char COL_NUM
-------------------- --------------
9999999999 9999999999
0.9              1
注意插入數值0.9後，儲存為1.這就是小數位的作用。在哪裡進行舍入。
帶小數位和精度的情況。
SQL> create table test_number2(col_char varchar(20),col_num number(1,3));
Table created
精度是1，小數位是3.
可見，精度不是小數位加整數位了。但是精度和小數位倒底什麼關係呢？
SQL> insert into test_number2 values('0.111',0.111);
insert into test_number2 values('0.111',0.111)
ORA-01438: value larger than specified precision allowed for this column
插入3位小數,0.111竟然報錯了，說精度不夠。
SQL> insert into test_number2 values('0.001',0.001);
1 row inserted
插入0.001時，成功了。
SQL> insert into test_number2 values('0.001',0.0015);
1 row inserted
插入0.0015也成功了。
看看插入的值。
SQL> select * from test_number2;
COL_CHAR             COL_NUM
-------------------- -------
0.001                  0.001
0.0015                 0.002
需要注意的是0.0015被舍入為0.002
精度大於小數位
SQL> create table test_number3 (col_char varchar(20), col_number number(5,3));
Table created
SQL> insert into test_number3 values('99.899',99.899);
1 row inserted
SQL> insert into test_number3 values('99.999',99.999);
1 row inserted
SQL> insert into test_number3 values('99.9999',99.9999);
insert into test_number3 values('99.9999',99.9999)
ORA-01438: value larger than specified precision allowed for this column
注意，當插入99.9999時，系統報錯。因為小數位為3位。第四位小數位是9,於是往前入。最終變成100.000.就已經超過了精度。
Number(5,3)可儲存的數值最大為99.999.
現在終於有點明白小數位與精度的關係了。
number(38,127)
可以儲存的最大小數為：127位小數，最後38為9.
即：0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999
小數位為負數。
我們從前面知道，小數位的取值為-48 ~127
為什麼小數位會為負數？這有點怪異了。像上面的number(5,3)將值舍入為最接近0.001
Number(5,-3)就是將值舍入為最接近的1000
SQL> create table test_number5 (col_char varchar(20), col_num number(5,-3));
Table created
插入值10999
SQL> insert into test_number5 values('10999',10999);
1 row inserted
查看一下結果
SQL> select * from test_number5;
COL_CHAR             COL_NUM
-------------------- -------
10999                  11000
儲存的結果為：11000
當小數部分為負數時，是對小數部分進行舍入。
那麼精度在這時起到什麼作用呢？與小數位又有什麼關係？
SQL> insert into test_number5 values('111111111',111111111);
insert into test_number5 values('111111111',111111111)
ORA-01438: value larger than specified precision allowed for this column
插入9個1時，報錯精度不夠。
SQL> insert into test_number5 values('11111111',11111111);
1 row inserted
插入8個1時，正確插入。
我們看看它的結果，看它是怎麼舍入的。
SQL> select * from test_number5;
COL_CHAR             COL_NUM
-------------------- -------
11111111             11111000
結果是1111100而不是1111100
無限接近1000，就是從百位開始進行四捨五入，後面的值全部為0。
所以看出number(5,-3)可儲存的最大值為：99999000
SQL> insert into test_number5 values('99999499.999999',99999499.999999);
1 row inserted
SQL> select * from test_number5;
COL_CHAR             COL_NUM
-------------------- -------
99999999             99999000
99999499.999999      99999000
現在應該明白了精度和小數位的關係了吧。
小數位告訴系統保留多少位小數，從哪裡開始舍入。
精度舍入後，從舍入的位置開始，數值中允許有多少位。
§2.4  binary_float 和binary_double
這兩種類型是oracle 10g新引進的數實值型別。在oracle 10g之前是沒有這兩種類型的。
Number類型是由oracle軟體支援的類型。而浮點數用於近似數值。但是它浮點數允許由在硬碟上(CPU,晶片)上執行運行。而不是在oracel進程中運算。如果希望在一個科學計算中執行實數處理，依賴於硬體的算術運算速度要快得多。但是它的精度卻很小。如果希望用來儲存金融數值，則必須用number.
BINARY_FLOAT是一種IEEE固有的單精確度浮點數。可儲存6位精度，取值範圍在~±1038.25的數值。
BINARY_DOUBLE是一種IEEE固有的雙精確度浮點數。可儲存12位精度。取值範圍在~±10308.25的數值
SQL> create table test_floatdouble(col_number number, col_float binary_float, col_double binary_double);
Table created
SQL> insert into test_floatdouble values(9876543210.0123456789,9876543210.0123456789,9876543210.0123456789);
1 row inserted
2 SQL> select to_char(col_number), to_char(col_float), to_char(col_double) from test_floatdouble;
3
4 TO_CHAR(COL_NUMBER)                      TO_CHAR(COL_FLOAT)                       TO_CHAR(COL_DOUBLE)
5 ---------------------------------------- ---------------------------------------- ----------------------------------------
6 9876543210.0123456789                    9.87654349E+009                          9.8765432100123463E+009
由此可見，binary_float無法表示這個數。Binary_float和binary_double無法用於對精度要求高的資料。
SQL> select dump(col_float)from test_floatdouble;
DUMP(COL_FLOAT)
--------------------------------------------------------------------------------
Typ=100 Len=4: 208,19,44,6
BINARY_FLOAT 類型編碼為100
Len=4 佔用4個位元組。它是採用固定位元組進行儲存的。
SQL> select dump(col_double)from test_floatdouble;
DUMP(COL_DOUBLE)
--------------------------------------------------------------------------------
Typ=101 Len=8: 194,2,101,128,183,80,25,73
BINARY_DOUBLE 類型編碼為101
Leng= 8 佔用8個位元組。也是採用固定位元組進行儲存。
注意：number 類型使用的CPU時間是浮點數類型的50倍。浮點數是數值的一個近似值，精度在6-12位之間。從Number類型得到的結果要比從浮點數得到的結果更精確。但在對科學資料進行資料採礦和進行複雜數值分析時，精度的損失是可以接受的，還會帶來顯著的效能提升。
這時需要使用內建CAST函數，對NUMBER類型執行一種即時的轉換，在執行複雜數學運算之前先將其轉換為一種浮點數類型。CPU使用時間就與固有浮點類型使用的CPU時間非常接近了。
Select ln(cast(number_col as binary_double)) from test_number.
§2.5 Oracle在文法上還支援的數值資料類型
NUMERIC(p,s):完全映射到NUMBER(p,s)。如果p未指定，則預設為38.
DECIMAL(p,s)或DEC(p,s):同NUMERIC(p,s).
INTEGER或int:完全映射至NUMBER(38)
SMALLINT:完全映射至NUMBER(38)
FLOAT(b):映射至NUMBER
DOUBLE PRECISION:映射到NUMBER
REAL:映射到NUMBER.