第二部分 数值类型
§ 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.