ORACLE数值类型详解--NUMBER、FLOAT、BINARY_FLOAT、BINARY_DOUBLE、BINARY_INTEGER

来源：互联网发布：淘宝的人参珍珠膏编辑：程序博客网时间：2024/05/16 08:52

在9i之前，ORACLE为我们提供了Number数值类型，在10g中，ORACLE又引入了BINARY_FLOAT和BINARY_DOUBLE数据类型。除此之外，还有integer，smallint等数据类型，但这些类型的底层实现依然是NUMBER、BINARY_FLOAT、BINARY_DOUBLE。因此，我们可以认为ORACLE总共为我们提供了三种存储数值的数据类型：NUMBER、BINARY_FLOAT、BINARY_DOUBLE。通过这三种数值类型，可以存储正值，负值，0，无穷大和NAN（not a number）。

NUMBER

number类型在oracle中会占用0~22个字节的存储空间，是一种变长数据类型，采用oracle内部算法，是一种软数据类型，因此具有较好的数据精确度，通用性和可移植性较强，但是其运算效率相对一浮点类型（硬件运算）偏低。在0~22个字节的存储空间中，一个字节用于存储数据长度，21个字节用于存储数据（其中一个字节存储与符号和指数相关的内容，20个字节用于存储数据）。

number存储数据的范围如下：

正数：1 x 10^-130 to 9.99...9 x 10¹²⁵ ,数据精度为38个有效数字

负数：-1 x 10^-130 to 9.99...99 x 10¹²⁵ ，数据精度为38个有效数字

零：0

无穷大：仅仅只可以从oracle 5中导入

number有效数字

可以通过如下格式来指定：COLUMN_NAME number（precision ，scale），其中precision指number可以存储的最大数字长度（不包括左右两边的0），scale指在小数点右边的最大数字长度（包括左侧0）。如果我们指定了p和s，可以按照如下算法来判读数据是否有效：如果s>=0，首先我们从小数点从左向右数s个数字，如果在此之右，仍然有数据，则将其截断，然后我们在当前位置再向左侧数p个数字，如果在此之左仍有非零数字，则数字不合法，否则数据合法；如果s<0,首先我们从小数点从右向做数|s|个数字，并截断其右侧数据，然后我们在当前位置再向左侧数p个数字，如果在此之左仍有非零数字，则数字不合法，否则数据合法。

如果我们没有指定phes如：column_name number,则数字按照输入格式存储；如果我们指定了p，但是没有指定s，则s默认为0，如column_name number(p)；如果我们指定了s但是没有指定p，则p默认为38，如column_name number（*，s）。

p的取值范围为1-38，在官方文档中有这样一段话：

Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.由于在内部采用20个字节存储数据，每个字节存储2位数字，因此最多可以存放40个数字，及文档中谈及的40，但是如果小数点左侧的数字数量为奇数，则左侧补一位0，那么这是有效数字就最大为39。在语法上，oracle规定precision为38，可能是为了稳妥或者其他的原因。

s的取值范围为-84~127，这也是与number的内部存储格式有关，不做过多的讨论。

SQL> create table t6(c1 number,c2 number(*,1),c3 number(9),c4 number(9,2),c5 number(9,1),c6 number(6),c7 number(7,-2));表已创建。SQL> insert into t6 values(7456123.89,7456123.89,7456123.89,7456123.89,7456123.89,7456123.89,7456123.89);insert into t6 values(7456123.89,7456123.89,7456123.89,7456123.89,7456123.89,7456123.89,7456123.89)                                                                             *第 1 行出现错误:ORA-01438: 值大于为此列指定的允许精度SQL> insert into t6 values(7456123.89,7456123.89,7456123.89,7456123.89,7456123.89,0,7456123.89);已创建 1 行。SQL> commit;

SQL> select * from t6;  C1       C2    C3 C4      C5   C6C7------------ ------------ ------------ ------------ ------------ ------------ ------------  7456123.897456123.9      7456124 7456123.89    7456123.9    0   7456100

number存储结构

number数据类型的存储结构，可以通过dump函数来展现，在探究number存储结构之前，让我们先来看一下dump函数的使用方法。

DUMP函数的输出格式类似：

类型 <[长度]>，符号/指数位 [数字1，数字2，数字3，……，数字20]

其中各参数的含义如下：

·类型指字段数据类型，Number型，Type=2（类型代码可以从Oracle的文档上查到）；
·长度指存储的字节数；
·符号/指数位用于代表数字的正负及指数值；
·数据存储。

oracle采用21个字节存储number，第一个字节为头信息，随后20个字节存储有效数字。

头信息数据域1 数据域2 .............. 数据域n （n<=20)

XXXXXXXXXXXXXXXX XXXXXXXX ............XXXXXXXX

| | |-一个字节存储2个十进制数字，可以存储0-99，因为0在c语言中作为特殊字符，因此用1-100来存储0-99，即在原值上加1。在数据域不存储正负符号，为了

| | |-排序的方便，对于负数，我们用101-2来存储0-99（为什么不用100-1，请高手指教），在负数的最后会添加一个值为102的字节，这是为了避免排序错误

| |-数据指数范围标识位，当正数时，该数值与64做差（x-64），其结果为指数，当负数时，63与该数值做差（63-x），其结果为指数。在oracle中0X80,代表0，因此对于

| |-正数，其指数范围为-63~0（小数） 1~63（非小数）；对于负数，指数范围为63~1（非小数）,0~-64（小数）

|-符号信息位，1代表正数，0代表负数

查询是，对于存储在数据域的数据，oracle会将每个字节转换为十进制，然后拼接各个字节。如果指数域的值为正数n，那么会将小数点定位在第n个字节的后面，如果为0，则小数点在第一个字节的前面，如果为负数，则小数点在第一个字节向前推进n个字节。

下面，我们通过实例来看一下上面的说法正不正确：

desc tab1名称 空值 类型            -- -- ------------- C1    NUMBER        C2    VARCHAR2(300)

如果负数的精度很大，102比较位可能会被舍弃，因此此时102已经起不到任何作用了。

SQL> insert into tab1 values(-111111111111111111111111111111111111111111111111111111111111);已创建 1 行。SQL> select dump(c1) from tab1;DUMP(C1)--------------------------------------------------Typ=2 Len=2: 193,2Typ=2 Len=3: 62,100,102Typ=2 Len=1: 128Typ=2 Len=6: 197,2,24,46,68,90Typ=2 Len=7: 58,100,78,56,34,12,102Typ=2 Len=12: 58,100,78,56,34,12,3,25,47,69,91,102Typ=2 Len=11: 197,2,24,46,68,90,99,77,55,33,11Typ=2 Len=21: 33,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90,90

number变种

除number类型外，oracle还为我们提供了一些number的变种数据类型，这些类型其内部实现仍然是number，我们可以将其理解为number的特定别名。

NUMERIC(p,s)：完全映射至NUMBER(p,s)。如果p未指定，则默认为38.

DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)。如果p为指定，则默认为38

INTEGER或INT：完全映射至NUMBER(38)类型。

SMALLINT：完全映射至NUMBER(38)类型。
FLOAT(b)：映射至NUMBER类型。
DOUBLE PRECISION：映射至NUMBER类型。
REAL：映射至NUMBER类型。

在这里需要特别注意的是float类型，参加官方文档：

The FLOAT data type is a subtype of NUMBER. It can be specified with or without precision, which has the same definition it has for NUMBER and can range from 1 to
126. Scale cannot be specified, but is interpreted from the data. Each FLOAT value requires from 1 to 22 bytes.

To convert from binary to decimal precision, multiply n by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

由此可知，float中的有效数指的是二进制格式的有效数，转换为10进制为b*0.30103. 如果结果不是整数，则采用上侧整数作为底层number存储的precision。如：

SQL> desc tab1 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- C1                                                 FLOAT(2) C2                                                 FLOAT(4) C3                                                 FLOAT(6) C4                                                 FLOAT(7) C5                                                 FLOAT(9) C6                                                 FLOAT(11) C7                                                 FLOAT(13)SQL> insert into tab1 values(1234567890.0987654321,1234567890.0987654321,1234567890.0987654321,1234567890.0987654321,1234567890.0987654321,1234567890.0987654321,1234567890.0987654321);已创建 1 行。SQL> select * from tab1;        C1         C2         C3         C4         C5         C6         C7---------- ---------- ---------- ---------- ---------- ---------- ----------1000000000 1200000000 1200000000 1230000000 1230000000 1235000000 1235000000

PLS_INTEGER和BINARY_INTEGER

PLS_INTEGER和BINARY_INTEGER是plsql数据类型，无法在表中直接使用。在11g的官方文档中，官方指出PLS_INTEGER和BINARY_INTEGER两者是相同的，是有符号的整数类型，其取值范围为-2^31 ~ 2^31，共占用32个比特位。其内部存储格式，尚无资料表明，通过dump函数查看其为number类型，这可能是不正确的（补充：后来dump varchar2和nvarchar2数据类型时，其typ均为1，由此可见dump可能对相似的数据类型使用相同的typ），因为官方文档明确指出了PLS_INTEGER BINARY_INTEGER通number的一下区别：

1.存储空间要比number小

2.计算效率要比number高，因此可以直接硬件运算，而number是软运算。

如果赋值范围超出了PLS_INTEGER和BINARY_INTEGER的范围，将会发生溢出异常；

DECLARE  p1 PLS_INTEGER := 2147483647;  p2 PLS_INTEGER := 1;  n NUMBER;BEGIN  n := p1 + p2;END;/Result:DECLARE*ERROR at line 1:ORA-01426: numeric overflowORA-06512: at line 6

在11g中，oracle引入了simple_integer，该类型是PLS_INTEGER的子类型，取值范围相同，但是该类型不会进行空值和溢出检测（必须人为保证非空和不会发生溢出），因此与PLS_INTEGER相比，具有更高的性能。如果可以确定某个值为非空并且不会发生溢出操作，可以使用simple_integer来存储。由于不会进行溢出检测，simple_integer的运算结果可能会发生正负值的转换，例如：

DECLARE  n SIMPLE_INTEGER := 2147483645;BEGIN  FOR j IN 1..4 LOOP    n := n + 1;    DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));  END LOOP;  FOR j IN 1..4 LOOP   n := n - 1;   DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));  END LOOP; 12  END; 13  /+2147483646+2147483647-2147483648-2147483647-2147483648+2147483647+2147483646+2147483645PL/SQL 过程已成功完成。

如果将simple_integer转换为pls_integer, 会转换为pls_integer not null。

BINARY_FLOAT和BINARY_DOUBLE

oracle根据IEEE754实现单精度和双精度浮点型，要了解IEEE754，请参照http://blog.csdn.net/yidian815/article/details/12912661

由于存储结构上的不同，BINARY_FLOAT和BINARY_DOUBLE较number，可以存储更大范围的数据，但是其精度并不如number。如果存储金融数据，建议使用number，而如果进行科学运算，建议使用BINARY_FLOAT和BINARY_DOUBLE,因为浮点型数据使用的是硬件计算，其计算效率是非常高的。

我们可以通过如下方式来判断某个浮点型是否为无穷或者非数值。

expr is [not] NAN

expr is [not] INFINITE

ORACLE可以对NAN排序，排序规则是，NAN最大，NAN与NAN比较时，其值相等。infinity和infinity相比，其值相等。

SQL> create table tab1(id number,c1 binary_float,c2 binary_double);表已创建。SQL> insert into tab1 values(1,1,1);已创建 1 行。SQL> insert into tab1 values(2,nan,nan);insert into tab1 values(2,nan,nan)                              *第 1 行出现错误:ORA-00984: 列在此处不允许SQL> insert into tab1 values(2,binary_float_nan,binary_double_nan);已创建 1 行。SQL> insert into tab1 values(3,binary_float_infinity,binary_double_infinity);已创建 1 行。SQL> insert into tab1 values(4,-binary_float_infinity,-binary_double_infinity);已创建 1 行。SQL> commit;提交完成。SQL> select * from tab1;    ID       C1  C2------ ---------- ----------     1 1.0E+000   1.0E+000     2      Nan Nan     3      Inf Inf     4     -Inf-InfSQL> select * from tab1 order by c1;    ID       C1  C2------ ---------- ----------     4     -Inf-Inf     1 1.0E+000   1.0E+000     3      Inf Inf     2      Nan NanSQL> select * from tab1 where c1 is infinite;    ID       C1  C2------ ---------- ----------     3      Inf Inf     4     -Inf-InfSQL> select * from tab1 where c1 is nan;    ID       C1  C2------ ---------- ----------     2      Nan NanSQL> select * from tab1 where c1 = binary_float_infinity;    ID       C1  C2------ ---------- ----------     3      Inf InfSQL> select * from tab1 where c1 != binary_float_infinity;    ID       C1  C2------ ---------- ----------     1 1.0E+000   1.0E+000     2      Nan Nan     4     -Inf-InfSQL> select * from tab1 where c1 = (select c1 from tab1 where id =2);    ID       C1  C2------ ---------- ----------     2      Nan NanSQL> select * from tab1 where c1 = nan;select * from tab1 where c1 = nan                              *第 1 行出现错误:ORA-00904: "NAN": 标识符无效SQL> select * from tab1 where c1 = binary_float_nan;    ID       C1  C2------ ---------- ----------     2      Nan NanSQL> select * from tab1 where c1 = (select c1 from tab1 where id =3);    ID       C1  C2------ ---------- ----------     3      Inf InfSQL>

优先级：在数据转换时，优先转换为高级别的数据类型

date type > binary_double > binary_float > number > char