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 column
b、使用小数位(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.456
scott@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 column
c、负小数位的情形
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被舍掉，结果为100
123.456 100 -->同上
scott@CNMMBO> insert into t select '987.65',987.65 from dual;
scott@CNMMBO> select * from t;
NUM_MSG NUM
------------ ----------
123.45 12300
123.45 100
123.456 100
987.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 column
d、最大值与最小值
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 overflow
scott@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+125
min_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 99999999999999999999999999999
scott@CNMMBO> column v1 format 99
scott@CNMMBO> column v2 format 99
scott@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仅仅存储有效数字，以及指定小数点位置的指数，数值的符号信息等。