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.