oracle数据类型与存储结构

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的后面。
注：COL#列表示数据表定义时候的顺序，而SEGCOL#表示的是数据库实际存储的顺序。
举例2：

SQL> alter table test_long_1
  2  add a3 number;
Table altered
SQL> select name,col#,segcol# from sys.col$ where obj# = '47383';
NAME                           COL# SEGCOL#
------------------------------ ---- -------
A1                                1       2
A2                                2       1
A3                                3       3

如果表中已经存在了LONG类型的字段，那么在新加入其他类型字段时候就不会发生任何转化，此时LONG类型的字段在中间，这对性能会有很大的影响，这就需要DBA来手工的进行调整了。

5,DATE
DATE类型提供关于世纪，年，月，日，小时，分和秒的信息，不包含有小数的秒和时区信息。DATE的内部编码为12。由固定的7个字节组成：
前两位表示世纪和年，基数为100
下两位表示月和日，基数为0
最后的三位是小时（24小时制），分和秒，基数为1

SQL> create table test_date
  2  (a1 date);
Table created
SQL> insert into test_date values(sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_date;
A1          DUMP(A1)
-------------------------------------------------------------------------------
2006-5-29 2 Typ=12 Len=7: 120,106,5,29,22,33,41

世纪：120 – 100 = 20
年  ：106 – 100 = 06
月  ：5
日  ：29
小时：22 – 1 = 21
分钟：33  - 1 = 32
秒  ：41  - 1 = 40

5，    TIMESTAMP
TIMESTAMP类型提供关于年，月，日，小时，分，带有小数的秒的信息。TIMESTAMP的内部编码为180。
TIMESTAMP由固定的11个字节组成：
前七个字节与DATE类型的一样
后四个字节表示秒的小数部分

SQL> create table test_stamp
  2  (a1 timestamp);
Table created
SQL> insert into test_stamp values(systimestamp);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_stamp;
A1                                          DUMP(A1)
-------------------------------------------------------------------------------                                 
29-5月 -06 09.56.04.375000 

世纪：120 – 100 = 20
年  ：106 – 100 = 06
月  ：5
日  ：29
小时：22 – 1 = 21
分钟：57  - 1 = 56
秒（整数）：5  - 1 = 4
秒（小数）：0.23440320

SQL> select to_char('22','xx') from dual;
TO_CHAR('22','XX')
------------------
 16
SQL> select to_char('90','xx') from dual;
TO_CHAR('90','XX')
------------------
 5a
SQL> select to_char('11','xx') from dual;
TO_CHAR('11','XX')
------------------
  b
SQL> select to_char('192','xx') from dual;
TO_CHAR('192','XX')
-------------------
 c0
SQL> select to_number('165abc0','xxxxxxxxxxx') from dual;
TO_NUMBER('165ABC0','XXXXXXXXX
------------------------------
23440320