程序博客网 > 监控计算机的软件

Oracle数据类型之number

来源：互联网 发布：监控计算机的软件 编辑：程序博客网 时间：2024/05/02 02:49

转自：http://www.blogjava.net/caojianhua/archive/2011/01/24/343461.html

oracle的number类型是oracle的内置类型之一，是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型，在10g以后，才出现了两种新的数值类型，即推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位). 这些新数据类型都是基于IEEE二进制浮点运算标准,ANSI/IEEE Std 754-1985 [IEEE 754]，使用这些类型时要加上文字f(BINARY_FLOAT)或者d(BINARY_DOUBLE)，比如2.07f、3.000094d。 


number数据类型

number类型的语法很简单：number(p,s)：
p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符*表示38。
s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。
p：is the precision,or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
s：is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127. 

number类型的p和s，与其底层存储完全没有关系，根本不会影响数据在磁盘上如何存储，它只会影响允许哪些值以及数值如何舍入，你可以认为其是对数据的“编辑”。简单的说，精度位p表示数值最多能有多少个有效数字，而小数位s表示最多能有多少位小数。换句话说，p表示一共有多少位有效数字（即小数点左边最多有p-s位有效数字），s表示小数点右边有s位有效数字。如number(5,2)类型的数据，就表示小数点左边最多有3位有效数字，右边最多有2位有效数字，加起来就是最多有5位有效数字，超过这个范围的数字就不能正确的存储下来，注意这里说的是不能正确存储，但并不是不能存储。

最高整数位数＝p-s 
s正数，小数点右边指定位置开始四舍五入 
s负数，小数点左边指定位置开始四舍五入 
s是0或者未指定，四舍五入到最近整数 
当p小于s时候，表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数。 

p>0，对s分2种情况： 
1. s>0 
精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。 
2. s<0 
精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s| 

具体数据可参考下表

Value

Datatype

Stored Value

123.2564

NUMBER

123.2564

1234.9876

NUMBER(6,2)

1234.99

12345.12345

NUMBER(6,2)

Error

1234.9876

NUMBER(6)

1235

12345.345

NUMBER(5,-2)

12300

1234567

NUMBER(5,-2)

1234600

12345678

NUMBER(5,-2)

Error

123456789

NUMBER(5,-4)

123460000

1234567890

NUMBER(5,-4)

Error

12345.58

NUMBER(*, 1)

12345.6

0.1

NUMBER(4,5)

Error

0.01234567

NUMBER(4,5)

0.01235

0.09999

NUMBER(4,5)

0.09999

0.099996

NUMBER(4,5)

Error


里面发生错误的行有的是因为源数据超过了可以表示的范围，有的是因为进行小数四舍五入后超过了可以表示的范围。

以下是一些例子

1. s>0 

精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p； 

ZWF.YUDONG>create table t_n(id number(5,2)); 

Table created. 

ZWF.YUDONG>insert into t_n values(123.45); 

1 row created. 

ZWF.YUDONG>insert into t_n values(123.455); 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
    123.45 
    123.46 

2 rows selected. 

ZWF.YUDONG>insert into t_n values(1.234); 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
    123.45 
    123.46 
      1.23 

3 rows selected. 

ZWF.YUDONG>insert into t_n values(.001); 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
    123.45 
    123.46 
      1.23 
         0 

4 rows selected. 

ZWF.YUDONG>insert into t_n values(1234.56); 
insert into t_n values(1234.56) 
                       * 
ERROR at line 1: 
ORA-01438: value larger than specified precision allowed for this column 


如果s>p，小数点右边至少有s-p个0填充。 

ZWF.YUDONG>create table t_n(id number(4,5)); 

Table created. 

ZWF.YUDONG>insert into t_n values(1); 
insert into t_n values(1) 
                       * 
ERROR at line 1: 
ORA-01438: value larger than specified precision allowed for this column 


ZWF.YUDONG>insert into t_n values(.1); 
insert into t_n values(.1) 
                       * 
ERROR at line 1: 
ORA-01438: value larger than specified precision allowed for this column 


ZWF.YUDONG>insert into t_n values(.01); 

1 row created. 

ZWF.YUDONG>commit; 

Commit complete. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
       .01 

1 row selected. 

ZWF.YUDONG>insert into t_n values(.001); 

1 row created. 

ZWF.YUDONG>insert into t_n values(.0001); 

1 row created. 

ZWF.YUDONG>insert into t_n values(.00001); 

1 row created. 

ZWF.YUDONG>insert into t_n values(.000001);   --超过刻度存储0 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
       .01 
      .001 
     .0001 
    .00001 
         0 


10 rows selected. 

ZWF.YUDONG>col dp for a50 
ZWF.YUDONG>select id,dump(id) dp,length(id),vsize(id) from t_n;  --vsize和dump的是字节数，length是数值实际位数（含小数点） 

        ID DP                                                 LENGTH(ID)  VSIZE(ID) 
---------- -------------------------------------------------- ---------- ---------- 
       .01 Typ=2 Len=2: 192,2                                          3          2 
      .001 Typ=2 Len=2: 191,11                                         4          2 
     .0001 Typ=2 Len=2: 191,2                                          5          2 
    .00001 Typ=2 Len=2: 190,11                                         6          2 
         0 Typ=2 Len=1: 128                                            1          1 

5 rows selected. 


2. s<0 

精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s| 

ZWF.YUDONG>create table t_n(id number(5,-2)); 

Table created. 

ZWF.YUDONG>insert into t_n values(12345); 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
     12300 

1 row selected. 

ZWF.YUDONG>insert into t_n values(123456); 

1 row created. 

ZWF.YUDONG>insert into t_n values(1234567); 

1 row created. 

ZWF.YUDONG>select * from t_n; 

        ID 
---------- 
     12300 
    123500 
   1234600 

3 rows selected. 

ZWF.YUDONG>insert into t_n values(12345678); 
insert into t_n values(12345678) 
                       * 
ERROR at line 1: 
ORA-01438: value larger than specified precision allowed for this column 

oracle的number类型存储结构

oracle采用变长存储number数据类型（按一定规则进行转换成2进制编码格式存储）。 

oracle数据库中存储的number类型包含3个部分: HEAD部分, DATA部分, 符号位。 

对正数来说, 符号位省略, 对0来说, oracle存储的是X80（128）。 

ZWF.YUDONG>select dump(0) from dual; 

DUMP(0) 
---------------- 
Typ=2 Len=1: 128 

1 row selected. 

ZWF.YUDONG>select dump(1) from dual; 

DUMP(1) 
------------------ 
Typ=2 Len=2: 193,2 

1 row selected. 

ZWF.YUDONG>select dump(-1) from dual; 

DUMP(-1) 
----------------------- 
Typ=2 Len=3: 62,100,102   

1 row selected. 

HEAD部分为一个字节8位, 就是前面看到的128, 193，62。由该部分我们可以看出number类型的基本信息，因为设计这种存储格式的时候, oracle希望以十六进制00-FF来表示所有 
的number, 所以为了编码的对称, 首先将number分为正负, 所以以00-FF的中间位置80, 也就是十进制的128来表示0, HEAD部分小于80,即为负数,大于80即为正数。ORACLE再次对 
00-80, 80-FF进行对分： 

00-3E 表示: number <= -1 
3F-7F 表示: -1 < number < 0 
81-C0 表示: 0 < number < 1 
C1-FF 表示：number >= 1 

从HEAD部分我们可以也看出数据的位数信息，是否含有小数，可以根据HEAD的信息判断小数点的位置。由于数据部分低位2的n次方位个0是不被存储的，数据展现的时候oracle 
根据HEAD的信息给补充末位的0。 

ZWF.YUDONG>select dump(123456789) from dual; 

DUMP(123456789) 
------------------------------ 
Typ=2 Len=6: 197,2,24,46,68,90 --197（C5）的含义：表示数字123456789大于1，197-193(数字1占用2个字节该值为193) = 4 ，所以该数字占用6（2+4）个字节。 

1 row selected. 


然后,我们再来看数据部分, ORACLE对十进制的数字(整数部分,小数部分正好相反)是两位两位进行存储的(从右往左的顺序), 例如对1234, ORACLE会分别对12, 34进行存储. 
所以只需要对(+-)1-99进行编码 

1 --- 99 分别用十六进制2-64表示，就是2-100, 

-1--- -99 用十六进制64-2表示，就是100-2 

ZWF.YUDONG>select dump(12345) from dual; 

DUMP(12345) 
------------------------ 
Typ=2 Len=4: 195,2,24,46  --数据部分2,24,46 表示 （2-1=1，24-1=23，46-1=45）；HEAD部分表示12345 >= 1,占用195-193+2=4字节。 

1 row selected. 


SYS.YUDONG>select dump(1100) from dual;  

DUMP(1100) 
------------------- 
Typ=2 Len=2: 194,12       --如果从右边起，连续2的n次方位为0，oracle一次排触（不存储）只是位数加1。可以对比dump(11)的情况看看。 

1 row selected. 

SYS.YUDONG>select dump(11) from dual; 

DUMP(11) 
------------------- 
Typ=2 Len=2: 193,12        --这里数据部分和1100是一样的，末位的2个0没有实际存储，长度193比194小1。 

1 row selected. 

--对于含小数(负数、整数2种情况)的情况： 

1、负数 

SYS.YUDONG>select dump(-1.2) from dual; 

DUMP(-1.2) 
-------------------------- 
Typ=2 Len=4: 62,100,81,102    --HEAD=62（3E）表示该数值小于等于-1;数据部分：整数部分的-1存储为100，小数部分从左往右2位一结合，不足2位后边补一个1。 
                              对应关系变为9，8...1表示1，2...9，看下面几个例子，如果足2位，还是按照上边说的规律（-1--- -99 用十六进制64-2表示，就是100-2）。 

1 row selected. 


ZWF.YUDONG>select dump(-2.1) from dual; 

DUMP(-2.1) 
------------------------- 
Typ=2 Len=4: 62,99,91,102 

1 row selected. 

ZWF.YUDONG>select dump(-2.2) from dual; 

DUMP(-2.2) 
------------------------- 
Typ=2 Len=4: 62,99,81,102 

1 row selected. 

ZWF.YUDONG>select dump(-2.9) from dual; 

DUMP(-2.9) 
------------------------- 
Typ=2 Len=4: 62,99,11,102 

1 row selected. 

ZWF.YUDONG>select dump(-2.12) from dual; 

DUMP(-2.12) 
------------------------- 
Typ=2 Len=4: 62,99,89,102 

1 row selected. 

ZWF.YUDONG>select dump(-2.13) from dual; 

DUMP(-2.13) 
------------------------- 
Typ=2 Len=4: 62,99,88,102 

1 row selected. 

ZWF.YUDONG>select dump(-2.123) from dual; 

DUMP(-2.123) 
---------------------------- 
Typ=2 Len=5: 62,99,89,71,102 

1 row selected. 


2、正数 

SYS.YUDONG>select dump(1.222) from dual; 

DUMP(1.222) 
------------------------ 
Typ=2 Len=4: 193,2,23,21      --HEAD=193(C1)表示该数字大于等于1;数据部分:整数部分存储2(2-1=1),小数部分从左往右2位一结合,23(23-1=22)表示22,后边还剩下一个2, 
                                不足2位的末尾补充一个1,也就是等于1.2220 

1 row selected. 

ZWF.YUDONG>select dump(1.2220) from dual; 

DUMP(1.2220) 
------------------------ 
Typ=2 Len=4: 193,2,23,21 

1 row selected. 


符号位： 用的是(+-)1-99都不可能用到的编码66(102)来表示，有资料说为了处理排序问题（未加考证）。根据HEAD部分可以做初步判断，根据我们说的HEAD部分的四个范围， 
如果2个数值不在一个范围，立即可以看出大小，如果在一个范围其实也可以根据其正负+绝对值来进行排序了，正数绝对值大的就大，负数则相反，为何还要用到这个符号位？

本地浮点类型

另外再说一下两个数值类型本地浮点数据类型（binary_float与binary_double）

本地浮点数据类型最大的特点就是比NUMBER类型效率更高
硬件运算/数学运算快 5– 10 倍
占用更少的内存/磁盘空间（5/9 字节与 1 – 22 字节）
BINARY_DOUBLE 值范围更大（e308 与 e125）
无需类型转换（使用与字节顺序无关的存储格式）

下面程序是使用欧拉级数计算圆周率∏:
∏ = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + ... ) )

方法一：使用NUMBER类型

create or replace procedure Euler_Pi_Number is

subtype My_Number is number;

zero constant My_Number := 0.0;
one constant My_Number := 1.0;
two constant My_Number := 2.0;
six constant My_Number := 6.0;
toler constant My_Number := 0.00000000001;
root_toler constant My_Number := toler/1000.0;

root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;

begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two; 
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Number;
/


方法二：使用BINARY_DOUBLE类型

create or replace procedure Euler_Pi_Binary is

subtype My_Number is binary_double;

zero constant My_Number := 0.0d;
one constant My_Number := 1.0d;
two constant My_Number := 2.0d;
six constant My_Number := 6.0d;
toler constant My_Number := 0.00000000001d;
root_toler constant My_Number := toler/1000.0d;

root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;

begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two; 
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Binary;
/


SQL> set timing on
SQL> exec Euler_Pi_Number;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 11.59
SQL> exec Euler_Pi_Binary;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 02.09


上面例子中近似300,000次迭代计算,NUMBER类型花费11.59秒,BINARY_DOUBLE类型花费约2.09秒,性能提高大约5.5倍.

结论：在版本10g之后写一些偏数字科学运行量巨大的存储过程、函数时,对于浮点数字类型要优先考虑使用本地浮点数据类型

相关链接
http://it.chinawin.net/database/article-4193.html
http://ilinux.javaeye.com/blog/289550
http://yaanzy.itpub.net/post/1263/200971
0 0
监控计算机的软件 监控计算机的软件
原创粉丝点击
热门IT博客
网络空间通常从 描绘网络空间通常从 描绘
小米平板1刷windows小米平板1刷windows
java get set方法解释
剑三初始捏脸数据
视频剪辑软件什么好
淘宝差评师怎么做
js中给文本框赋值
苹果mac使用
js实现div上移下移
纯种狗是退化知乎
数据库归档日志清理
调查问卷哪个最赚 知乎
java简明教程 pdf下载
windows driver kit 7
高级的数据分析算法
r软件下载
银行数据保密协议范本
安卓手机编程语言
南京大学软件学院地址
买家怎么进淘宝特卖
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 龙宽 疏楼龙宿 龙宿 高墙之内by龙宿一 中华龙将 龙将手机版 91玩龙将2 360龙将2 龙将手游 三国龙将传 龙将2 装备 龙将摸金符 龙将石头 龙将2幻浪 龙将2开服表 龙将斩千 龙将武将 紫霞龙将 龙将三国 龙将八爪吧 欢乐园龙将 龙将顾问 龙将太史慈 龙将打孔石 龙将2职业选择 龙将2媒体礼包 龙将v3多少钱 龙将2魔神吕布 龙将声望怎么获得 360龙将2开服表 星河龙将 狂潮大队长 龙将70金将对酒攻略 龙将武将大全 手游龙将礼包 三国争霸龙将 龙将起手技能 龙将橙色武魂 龙将怪物攻城 龙将职业选择 手机龙将开服表 龙将80级武将搭配

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里