Oracle数据库内建的数据类型主要有如下几种:
。CHAR,NCHAR,VARCHAR2,NVARCHAR2
。NUMBER
。RAW
。LONG,LONG RAW
。DATE
。TIMESTAMP
。INTERVAL
。CLOB,BLOB,NCLOB,BFILE
。ROWID,UROWID
每种不同的数据类型用来存储不同的数据,CHAR存储字符,NUMBER存储数值型。此外,每种数据类型在数据块中的存储结构也是不一样的,他们的存储规则存在很大的区别,下面详细介绍一下每种数据类型在数据库中的存储结构,以及转化的算法。
1, 准备知识
SQL> create table test
2 (id varchar2(10),
3 score number(5,2));
Table created
SQL> insert into test values('a',78);
1 row inserted
SQL> insert into test values('b',85);
1 row inserted
SQL> commit;
Commit complete
SQL> select id,dump(id) from test;
ID DUMP(ID)
---------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
b Typ=1 Len=1: 98
SQL> select score,dump(score) from test;
SCORE DUMP(SCORE)
------- --------------------------------------------------------------------------------
78.00 TYP=2Len=2: 193,79
85.00 TYP=2Len=2: 193,86
Oracle在数据块中并不是直接的不加修改的存储我们的数据,而是进行一系列的转化,以一种更高效的方式来存储。从上面的例子可以看出,字符“a”在数据块中用“97”来表示的,数值“78”是用“193,79”来存储的。
注:dump命令可以得到数据在数据库中的存储结构。Dump的语法为dump(数据,进制),默认是转化为10进制的。如果需要转化为16进制,则语法为dump(数据,16)。
2, CHAR,NCHAR,VARCHAR2,NVARCHAR2
CHAR:字符型,单字节,固定长度,最大为2000个字符,内部编码96
VARCHAR2:字符型,单字节,可变长度,最大为4000个字符,内部编码1
NCHAR:字符型,多字节,固定长度,最大为2000个字符,内部编码96
NARCHAR2:字符型,多字节,可变长度,最大为4000个字符,内部编码1
1) CHAR
SQL> create table test_char
2 (a1 char(1),
3 a2 char(5));
Table created
SQL> insert into test_char values('a','ab');
1 row inserted
SQL> commit;
Commit complete
SQL> insert into test_char values('b’,’abc’);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_char;
A1 DUMP(A1)-- --------------------------------------------------------------------------------
a Typ=96 Len=1: 97
b Typ=96 Len=1: 98
SQL> select a2,dump(a2) from test_char;
A2 DUMP(A2)
----- --------------------------------------------------------------------------------
ab Typ=96 Len=5: 97,98,32,32,32
abc Typ=96 Len=5: 97,98,99,32,32
从dump的结果可以看到:
。CHAR的类型编码Typ为96
。Len表示该数据在数据库中的存储长度,char(1)为1,而char(5)为5
。“a”在数据库的存储格式为“97”,“b”为“98”
。“ab”在数据库的存储格式为“97,98,32,32,32”
。“abc”在数据库的存储格式为“97,98,99,32,32”
结论:
1, CHAR的类型编码Typ为96
2, CHAR在数据库中按固定长度存储,不足的为用空格补齐(chr(32))
3, 转化规则
(1) 单字节字符:数据库中存储的为数据的ascii码(1个字节)
SQL> select ascii('a') from dual;
ASCII('A')
----------
97
SQL> select ascii('b') from dual;
ASCII('B')
----------
98
(2) 多字节字符:数据库中存储的为数据的ascii码(多个字节)
SQL> select dump('好') from dual;
DUMP('好')
---------------------
Typ=96 Len=2: 186,195
SQL> select ascii('好') from dual;
ASCII('好')
-----------
47811
SQL> select 186*256 + 195 from dual;
186*256+195
-----------
47811
其中186为高位,195为低位,根据公式ascii码 = 高位*power(2,8)+ 低位,所以'好'的ascii为47811。
2) VARCHAR2
SQL> create table test_varchar2
2 (a1 varchar2(1),
3 a2 varchar2(5));
Table created
SQL> insert into test_varchar2 values ('a','ab');
1 row inserted
SQL> insert into test_varchar2 values ('b','abc');
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_varchar2;
A1 DUMP(A1)
-- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
b Typ=1 Len=1: 98
SQL> select a2,dump(a2) from test_varchar2;
A2 DUMP(A2)
----- --------------------------------------------------------------------------------
ab Typ=1 Len=2: 97,98
abc Typ=1 Len=3: 97,98,99
从dump的结果可以看到:
。VARCHAR2的类型编码Typ为1
。VARCHAR2类型的数据在数据库的存储长度与实际插入的数据有关系,不需要补位
。“a”在数据库的存储格式为“97”,“b”为“98”
。“ab”在数据库的存储格式为“97,98,32,32,32”
。“abc”在数据库的存储格式为“97,98,99,32,32”
结论:
1, VARCHAR2的类型编码Typ为1
2, VARCHAR2在数据库存储就是数据的实际长度,不需要补位
3, 转化规则:与CHAR的转化规则完全一致
另:NCHAR,NVARCHAR2与CHAR,VARCHAR2类似,就不一一介绍了。
结论
1, CHAR与VARCHAR2的最大的区别就是一个2000字节,一个4000个字节,还有就是一个补足空位,一个不需要补空位
2, 一般说来数据库能用CHAR的地方都可以利用VARCHAR2来代替,这样可以节省数据库的空间。但是如果对性能有要求的系统来说,合理的使用CHAR会比使用VARCHAR2有更好的性能。
3, NUMBER
SQL> create table test_number
2 (a1 number(8,2));
Table created
SQL> insert into test_number values (0);
1 row inserted
SQL> insert into test_number values (1);
1 row inserted
SQL> insert into test_number values (-1);
1 row inserted
SQL> insert into test_number values (0.12);
1 row inserted
SQL> insert into test_number values (34.56);
1 row inserted
SQL> insert into test_number values (-34.56);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1 ,dump(a1) from test_number;
A1 DUMP(A1)
---------- --------------------------------------------------------------------------------
0.00 Typ=2 Len=1: 128
1.00 Typ=2 Len=2: 193,2
-1.00 Typ=2 Len=3: 62,100,102
0.12 Typ=2 Len=2: 192,13
34.56 Typ=2 Len=3: 193,35,57
-34.56 Typ=2 Len=4: 62,67,45,102
6 rows selected
从dump的结果可以看到:
。NUMBER的类型编码Typ为2
。如果数值为负数那么需要在最后面补一位102,正数和零则不需要
。Len为数值的实际值加上补位的长度
。数值0在数据库的存储格式为128
。数值1在数据库的存储格式为193,2
。数值-1在数据库的存储格式为62,100,102
。数值0.12在数据库存储格式为192,13
。数值34.56在数据库的存储格式为193,35,56
。数值-34.56在数据库的存储格式为62,67,45,102
结论:
1, NUMBER的类型编码Typ为2
2, 如果数值为负数那么需要在最后面补一位102,正数和零则不需要
之所以这样处理,是为了更加方便的进行两个数值的大小比较。Oracle在进行数据库中两个数值大小比较的时候,直接比较他们在数据库中的存储格式,并不是把他们转化成我们常用的数值形式后在比较。比如说:比较0和1这两个数,0在数据库中表示为128,1在数据库中表示为193,2,那么直接进行比较‘128’小于‘193’,所以0肯定比1要小。在比如说1和0.12这两个数比较,0.12在数据库中表示为192.13,因为‘193’大于‘192’,所以1肯定比0.12大。这个规则对于正数和0完全适用,但是对于不补位的负数就要出麻烦了。看下面的例子:
SQL> select dump(-0.12) from dual;
DUMP(-0.12)
----------------------
Typ=2 Len=3: 63,89,102
SQL> select dump(-0.125) from dual;
DUMP(-0.125)
-------------------------
Typ=2 Len=4: 63,89,51,102
如果不补位的话-0.12应该为63,89,而-0.125应该为63,89,51,那么按照前面的规则来说‘63’等于‘63’,‘89’等于‘89’,‘51’肯定要大于空,就可以得出错误的结论-0.12小于-0.125。而当在负数后面补上102后,情况就完全改变了,就可以很快的得到正确的大小关系。
3, 转化规则
(1)数值在数据库中存储结构的第一位叫做标志位,是整个数值表示中最关键的一位。
如果标志位大于128,那么该数值为正数
如果标志位等于128,那么该数值为0
如果标志位小于128,那么该数值为负数
例如:
数值0的标志位为128,所以为0
数值0.12的标志位为192,所以0.12为正数
数值-34.56的标志位为62,所以-34.56为负数
(2)数据库中存储结构的除了第一位其余的为数值位
如果该数值为正数,那么每位上的实际数值等于数据库中存储的数值减1
如果该数值为负数,那么每位上的实际数值等于101减去数据库中存储的数值,最后补位的102不用计算。
例如:
数值1的数字为2 – 1 = 1
数值-1的数字为 101 – 100 = 1
数值34.56的数字为35 – 1 = 34,57 – 1 = 56,即3456
数值-34.56的数字为101 – 67 = 34,101 – 45 = 56,即3456
(3)确定小数点位置
确定小数点的位置需要标志位和第一位共同来决定
如果该数值为正数,那么该数值的前几位应该为第一位 * power(100,(标志位 – 193))
如果该数值为负数,那么该数值的前几位应该为第一位 * power(100,(62–标志位))
例如:
34.56 Typ=2 Len=3: 193,35,57,该数值的前几位应该为34*power(100,0)=34,所以34.56的小数点应该在4的后面,那么就可以得到该数值为34.56
-34.56 Typ=2 Len=4: 62,67,45,102该数值的前几位应该为34*power(100,0)=34,所以34.56的小数点应该在4的后面,那么就可以得到该数值为-34.56
4, LONG
LONG类型可变长度的字符类型,最高可以达到2G个字符,可以在数据库和用户session的字符集之间自动的转化,内部编码为8,oracle建议使用LOB类型代替LONG。
由于dump不能对LONG型的字符进行处理,所以这里就不在举例子说明了。LONG类型的转化规则和其他字符型的类似。
下面着重说一下LONG类型在数据表中的存放位置问题。
由于LONG字符的特殊性,长度很大,所以一般当数据表中含有LONG类型的字段时,oracle都会尽可能的把它放到最后面来存储,一来可以节省空间,二来可以提高效率。这里面说的是尽可能,有的时候oracle无法做出相应的处理,LONG也可能位于中间位置,这时就需要DBA进行响应的调整了。
举例1:
SQL> create table test_long_1
2 (a1 long,
3 a2 varchar2(10));
Table created
SQL> select object_id from dba_objects where object_name = 'TEST_LONG_1';
OBJECT_ID
---------
47383
SQL> select name,col#,segcol# from sys.col$ where obj# = '47383';
NAME COL# SEGCOL#
------------------------------ ---- -------
A1 1 2
A2 2 1
由上面的结果可以看到虽然LONG类型的a1首先被定义,但是在数据库存储的时候却被放到了a2的后面。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13650998/viewspace-220333/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13650998/viewspace-220333/