SQL, PL/SQL 之NUMBER資料類型

    NUMBER資料類型在Oracle中使用的較為廣泛，可以儲存零值，正負數，以及定長數，對於這個資料類型有個幾個概念要搞清，否則容易搞混，下面給出具體描述。

 

1、可表示範圍及儲存空間
    從1.0 x 10-130 到 1.0 x 10126(不包括)，如果運算式或值大於1.0 x 10126，Oracle會返回錯誤資訊
    所需的儲存空間為1到22個位元組
 
2、Number類型標記法
    NUMBER(p,s)   P 和S 可選

    其中precision表示數位總長度，scale代表可以有幾位小數。
    precision也叫精度，是指數中的總數字個數，預設情況下，精度為38 位，取值範圍是1～38 之間。
    scale是小數位元，即數中小數點右邊的數字個數。其範圍從-84到127，能夠決定舍入規則。如果我們不指定scale的值，預設就為0。
    不可以用常量或變數指定NUMBER的長度和精度。NUMBER類型最大的長度是38位。
    如果不指定NUMBER類型的最大長度，就會採用預設長度或是使用系統所支援的最大長度。
    精度和小數位元不會影響資料在磁碟上如何儲存，而只會影響允許有哪些值以及數值如何舍入（round）。

    例如，數 123.45 的精度是 5，小數位元是 2。
  
    下面對p和s進行分析
    p>0，對s分2種情況分析：
  
  a. s>0
    精確到小數點右邊s位，並四捨五入。然後檢驗有效數位是否<=p；如果s>p，小數點右邊至少有s-p個0填充。
  
  b. s<0
    精確到小數點左邊s位，並四捨五入。然後檢驗有效數位是否<=p+|s|
  
    (有效數位：從左邊第一個不為0的數算起)
  
    對於浮點數則不考慮精度問題
  
  c、表示整數
    當s的值被省略時，即等同於s等於0，表示整數
  
    NUMBER(p) 等同於NUMBER(p,0)
  
  c、浮點型
    當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

3、樣本

a、使用精度(precision)保證資料的完整性scott@CNMMBO> create table t(num number(5));scott@CNMMBO> insert into t select 12345 from dual;scott@CNMMBO> insert into t select 123456 from dual;    -->給出錯誤資訊，超出精度範圍insert into t select 123456 from dual                   -->精度為5，而實際的資料位元有6位                     *ERROR at line 1:ORA-01438: value larger than specified precision allowed for this columnb、使用小數位(scale)scott@CNMMBO> truncate table t;scott@CNMMBO> alter table t modify(num number(5,2));scott@CNMMBO> alter table t add num_msg varchar2(12);scott@CNMMBO> desc t; Name                    Null?    Type ----------------------- -------- --------------- NUM                              NUMBER(5,2) NUM_MSG                          VARCHAR2(12)scott@CNMMBO> insert into t select 123.45,'123.45' from dual; scott@CNMMBO> insert into t select 123.456,'123.456' from dual;  -->此時的number進行了四捨五入scott@CNMMBO> select * from t;       NUM NUM_MSG---------- ------------    123.45 123.45    123.46 123.456scott@CNMMBO> insert into t select 1234,'1234' from dual; -->同樣給出超出精度的錯誤提示insert into t select 1234,'1234' from dual                -->此處的1234並不是1234，Oracle根據該列的定義會轉換為1234.00                     *                                    -->因為指定了2位小數，因此小數點左邊最後只能有3位，右邊為2位ERROR at line 1:ORA-01438: value larger than specified precision allowed for this columnc、負小數位的情形    scott@CNMMBO> truncate table t;  -->清空之前的資料scott@CNMMBO> alter table t modify(num number(5,-2)); -->修改列的scale為負數scott@CNMMBO> desc t Name                Null?    Type ------------------- -------- ------------------ NUM_MSG                      VARCHAR2(12) NUM                          NUMBER(5,-2)            scott@CNMMBO> insert into t select '123.45',12345 from dual;   scott@CNMMBO> insert into t select '123.45',123.45 from dual;scott@CNMMBO> insert into t select '123.456',123.456 from dual;scott@CNMMBO> select * from t;NUM_MSG             NUM------------ ----------123.45            12300     -->輸入的12345為整數，即12345.00，小數位之前45被舍掉123.45              100     -->輸入的123.45，同樣由於scale為-2，23被舍掉，結果為100123.456             100     -->同上scott@CNMMBO> insert into t select '987.65',987.65 from dual;scott@CNMMBO> select * from t;NUM_MSG             NUM------------ ----------123.45            12300123.45              100123.456             100987.65             1000  -->未超出進度的情況下，產生了進位scott@CNMMBO> insert into t select '98765432',98765432 from dual;  -->超出精度insert into t select '98765432',98765432 from dual                                *ERROR at line 1:ORA-01438: value larger than specified precision allowed for this columnd、最大值與最小值scott@CNMMBO> truncate table t;scott@CNMMBO> alter table t modify(num number);scott@CNMMBO> insert into t select 'max_value',power(10,126)-1 from dual;insert into t select 'max_value',power(10,126)-1 from dual                                 *ERROR at line 1:ORA-01426: numeric overflowscott@CNMMBO> insert into t select 'max_value',power(10,125) from dual; 10的125次方可以成功插入scott@CNMMBO> insert into t select 'min_value',power(10,-130) from dual;scott@CNMMBO> select * from t;NUM_MSG             NUM------------ ----------max_value    1.000E+125min_value    1.000E-130-->從上面的測試可知，使用number來用作sequence，根部無需擔心sequence不夠用的情形d、計算number列的長度scott@CNMMBO> drop table t purge;Table dropped.scott@CNMMBO> create table t(l number,m number);Table created.-->使用vsize過的number的磁碟佔用空間scott@CNMMBO> insert into t(l) select to_number(rpad('9',rownum*2,'9')) from dba_objects  2  where rownum<=12;12 rows created.scott@CNMMBO> update t set m=l+1;12 rows updated.scott@CNMMBO> set numformat 99999999999999999999999999999scott@CNMMBO> column v1 format 99scott@CNMMBO> column v2 format 99scott@CNMMBO> select l,m,vsize(l) v1, vsize(m) v2 from t order by l;                             L                              M  V1  V2------------------------------ ------------------------------ --- ---                            99                            100   2   2                          9999                          10000   3   2                        999999                        1000000   4   2                      99999999                      100000000   5   2                    9999999999                    10000000000   6   2                  999999999999                  1000000000000   7   2                99999999999999                100000000000000   8   2              9999999999999999              10000000000000000   9   2            999999999999999999            1000000000000000000  10   2          99999999999999999999          100000000000000000000  11   2        9999999999999999999999        10000000000000000000000  12   2      999999999999999999999999      1000000000000000000000000  13   2      -->對於列L,隨著值的不斷變大，其所耗用的儲存空間也不但增加，呈線性增長。-->對於列M，其所用的儲存空間保持不變-->從上可知，並非數值越大，耗用的儲存空間越多。Oracle僅僅儲存有效數字，以及指定小數點位置的指數，數值的符號資訊等。

4、更多參考

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫