利用dump函数理解oracle如何存储各种类型数据
1.dump函数介绍
1.1标准格式
DUMP(expr[,return_fmt[,start_position][,length]])
expr：需要dump的表达式
return_fmt：指返回参数的格式，有5种用法：
1）8：以8进制返回结果的值
2）10：以10进制返回结果的值（默认）
3）16：以16进制返回结果的值
4）17：以单字符的形式返回结果的值
5）1000：以上4种加上1000，表示在返回值中加上当前字符集
start_position：需要返回的起始字符位置
length：需要返回的字符长度
1.2 返回结果
返回结果包含三方面值，分别是TYPE、LEN、VALUE，例如
1 DUMP('ABC') 2 ---------------------- 3 Typ=96 Len=3: 97,98,99
其一，typ表示当前的expr值的类型，有如下类型
1 CODE TYP 2 ----- ------------------------------ 3 1 VARCHAR2 4 2 NUMBER 5 8 LONG 6 12 DATE 7 23 RAW 8 24 LONG RAW 9 69 ROWID 10 96 CHAR 11 112 CLOB 12 113 BLOB 13 114 BFILE 14 180 TIMESTAMP 15 181 TIMESTAMP WITH TIMEZONE 16 182 INTERVAL YEAR TO MONTH 17 183 INTERVAL DAY TO SECOND 18 208 UROWID 19 231 TIMESTAMP WITH LOCAL TIMEZONE
其二，len表示该值所占用的字节数
其三，value部分即为oracle在内部如何来存储这个expr值
2.几种常用数据类型的dump
2.1Date类型
1 SYS@crm AS SYSDBA>col DUMP_DATE format a50 2 SYS@crm AS SYSDBA>select dump(last_ddl_time) dump_date, to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') real_date from user_objects where rownum=1; 3 4 DUMP_DATE REAL_DATE 5 -------------------------------------------------- ------------------- 6 Typ=12 Len=7: 120,112,5,21,21,33,9 2012-05-21 20:32:08 7 8 Elapsed: 00:00:00.01
Oracle是按如下表格所示来存储date类型数据的，根据dump值做相应的逆行运算即可.
|
世纪 |
120 - 100 = 20 |
世纪和年份加100后存储 |
|
年份 |
112 - 100 = 12 |
|
|
月份 |
5 |
月份和日期按原值存储 |
|
日期 |
21 |
|
|
小时 |
21 - 1 = 20 |
时间均加1后存储 |
|
分钟 |
33 - 1 = 32 |
|
|
秒 |
9 - 1 = 8 |
留意了一下内存中date类型数据的表示，果然还是不一样。
1 SYS@crm AS SYSDBA>col dumpsysdate format a50 2 SYS@crm AS SYSDBA>select dump(sysdate) dumpsysdate, sysdate from dual; 3 4 DUMPSYSDATE SYSDATE 5 -------------------------------------------------- ------------------- 6 Typ=13 Len=8: 221,7,7,29,12,5,25,0 2013-07-29 12:05:25
这里有几个需要注意的：typ变成了13，这在上面列举的数据类型表里貌似没有。看的出来，除了世纪和年份，其它值和真实值都一模一样；那这里的2013是怎么运算得出221,7的呢？
因为这个机器是little-endian architecture的服务器，“221,7”应该解读为“7,221”，两个数字分别换算成十六进制后合起来就是“0x7DD”，即2013，这就对上了！
1 SYS@crm AS SYSDBA>select to_char(221,'xxx') from dual; 2 TO_C 3 ---- 4 dd 5 Elapsed: 00:00:00.00 6 7 SYS@crm AS SYSDBA>select to_number('7DD','XXX') FROM DUAL; 8 TO_NUMBER('7DD','XXX') 9 ---------------------- 10 2013
2.2 Number类型
Number类型dump出来有点特别，value方面里第一个数字（用value1来表示）代表符号位/指数，按以下方法来计算
正数：指数=value1 - 193 (value1换算成二进制后最高位为1则代表正数)
负数：指数=62 - value1（value1换算成二进制后最高位为0则代表负数）
从value2开始，正数加1存储；负数被101减后存储，并且如果总长度小于21个字节，为了排序的需要，最后加一个102。
所存储的数值计算方法为：
把下面计算的结果加起来：
每个数字乘以100^(指数-N) (N是有效位数的顺序位，第一个有效位的N=0)
例：
1 select dump(123456.789) from dual; 2 DUMP(123456.789) 3 ------------------------------- 4 Typ=2 Len=6: 195,13,35,57,79,91
|
Value1(指数) |
195 - 193 = 2 |
||
|
Value2 |
13 - 1 = 12 |
*1002-0 |
120000 |
|
Value3 |
35 - 1 = 34 |
*1002-1 |
3400 |
|
Value4 |
57 - 1 = 56 |
*1002-2 |
56 |
|
Value5 |
79 - 1 = 78 |
*1002-3 |
.78 |
|
Value6 |
91 - 1 = 90 |
*1002-4 |
.009 |
|
|
123456.789 |
||
1 select dump(-123456.789) from dual; 2 DUMP(-123456.789) 3 ---------------------------------- 4 Typ=2 Len=7: 60,89,67,45,23,11,102
|
Value1(指数) |
62 - 60 = 2 |
||
|
Value2 |
101 - 89 = 12 |
*1002-0 |
120000 |
|
Value3 |
101 - 67 = 34 |
*1002-1 |
3400 |
|
Value4 |
101 - 45 = 56 |
*1002-2 |
56 |
|
Value5 |
101 - 23 = 78 |
*1002-3 |
.78 |
|
Value6 |
101 - 11 = 90 |
*1002-4 |
.009 |
|
|
123456.789(-) |
||
这里在最后加102是为了排序的需要，-123456.789在数据库中实际存储为
60,89,67,45,23,11
而-123456.78901在数据库中实际存储为
60,89,67,45,23,11,91
可见，如果不在最后加上102，在排序时会出现-123456.789<-123456.78901的情况
2.3char、varchar2类型
英文字符的dump值可以直接理解为对应的ASCII码。
1 SYS@crm AS SYSDBA>select dump('abcdefg') from dual; 2 DUMP('ABCDEFG') 3 -------------------------------------- 4 Typ=96 Len=7: 97,98,99,100,101,102,103
中文字符的dump值则要看具体的字符集了。
1 SYS@crm AS SYSDBA>select dump('笑看风云') from dual; 2 DUMP('笑看风云') 3 -------------------------------------------------------------- 4 Typ=96 Len=12: 231,172,145,231,156,139,233,163,142,228,186,145 5 Elapsed: 00:00:00.00
6 SYS@crm AS SYSDBA>select dump('笑看风云',1010) from dual; 7 DUMP('笑看风云',1010) 8 ------------------------------------------------------------------------------------ 9 Typ=96 Len=12 CharacterSet=AL32UTF8: 231,172,145,231,156,139,233,163,142,228,186,145 10 Elapsed: 00:00:00.00
下面看看char varchar2两种类型数据的存储的区别。
1 SYS@crm AS SYSDBA>create table zjtest(c1 varchar2(10),c2 char(10)); 2 Table created. 3 Elapsed: 00:00:00.11 4 5 SYS@crm AS SYSDBA>insert into zjtest values (1,1); 6 1 row created. 7 Elapsed: 00:00:00.00 8 9 SYS@crm AS SYSDBA>insert into zjtest values (2,2); 10 1 row created. 11 Elapsed: 00:00:00.00 12 13 SYS@crm AS SYSDBA>insert into zjtest values (3,3); 14 1 row created. 15 Elapsed: 00:00:00.00 16 SYS@crm AS SYSDBA>commit; 17 SYS@crm AS SYSDBA>col DUMP(C1) format a50 18 SYS@crm AS SYSDBA>col DUMP(C2) format a50 19 SYS@crm AS SYSDBA>select c1,dump(c1),c2,dump(c2) from zjtest; 20 21 C1 DUMP(C1) C2 DUMP(C2) 22 ---------- -------------------------------------------------- ---------- -------------------------------------------------- 23 1 Typ=1 Len=1: 49 1 Typ=96 Len=10: 49,32,32,32,32,32,32,32,32,32 24 2 Typ=1 Len=1: 50 2 Typ=96 Len=10: 50,32,32,32,32,32,32,32,32,32 25 3 Typ=1 Len=1: 51 3 Typ=96 Len=10: 51,32,32,32,32,32,32,32,32,32 26 27 Elapsed: 00:00:00.00
两者的区别一览无余。
注：本文部分内容引用自eygle博客http://www.eygle.com/archives/2005/12/how_oracle_stor.html
