oracle数据类型--数值类型

最新推荐文章于 2024-05-16 19:07:13 发布

weixin_34410662

最新推荐文章于 2024-05-16 19:07:13 发布

阅读量3.2k

点赞数

文章标签：数据库数据结构与算法 python

原文链接：https://my.oschina.net/u/1862478/blog/1932190

版权

2019独角兽企业重金招聘Python工程师标准>>>

NUMBE：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类型可以存储更小或更大的数值。下面举一个简单的例子，我们将用不同的数据类型来创建一个表，查看给定相同的输入时，各个列中会存储什么内容：

scott@ORCL>create table t
  2  ( num_col number,
  3     float_col binary_float,
  4     dbl_col binary_double
  5  )
  6  /

表已创建。

scott@ORCL>insert into t ( num_col, float_col, dbl_col )
  2  values ( 1234567890.0987654321,
  3     1234567890.0987654321,
  4     1234567890.0987654321 );

已创建 1 行。

scott@ORCL>set numformat 99999999999.99999999999
scott@ORCL>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则要好多了，它正确地表示了这个数中的17位。由此可以很好地说明BINARY_FLOAT和BINARY_DOUBLE类型在金融应用中不适用！如果尝试不同的值，可能会看到不同的结果：

scott@ORCL>delete from t;

已删除 1 行。

scott@ORCL>insert into t ( num_col, float_col, dbl_col )
  2  values ( 9999999999.9999999999,
  3     9999999999.9999999999,
  4     9999999999.9999999999 );

已创建 1 行。

scott@ORCL>select * from t;

                 NUM_COL                FLOAT_COL                  DBL_COL
------------------------ ------------------------ ------------------------
  9999999999.99999999990  10000000000.00000000000  10000000000.00000000000

NUM_COL又一次正确地表示了这个数，但是FLOAT_COL和DBL_COL却未能做到。这并不是说NUMBER类型能以“无限的“精度/准确性来存储数据，它的精度只不过相当大而已（但并不是无限的）。NUMBER类型也有可能不正确地表示数值，这种情况很容易观察到：

scott@ORCL>delete from t;

已删除 1 行。

scott@ORCL>insert into t ( num_col )
  2  values ( 123 * 1e20 +  123*1e-20 ) ;

已创建 1 行。

scott@ORCL>set numformat 999999999999999999999999.999999999999999999999999
scott@ORCL>select num_col, 123*1e20, 123*1e-20 from t;

                                           NUM_COL
             123*1E20                                          123*1E-20
-------------------------------------------------- -----------------------------
--------------------- --------------------------------------------------
  12300000000000000000000.000000000000000000000000   12300000000000000000000.000
000000000000000000000                          .000000000000000001230000

如果把一个非常大的数（123 * 1e20）和一个非常小的数（123*1e-20）放在一起，就会丢失精度，因为这个算术运算需要的精度不止38位。就较大数（123 * 1e20）本身而言，它能得到“忠实“的表示，较小数（123*1e-20）也能精确地表示，但是较大数加上较小数的结果却不能如实表示。这不只是一个显示/格式化的问题，可以做以下验证：

scott@ORCL>select num_col from t where num_col = 123*1e20;

                                           NUM_COL
--------------------------------------------------
  12300000000000000000000.000000000000000000000000

NUM_COL中的值等于 123 * 1e20，而不是我们真正想插入的值。

1 NUMBER类型的语法和用法

NUMBER( p,s )

在此P和S是可选的，用于指定：
精度（precision），或总位数。默认情况下，精度为38位，取值范围是1～38之间。也可以用字符*表示38。
小数位置（scale），或小数点右边的位数。小数位数的合法值为-48～127，其默认值取决于是否指定了精度。如果没有指定精度，小数位数则默认有最大的取值区间。如果指定了精度，小数位数默认为0.

应该把精度和小数位数考虑为对数据的“编辑“，从某种程度上讲它们可以算是一种完整性工具。精度和小数位数根本不会影响数据在磁盘上如何存储，而只会影响允许有哪些值以及数值如何舍入（round）。例如，如果某个值超过了所允许的精度，Oracle就会返回一个错误：

scott@ORCL>create table t ( num_col number(5,0) );

表已创建。

scott@ORCL>insert into t (num_col) values ( 12345 );

已创建 1 行。

scott@ORCL>insert into t (num_col) values ( 123456 );
insert into t (num_col) values ( 123456 )
                                 *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

因此，可以使用精度来保证某些数据完整性约束。在这个例子中，NUM_COL列不允许多于5位。

另一方面，小数位数可以用于控制数值的“舍入“，例如：

scott@ORCL>create table t ( msg varchar2(12), num_col number(5,2) );

表已创建。

scott@ORCL>insert into t (msg,num_col) values ( '123.45', 123.45 );

已创建 1 行。

scott@ORCL>insert into t (msg,num_col) values ( '123.456', 123.456 );

已创建 1 行。

scott@ORCL>select * from t;

MSG                         NUM_COL
------------------------ ----------
123.45                       123.45
123.456                      123.46

尽管数值123.456超过了5位，但这一次插入成功了。这是因为，这个例子中利用小数位数将123.456“舍入“为只有两位小数，这就得到了123.46，再根据精度来验证123.46，发现满足精度要求，所以插入成功。不过，如果试图执行以下插入，则会失败：

scott@ORCL>insert into t (msg,num_col) values ( '1234', 1234 );
insert into t (msg,num_col) values ( '1234', 1234 )
                                             *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

数值1234.00的位数超过了5位。指定小数位数为2时，小数点左边最多只有3位，右边有2位。因此，这个数不满足精度要求。NUMBER(5,2)列可以存储介于999.99～-999.99之间的所有值。

允许小数位数在-84～127之间变化，小数位数可以为负值其作用是允许对小数点左边的值舍入。就像NUMBER(5,2)将值舍入为最接近0.01一样，NUMBER(5,-2)会把数值舍入为与之最接近的100，例如：

scott@ORCL>create table t ( msg varchar2(12), num_col number(5,-2) );

表已创建。

scott@ORCL>insert into t (msg,num_col) values ( '123.45', 123.45 );

已创建 1 行。

scott@ORCL>insert into t (msg,num_col) values ( '123.456', 123.456 );

已创建 1 行。

scott@ORCL>select * from t;

MSG                         NUM_COL
------------------------ ----------
123.45                          100
123.456                         100

这些数舍入为与之最接近的100,精度还是5位，但是现在小数点左边允许有7位（包括尾部的两个0）：

scott@ORCL>insert into t (msg,num_col) values ( '1234567', 1234567 );

已创建 1 行。

scott@ORCL>select * from t;

MSG                         NUM_COL
------------------------ ----------
123.45                          100
123.456                         100
1234567                     1234600

scott@ORCL>insert into t (msg,num_col) values ( '12345678', 12345678 );
insert into t (msg,num_col) values ( '12345678', 12345678 )
                                                 *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

精度指示了舍入后数值中允许有多少位，并使用小数位数来确定如何舍入。精度是一个完整性约束，而小数位数是一种“编辑“。

NUMBER类型实际上是磁盘上的一个变长数据类型，会占用0～22字节的存储空间。Oracle NUMBER类型与变长字符串很类似。下面通过例子来看看如果数中包含不同数目的有效数字会发生什么情况。我们将创建一个包含两个NUMBER列的表，并用分别有2、4、6、…、28位有效数字的多个数填充第一列。然后再将各个值分别加1，填充第二列：

scott@ORCL>create table t ( x number, y number );

表已创建。

scott@ORCL>insert into t ( x )
  2  select to_number(rpad('9',rownum*2,'9'))
  3  from all_objects
  4  where rownum <= 12;

已创建12行。

scott@ORCL>update t set y = x+1;

已更新12行。

下面使用内置VSIZE函数，它能显示列占用多大的存储空间，从而可以看到每行中两个数的大小有怎样的差异：

scott@ORCL>set numformat 99999999999999999999999999999
scott@ORCL>column v1 format 99
scott@ORCL>column v2 format 99
scott@ORCL>select x, y, vsize(x) v1, vsize(y) v2 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  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2

已选择12行。

可以看到，随着X的有效数字数目的增加，需要越来越多的存储空间。每增加两位有效数字，就需要另外一个字节的存储空间。但是对各个数加1后得到的数总是只占2个字节。Oracle存储一个数时，会存储尽可能少的内容来表示这个数。为此会存储有效数字、用于指定小数点位置的一个指数，以及有关数值符号的信息（正或负）。因此，数中包含的有效数字越多，占用的存储空间就越大。

2 BINARY_FLOAT/BINARY_DOUBLE类型的语法和用法

浮点数是一个有理数子集中一个数的数字表示，通常用于在计算机上近似一个任意的实数。特别是，它表示一个整数或浮点数（有效数，或正式地说法是尾数）乘以一个底数（在计算机中通常是2）的某个整数次幂（指数）。底数为2时，这就是二进制的科学计数法（通常的科学计数法底数为12）。

浮点数用于近似数值；它们没有前面所述的内置Oracle NUMBER类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）上执行运算，而不是在Oracle子例程中运算，所以在多种不同类型的应用中都很有用。因此，如果在一个科学计算应用中执行实数处理，算术运算的速度会快得多，不过你可能不希望使用浮点数来存储金融信息。

要在表中声明这种类型的列，语法相当简单：

BINARY_FLOAT
BINARY_DOUBLE

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类型。

4 性能考虑

一般而言，Oracle NUMBER类型对大多数应用来讲都是最佳的选择。不过，这个类型会带来一些性能影响。Oracle NUMBER类型是一种软件数据类型，在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER类型相加，这要在软件中模拟。不过，浮点数没有这种实现。将两个浮点数相加时，Oracle会使用硬件来执行运算。

scott@ORCL>create table t
  2  ( num_type number,
  3     float_type binary_float,
  4     double_type binary_double
  5  )
  6  /

表已创建。

scott@ORCL>insert /*+ APPEND */ into t
  2  select rownum, rownum, rownum
  3  from all_objects
  4  /

已创建72114行。

scott@ORCL>commit;

提交完成。

再对各种类型的列执行同样的查询，在此使用一个复杂的数学函数，如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类型得到的答案比从浮点数得到的答案“精确“得多。但是如果你在对科学数据执行数据挖掘或进行复杂的数值分析，这种精度损失往往是可以接受的，另外可能会得到非常显著的性能提升。

需要注意，通过使用内置的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函数来达到提速的目标。

转载于:https://my.oschina.net/u/1862478/blog/1932190