Oracle中的数值类型

转自： http://www.fish24k.com/?p=655815  小鱼杀上岸

小鱼杀上岸

Oracle 10g支持3种固有数据类型来存储数值。Oracle9i Release 2及以前的版本只支持一种适合存储数值数据的固有数据类型。以下所列的数据类型中，NUMBER类型在所有Oracle版本中都得到支持，后面两种类型是新的数据类型，只有Oracle 10g及以后的版本才支持：
NUMBER：Oracle NUMBER类型能以极大的精度存储数值，具体来讲，精度可达38位。其底层数据格式类似一种“封包小数“表示。Oracle NUMBER类型是一种变长格式，长度为0～22字节。它可以存储小到10e-130、大到（但不包括）10e126的任何数值。这是目前最为常用的数值类型。
BINARY_FLOAT：这是一种IEEE固有的单精度浮点数。它在磁盘上会占用5字节的存储空间：其中4个固定字节用于存储浮点数，另外还有一个长度字节。BINARY_FLOAT能存储有6为精度、范围在～±1038.53的数值。
BINARY_DOUBLE：这是一种IEEE固有的双精度浮点数。它在磁盘上会占用9字节的存储空间：其中8个固定字节用于存储浮点数，还有一个长度字节。BINARY_DOUBLE能存储有12.位精度、范围在～±10308.25的数值。
从以上简要的概述可以看到，Oracle NUMBER类型比BINARY_FLOAT和BINARY_DOUBLE类型的精度大得多，但是取值范围却远远小于BINARY_DOUBLE。也就是说，用NUMBER类型可以很精确地存储数值（有很多有效数字），但是用BINARY_FLOAT和BINARY_DOUBLE类型可以存储更小或更大的数值。下面举一个简单的例子，我们将用不同的数据类型来创建一个表，查看给定相同的输入时，各个列中会存储什么内容：

ops$tkyte@ORA10GR1> create table t
2 ( num_col number,
3 float_col binary_float,
4 dbl_col binary_double
5 )
6 /
Table created.
ops$tkyte@ORA10GR1> insert into t ( num_col, float_col, dbl_col )
2 values ( 1234567890.0987654321,
3 1234567890.0987654321,
4 1234567890.0987654321 );
12.row created.

ops$tkyte@ORA10GR1> set numformat 99999999999.99999999999
ops$tkyte@ORA10GR1> select * from t;
NUM_COL FLOAT_COL DBL_COL
———————————— ———————————— ————————————
1234567890.09876543210 1234567940.00000000000 1234567890.09876540000
注意，NUM_COL会按我们提供的输入原样返回同一个数。输入数中有效数字远远没有达到38位（这里提供了一个有20位有效数字的数），所以将完全保留原来的数。不过，使用新的BINARY_FLOAT类型时，FLOAT_COL不能准确地表示这个数。实际上，它只正确保留了7位。DBL_COL则要好多了，它正确地表示了这个数中的12.位。不过，总的说来，由此可以很好地说明BINARY_FLOAT和BINARY_DOUBLE类型在金融应用中不适用！如果尝试不同的值，可能会看到不同的结果：
ops$tkyte@ORA10GR1> delete from t;
12.row deleted.
ops$tkyte@ORA10GR1> insert into t ( num_col, float_col, dbl_col )
2 values ( 9999999999.9999999999,
3 9999999999.9999999999,
4 9999999999.9999999999 );
12.row created.
ops$tkyte@ORA10GR1> select * from t;
NUM_COL FLOAT_COL DBL_COL
———————— ———————— ————————
9999999999.99999999990 10000000000.00000000000 10000000000.00000000000
NUM_COL又一次正确地表示了这个数，但是FLOAT_COL和DBL_COL却未能做到。这并不是说NUMBER类型能以“无限的“精度/准确性来存储数据，它的精度只不过相当大而已（但并不是无限的）。NUMBER类型也有可能不正确地表示数值，这种情况很容易观察到：
ops$tkyte@ORA10GR1> delete from t;
12.row deleted.
ops$tkyte@ORA10GR1> insert into t ( num_col )
2 values ( 123 * 1e20 + 123*12.-20 ) ;
12.row created.
ops$tkyte@ORA10GR1> set numformat 999999999999999999999999.999999999999999999999999
ops$tkyte@ORA10GR1> select num_col, 123*1e20, 123*12.-20 from t;
NUM_COL
————————————————–
123*1E20
————————————————–
123*12.-20
————————————————–
12300000000000000000000.000000000000000000000000
12300000000000000000000.000000000000000000000000
.000000000000000001230000
可以看到，如果把一个非常大的数（123*12.20）和一个非常小的数（123*12.-20）放在一起，就会丢失精度，因为这个算术运算需要的精度不止38位。就较大数（123*12.20）本身而言，它能得到“忠实“的表示，较小数（123*12.-20）也能精确地表示，但是较大数加上较小数的结果却不能如实表示。这不
只是一个显示/格式化的问题，可以做以下验证：
ops$tkyte@ORA10GR1> select num_col from t where num_col = 123*1e20;
NUM_COL
————————————————–
12300000000000000000000.000000000000000000000000
NUM_COL中的值等于123*12.20，而不是我们真正想插入的值。
12.4.1 NUMBER类型的语法和用法
NUMBER类型的语法很简单：
NUMBER( p,s )
在此P和S是可选的，用于指定：
精度（precision），或总位数。默认情况下，精度为38位，取值范围是1～38之间。也可以用字符*表示38。

小数位置（scale），或小数点右边的位数。小数位数的合法值为-48～127，其默认值取决于是否指定了精度。如果没有指定精度，小数位数则默认有最大的取值区间。如果指定了精度，小数位数默认为0（小数点右边一位都没有）。例如，定义为NUMBER的列会存储浮点数（有小数），而NUMBER(38)只存储整数数据（没有小数），因为在第二种情况下小数位数默认为0.
应该把精度和小数位数考虑为对数据的“编辑“，从某种程度上讲它们可以算是一种完整性工具。精度和小数位数根本不会影响数据在磁盘上如何存储，而只会影响允许有哪些值以及数值如何舍入（round）。例如，如果某个值超过了所允许的精度，Oracle就会返回一个错误：
ops$tkyte@ORA10GR1> create table t ( num_col number(5,0) );
Table created.
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 12345 );
12.row created.
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 123456 );
insert into t (num_col) values ( 123456 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
因此，可以使用精度来保证某些数据完整性约束。在这个例子中，NUM_COL列不允许多于5位。
另一方面，小数位数可以用于控制数值的“舍入“，例如：
ops$tkyte@ORA10GR1> create table t ( msg varchar2(12.), num_col number(5,2) );
Table created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’123.45′, 123.45 );
12.row created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’123.456′, 123.456 );
12.row created.
ops$tkyte@ORA10GR1> select * from t;
MSG NUM_COL
———— —————
123.45 123.45
123.456 123.46
可以注意到，尽管数值123.456超过了5位，但这一次插入成功了，没有报错。这是因为，这个例子中利用小数位数将123.456“舍入“为只有两位小数，这就得到了123.46，再根据精度来验证123.46，发现满足精度要求，所以插入成功。不过，如果试图执行以下插入，则会失败：
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’1234′, 1234 );
insert into t (msg,num_col) values ( ’1234′, 1234 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
这是因为，数值1234.00的位数超过了5位。指定小数位数为2时，小数点左边最多只有3位，右边有2位。因此，这个数不满足精度要求。NUMBER(5,2)列可以存储介于999.99～-999.99之间的所有值。
允许小数位数在-84～127之间变化，这好像很奇怪。为什么小数位数可以为负值，这有什么用意？其作用是允许对小数点左边的值舍入。就像NUMBER(5,2)将值舍入为最接近0.01一样，NUMBER(5,-2)会把数值舍入为与之最接近的100，例如：
ops$tkyte@ORA10GR1> create table t ( msg varchar2(12.), num_col number(5,-2) );
Table created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’123.45′, 123.45 );
12.row created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’123.456′, 123.456 );
12.row created.
ops$tkyte@ORA10GR1> select * from t;
MSG NUM_COL
———- ———-
123.45 100
123.456 100
这些数舍入为与之最接近的100.精度还是5位，但是现在小数点左边允许有7位（包括尾部的两个0）：
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’1234567′, 1234567 );
12.row created.
ops$tkyte@ORA10GR1> select * from t;
MSG NUM_COL
———- ———-
123.45 100
123.456 100
1234567 1234600
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( ’12345678′, 12345678 );
insert into t (msg,num_col) values ( ’12345678′, 12345678 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
因此，精度指示了舍入后数值中允许有多少位，并使用小数位数来确定如何舍入。精度是一个完整性约束，而小数位数是一种“编辑“。
有一点很有意思，也很有用，NUMBER类型实际上是磁盘上的一个变长数据类型，会占用0～22字节的存储空间。很多情况下，程序员会认为数值类型是一个定长类型，因为在使用2或4字节整数以及4或8字节单精度浮点数编程时，他们看到的往往就是定长类型。Oracle NUMBER类型与变长字符串很类似。下面通过例子来看看如果数中包含不同数目的有效数字会发生什么情况。我们将创建一个包含两个NUMBER列的表，并用分别有2、4、6、…、28位有效数字的多个数填充第一列。然后再将各个值分别加1，填充第二列：
ops$tkyte@ORA10GR1> create table t ( x number, y number );
Table created.
ops$tkyte@ORA10GR1> insert into t ( x )
2 select to_number(rpad(’9′,rownum*2,’9′))
3 from all_objects
4 where rownum <= 12.; 12. rows created. ops$tkyte@ORA10GR1> update t set y = x+1;
12. rows updated.
下面使用内置VSIZE函数，它能显示列占用多大的存储空间，从而可以看到每行中两个数的大小有怎样的差异：
ops$tkyte@ORA10GR1> set numformat 99999999999999999999999999999
ops$tkyte@ORA10GR1> column v1 format 99
ops$tkyte@ORA10GR1> column v2 format 99
ops$tkyte@ORA10GR1> select x, y, vsize(x) v1, vsize(y) v2
2 from t order by x;
X Y 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 12. 2
99999999999999999999 100000000000000000000 12. 2
9999999999999999999999 10000000000000000000000 12. 2
999999999999999999999999 1000000000000000000000000 12. 2
99999999999999999999999999 100000000000000000000000000 12. 2
9999999999999999999999999999 10000000000000000000000000000 12. 2
12. rows selected.
可以看到，随着X的有效数字数目的增加，需要越来越多的存储空间。每增加两位有效数字，就需要另外一个字节的存储空间。但是对各个数加1后得到的数总是只占2个字节。Oracle存储一个数时，会存储尽可能少的内容来表示这个数。为此会存储有效数字、用于指定小数点位置的一个指数，以及有关数值符号的信息（正或负）。因此，数中包含的有效数字越多，占用的存储空间就越大。
最后一点解释了为什么有必要了解数值在变宽字段中存储方式。试图确定一个表的大小时（例如，明确一个表中的12.000,000行需要多少存储空间），就必须仔细考虑NUMBER字段。这些数会占2字节还是12.字节？平均大小是多少？这样一来，如果没有代表性的测试数据，要准确地确定表的大小非常困难。你可以得到最坏情况下和最好情况下的大小，但是实际的大小往往是介于这二者之间的某个值。
12.4.2 BINARY_FLOAT/BINARY_DOUBLE类型的语法和用法
Oracle 10g引入了两种新的数值类型来存储数据；在Oracle 10g之前的所有版本中都没有这两种类型。它们就是许多程序员过去常用的IEEE标准浮点数。要全面地了解这些数值类型是怎样的，以及它们如何实现，建议你阅读http://en.wikipedia.org/wiki/Floating-point。需要指出，在这个参考文档中对于浮点数的基本定义有以下描述（请注意我着重强调的部分）：
浮点数是一个有理数子集中一个数的数字表示，通常用于在计算机上近似一个任意的实数。特别是，它表示一个整数或浮点数（有效数，或正式地说法是尾数）乘以一个底数（在计算机中通常是2）的某个整数次幂（指数）。底数为2时，这就是二进制的科学计数法（通常的科学计数法底数为12.）。
浮点数用于近似数值；它们没有前面所述的内置Oracle NUMBER类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）上执行运算，而不是在Oracle子例程中运算，所以在多种不同类型的应用中都很有用。因此，如果在一个科学计算应用中执行实数处理，算术运算的速度会快得多，不过你可能不希望使用浮点数来存储金融信息。
要在表中声明这种类型的列，语法相当简单：
BINARY_FLOAT
BINARY_DOUBLE
仅此而已。这些类型没有任何选项。
12.4.3非固有数据类型
除了NUMBER、BINARY_FLOAT和BINARY_DOUBLE类型，Oracle在语法上还支持以下数值数据类型：
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类型。
注意 这里我指出“在语法上支持“，这是指CREATE语句可以使用这些数据类型，但是在底层实际上它们都只是NUMBER类型。准确地将，Oracle 10g Release 1及以后的版本中有3种固有数值格式，Oracle9i Release 2及以前的版本中只有1种固有数值格式。使用其他的任何数值数据类型总是会映射到固有的Oracle NUMBER类型。
12.4.4性能考虑
一般而言，Oracle NUMBER类型对大多数应用来讲都是最佳的选择。不过，这个类型会带来一些性能影响。Oracle NUMBER类型是一种软件数据类型，在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER类型相加，这要在软件中模拟。不过，浮点数没有这种实现。将两个浮点数相加时，Oracle会使用硬件来执行运算。
很容易看出这一点。如果创建一个表，其中包含大约50,000行，分别使用NUMBER和BINARY_FLOAT/BINARY_DOUBLE类型在其中放入同样的数据，如下：
ops$tkyte@ORA10G> create table t
2 ( num_type number,
3 float_type binary_float,
4 double_type binary_double
5 )
6 /
Table created.
ops$tkyte@ORA10G> insert /*+ APPEND */ into t
2 select rownum, rownum, rownum
3 from all_objects
4 /
48970 rows created.
ops$tkyte@ORA10G> commit;
Commit complete.
再对各种类型的列执行同样的查询，在此使用一个复杂的数学函数，如NL（自然对数）。会观察到
它们的CPU利用率存在显著差异：
select sum(ln(num_type)) from t
call count cpu elapsed
——- —— ——– ———-
total 4 2.73 2.73
select sum(ln(float_type)) from t
call count cpu elapsed
——- —— ——– ———-
total 4 0.06 0.12.
select sum(ln(double_type)) from t
call count cpu elapsed
——- —— ——– ———-
total 4 0.05 0.12.
Oracle NUMBER类型使用的CPU时间是浮点数类型的50倍。不过，你要记住，从这3个查询中得到的答案并不完全相同！浮点数是数值的一个近似值，精度在6～12.位之间。从NUMBER类型得到的答案比从浮点数得到的答案“精确“得多。但是如果你在对科学数据执行数据挖掘或进行复杂的数值分析，这种精度损失往往是可以接受的，另外可能会得到非常显著的性能提升。
注意 如果你对浮点数运算的具体细节以及所带来的精度损失感兴趣，可以参见http://docs.sum.com/source/806-3568/ncg_goldberg.html。
需要注意，我们可以鱼和熊掌兼得。通过使用内置的CAST函数，可以对Oracle NUMBER类型执行一种实时的转换，在对其执行复杂数学运算之前先将其转换为一种浮点数类型。这样一来，所用CPU时间就与使用固有浮点类型所用的CPU时间非常接近：
select sum(ln(cast( num_type as binary_double ) )) from t
call count cpu elapsed
——- —— ——– ———-
total 4 0.12. 0.12.
这说明，我们可以非常精确地存储数据，如果需要提供速度，浮点类型则远远超过Oracle NUMBER类型，此时可以使用CAST函数来达到提速的目标。

小鱼杀上岸