SQL, PL/SQL 之NUMBER数据类型

 

 

http://blog.csdn.net/leshami/article/details/8153826

 

 

 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、示例

[sql] view plaincopyprint?

1. a、使用精度(precision)保证数据的完整性  
2. scott@CNMMBO> create table t(num number(5));  
3.   
4. scott@CNMMBO> insert into t select 12345 from dual;  
5.   
6. scott@CNMMBO> insert into t select 123456 from dual;    -->给出错误信息，超出精度范围  
7. insert into t select 123456 from dual                   -->精度为5，而实际的数据位有6位  
8.                      *  
9. ERROR at line 1:  
10. ORA-01438: value larger than specified precision allowed for this column  
11.   
12. b、使用小数位(scale)  
13. scott@CNMMBO> truncate table t;  
14.   
15. scott@CNMMBO> alter table t modify(num number(5,2));  
16.   
17. scott@CNMMBO> alter table t add num_msg varchar2(12);  
18.   
19. scott@CNMMBO> desc t;  
20.  Name                    Null?    Type  
21.  ----------------------- -------- ---------------  
22.  NUM                              NUMBER(5,2)  
23.  NUM_MSG                          VARCHAR2(12)  
24.   
25. scott@CNMMBO> insert into t select 123.45,'123.45' from dual;   
26.   
27. scott@CNMMBO> insert into t select 123.456,'123.456' from dual;  -->此时的number进行了四舍五入  
28.   
29. scott@CNMMBO> select * from t;  
30.   
31.        NUM NUM_MSG  
32. ---------- ------------  
33.     123.45 123.45  
34.     123.46 123.456  
35.   
36. scott@CNMMBO> insert into t select 1234,'1234' from dual; -->同样给出超出精度的错误提示  
37. insert into t select 1234,'1234' from dual                -->此处的1234并不是1234，Oracle根据该列的定义会转换为1234.00  
38.                      *                                    -->因为指定了2位小数，因此小数点左边最后只能有3位，右边为2位  
39. ERROR at line 1:  
40. ORA-01438: value larger than specified precision allowed for this column  
41.   
42. c、负小数位的情形      
43. scott@CNMMBO> truncate table t;  -->清空之前的数据  
44.   
45. scott@CNMMBO> alter table t modify(num number(5,-2)); -->修改列的scale为负数  
46.   
47. scott@CNMMBO> desc t  
48.  Name                Null?    Type  
49.  ------------------- -------- ------------------  
50.  NUM_MSG                      VARCHAR2(12)  
51.  NUM                          NUMBER(5,-2)              
52.   
53. scott@CNMMBO> insert into t select '123.45',12345 from dual;     
54.   
55. scott@CNMMBO> insert into t select '123.45',123.45 from dual;  
56.   
57. scott@CNMMBO> insert into t select '123.456',123.456 from dual;  
58.   
59. scott@CNMMBO> select * from t;  
60.   
61. NUM_MSG             NUM  
62. ------------ ----------  
63. 123.45            12300     -->输入的12345为整数，即12345.00，小数位之前45被舍掉  
64. 123.45              100     -->输入的123.45，同样由于scale为-2，23被舍掉，结果为100  
65. 123.456             100     -->同上  
66.   
67. scott@CNMMBO> insert into t select '987.65',987.65 from dual;  
68.   
69. scott@CNMMBO> select * from t;  
70.   
71. NUM_MSG             NUM  
72. ------------ ----------  
73. 123.45            12300  
74. 123.45              100  
75. 123.456             100  
76. 987.65             1000  -->未超出进度的情况下，产生了进位  
77.   
78. scott@CNMMBO> insert into t select '98765432',98765432 from dual;  -->超出精度  
79. insert into t select '98765432',98765432 from dual  
80.                                 *  
81. ERROR at line 1:  
82. ORA-01438: value larger than specified precision allowed for this column  
83.   
84. d、最大值与最小值  
85. scott@CNMMBO> truncate table t;  
86.   
87. scott@CNMMBO> alter table t modify(num number);  
88.   
89. scott@CNMMBO> insert into t select 'max_value',power(10,126)-1 from dual;  
90. insert into t select 'max_value',power(10,126)-1 from dual  
91.                                  *  
92. ERROR at line 1:  
93. ORA-01426: numeric overflow  
94.   
95. scott@CNMMBO> insert into t select 'max_value',power(10,125) from dual; 10的125次方可以成功插入  
96.   
97. scott@CNMMBO> insert into t select 'min_value',power(10,-130) from dual;  
98.   
99. scott@CNMMBO> select * from t;  
100.   
101. NUM_MSG             NUM  
102. ------------ ----------  
103. max_value    1.000E+125  
104. min_value    1.000E-130  
105.   
106. -->从上面的测试可知，使用number来用作sequence，根部无需担心sequence不够用的情形  
107.   
108. d、计算number列的长度  
109.   
110. scott@CNMMBO> drop table t purge;  
111.   
112. Table dropped.  
113.   
114. scott@CNMMBO> create table t(l number,m number);  
115.   
116. Table created.  
117.   
118. -->使用vsize过的number的磁盘占用空间  
119. scott@CNMMBO> insert into t(l) select to_number(rpad('9',rownum*2,'9')) from dba_objects  
120.   2  where rownum<=12;  
121.   
122. 12 rows created.  
123.   
124. scott@CNMMBO> update t set m=l+1;  
125.   
126. 12 rows updated.  
127.   
128. scott@CNMMBO> set numformat 99999999999999999999999999999  
129. scott@CNMMBO> column v1 format 99  
130. scott@CNMMBO> column v2 format 99  
131. scott@CNMMBO> select l,m,vsize(l) v1, vsize(m) v2 from t order by l;  
132.   
133.                              L                              M  V1  V2  
134. ------------------------------ ------------------------------ --- ---  
135.                             99                            100   2   2  
136.                           9999                          10000   3   2  
137.                         999999                        1000000   4   2  
138.                       99999999                      100000000   5   2  
139.                     9999999999                    10000000000   6   2  
140.                   999999999999                  1000000000000   7   2  
141.                 99999999999999                100000000000000   8   2  
142.               9999999999999999              10000000000000000   9   2  
143.             999999999999999999            1000000000000000000  10   2  
144.           99999999999999999999          100000000000000000000  11   2  
145.         9999999999999999999999        10000000000000000000000  12   2  
146.       999999999999999999999999      1000000000000000000000000  13   2  
147.         
148. -->对于列L,随着值的不断变大，其所耗用的存储空间也不但增加，呈线性增长。  
149. -->对于列M，其所用的存储空间保持不变  
150. -->从上可知，并非数值越大，耗用的存储空间越多。Oracle仅仅存储有效数字，以及指定小数点位置的指数，数值的符号信息等。  

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语句执行计划