oracle数据类型--数值类型
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 /
decimal是整数数据类型表已创建。
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 ( 12
3 * 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 性能考虑
发表评论