一、函數(shù)用法
函數(shù)的標準格式是：DUMP(expr[,return_fmt[,start_position][,length]])
基本參數(shù)時4個，最少可以填的參數(shù)時0個，當完全沒有參數(shù)時，直接返回null。另外3個參數(shù)也都有各自的默認值，一個一個來看：
expr：這個參數(shù)是要進行分析的表達式(數(shù)字或字符串等，可以是各個類型的值)
return_fmt：指返回參數(shù)的格式，這個參數(shù)有5種用法
1) 8：以8進制返回結(jié)果的值
2) 10：以10進制返回結(jié)果的值(默認)
3) 16：以16進制返回結(jié)果的值
4) 17：以單字符的形式返回結(jié)果的值
5) 1000：以上4種加上1000，表示在返回值中加上當前字符集
start_position：開始進行返回的字符位置
length：需要返回的字符長度
舉幾個例子：
SQL> SELECT DUMP('abc') FROM DUAL;
DUMP('ABC')
----------------------
Typ=96 Len=3: 97,98,99
SQL> SELECT DUMP('abc',16) FROM DUAL;
DUMP('ABC',16)
----------------------
Typ=96 Len=3: 61,62,63
SQL> SELECT DUMP('abc',1016) FROM DUAL;
DUMP('ABC',1016)
----------------------------------------
Typ=96 Len=3 CharacterSet=UTF8: 61,62,63
SQL> SELECT DUMP('abc',17,2,2) FROM DUAL;
DUMP('ABC',17,2,2)
------------------
Typ=96 Len=3: b,c
二、結(jié)果分析
結(jié)果的格式一般都是類似： typ=96 Len=3 [CharacterSet=UTF8]: 61,62,63
1、type
其中typ表示了當前的expr值的類型，例如2表示NUMBER，96表示CHAR等等。
具體的所有格式列表在SQL Reference文檔中沒有找到，但是在網(wǎng)上找到了，網(wǎng)址：http://vongates.itpub.net/post/2553/17275
CODE TYP
----- ------------------------------
1 VARCHAR2
2 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
112 CLOB
113 BLOB
114 BFILE
180 TIMESTAMP
181 TIMESTAMP WITH TIMEZONE
182 INTERVAL YEAR TO MONTH
183 INTERVAL DAY TO SECOND
208 UROWID
231 TIMESTAMP WITH LOCAL TIMEZONE
具體可以從USER_TAB_COLS視圖的定義中獲取這個方法：
select text from dba_views where view_name = 'USER_TAB_COLS';
2、Len
Len表示該值所占用的字節(jié)數(shù)。
這個沒有什么好解釋的，但是有時我們也可以使用這個特性發(fā)現(xiàn)一些問題，例如：
SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
UTF8
SQL> select dump('多多',1010) from dual;
DUMP('多多',1010)
-------------------------------------------------------
Typ=96 Len=6 CharacterSet=UTF8: 229,164,154,229,164,154
SQL> connwxq/wxq@win10gr2
SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
ZHS16GBK
SQL> select dump('多多',1010) from dual;
DUMP('多多',1010)
---------------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: 182,224,182,224
可以看到，UTF8對于漢字來說，需要3個字節(jié)來存儲1個漢字，而我們常用的ZHS16GBK只需要2個字節(jié)。
3、Value
最后就是具體的存儲值了，這里的講究就比較多了，不是幾句話就能夠講得清楚的，更何況我自己就不清楚。恩，簡單得來說，這些返回的數(shù)值就是Oracle在自己內(nèi)部對前面的這個expr值的存儲形式。對于非漢字的普通字符串，可以理解為就是它的ASCII碼(字符集中的編碼值)。可以舉個例子證明一下：
SQL> select dump('a=?5') from dual;
DUMP('A=?5')
-------------------------
Typ=96 Len=4: 97,61,63,53
SQL> SELECT CHR(97),CHR(61),CHR(63),CHR(53) FROM DUAL;
CHR(97) CHR(61) CHR(63) CHR(53)
------- ------- ------- -------
a = ? 5
SQL> SELECT ASCII('a'),ASCII('='),ASCII('?'),ASCII('5') FROM DUAL;
ASCII('A') ASCII('=') ASCII('?') ASCII('5')
---------- ---------- ---------- ----------
97 61 63 53
而對于漢字的存儲，就不太好測試了，而且也沒有搞清楚原理，應(yīng)該是直接套用字符集的漢字編碼規(guī)則的。
對于數(shù)字的存儲，并不像字符那么簡單，而是應(yīng)用了Oracle自己的一個算法，eygle有過很詳細的說明：
具體的算法原理可以參見eygle的這篇文章：http://www.eygle.com/archives/2005/12/how_oracle_stor.html
三、關(guān)于其他
順帶介紹一下怎么查找Oracle對字符集類型的編碼(dump文件的前2個字節(jié))：
SELECT NLS_CHARSET_NAME(1) FROM DUAL; --返回數(shù)值對應(yīng)的字符集名稱
SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL; --返回字符集對應(yīng)的數(shù)值
附：dump函數(shù)對number的存儲表示
http://www.eygle.com/archives/2005/12/how_oracle_stor.html
--------------------------------------------------------------------------------
How Oracle Store Number internal?
作者：eygle |English Version 【轉(zhuǎn)載時請以超鏈接形式標明文章出處和作者信息及本聲明】
鏈接：http://www.eygle.com/archives/2005/12/how_oracle_stor.html
--------------------------------------------------------------------------------
Oracle在數(shù)據(jù)庫內(nèi)部通過相應(yīng)的算法轉(zhuǎn)換來進行數(shù)據(jù)存儲,本文簡單介紹Oracle的Number型數(shù)值存儲及轉(zhuǎn)換.這個內(nèi)容是為了回答留言板上的2119號問題.
我們可以通過DUMP函數(shù)來轉(zhuǎn)換數(shù)字的存儲形式,一個簡單的輸出類似如下格式:
SQL> select dump(1) from dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2
DUMP函數(shù)的輸出格式類似:
類型 <[長度]>，符號/指數(shù)位 [數(shù)字1，數(shù)字2，數(shù)字3，......，數(shù)字20]
各位的含義如下:
1.類型: Number型,Type=2 (類型代碼可以從Oracle的文檔上查到)
2.長度:指存儲的字節(jié)數(shù)
3.符號/指數(shù)位
在存儲上,Oracle對正數(shù)和負數(shù)分別進行存儲轉(zhuǎn)換:
正數(shù)：加1存儲(為了避免Null)
負數(shù)：被101減,如果總長度小于21個字節(jié)，最后加一個102(是為了排序的需要)
指數(shù)位換算:
正數(shù)：指數(shù)=符號/指數(shù)位 - 193 (最高位為1是代表正數(shù))
負數(shù)：指數(shù)=62 - 第一字節(jié)
4.從<數(shù)字1>開始是有效的數(shù)據(jù)位
從<數(shù)字1>開始是最高有效位,所存儲的數(shù)值計算方法為：
將下面計算的結(jié)果加起來：
每個<數(shù)字位>乘以100^(指數(shù)-N) (N是有效位數(shù)的順序位，第一個有效位的N=0)
5. 舉例說明
SQL> select dump(123456.789) from dual;
DUMP(123456.789)
-------------------------------
Typ=2 Len=6: 195,13,35,57,79,91
<指數(shù)>: 195 - 193 = 2
<數(shù)字1> 13 - 1 = 12 *100^(2-0) 120000
<數(shù)字2> 35 - 1 = 34 *100^(2-1) 3400
<數(shù)字3> 57 - 1 = 56 *100^(2-2) 56
<數(shù)字4> 79 - 1 = 78 *100^(2-3) .78
<數(shù)字5> 91 - 1 = 90 *100^(2-4) .009
123456.789
SQL> select dump(-123456.789) from dual;
DUMP(-123456.789)
----------------------------------
Typ=2 Len=7: 60,89,67,45,23,11,102
<指數(shù)> 62 - 60 = 2(最高位是0，代表為負數(shù))
<數(shù)字1> 101 - 89 = 12 *100^(2-0) 120000
<數(shù)字2> 101 - 67 = 34 *100^(2-1) 3400
<數(shù)字3> 101 - 45 = 56 *100^(2-2) 56
<數(shù)字4> 101 - 23 = 78 *100^(2-3) .78
<數(shù)字5> 101 - 11 = 90 *100^(2-4) .009
123456.789(-)
現(xiàn)在再考慮一下為什么在最后加102是為了排序的需要，-123456.789在數(shù)據(jù)庫中實際存儲為
60,89,67,45,23,11
而-123456.78901在數(shù)據(jù)庫中實際存儲為
60,89,67,45,23,11,91
可見，如果不在最后加上102，在排序時會出現(xiàn)-123456.789<-123456.78901的情況。
對于2119號提問,第一個問題是:
1.請問為什么193,2各代表什么意思？
從上面就可以看到答案了.
2.還有NUMBER數(shù)字類型為什么有2個字節(jié)的長度呢？
對于這個問題,我想我們應(yīng)該知道,所有數(shù)據(jù)類型最終在計算機里都以二進制存儲,實際上所謂的數(shù)據(jù)類型都是我們定義的.所以存儲只由算法決定.
所以這個問題是不成立的.比如:
SQL> select dump(110) from dual;
DUMP(110)
---------------------
Typ=2 Len=3: 194,2,11
SQL> select dump(1100) from dual;
DUMP(1100)
-------------------
Typ=2 Len=2: 194,12
我們會看到,雖然1100>110,但是存儲上1100卻只占2字節(jié),而110卻占了3個字節(jié).
select o.name,
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'),
nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o
where o.owner#=u.user# and o.obj#=ac.synobj#),
ut.name),
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
decode(c.col#, 0, to_number(null), c.col#), c.deflength,
c.default$, h.distcnt,
case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
then h.lowval
else null
end,
case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
then h.hival
else null
end,
h.density, h.null_cnt,
case when nvl(h.distcnt,0) = 0 then h.distcnt
when h.row_cnt = 0 then 1
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt
and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then h.row_cnt
else h.bucket_cnt
end,
h.timestamp#, h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln,
c.spare3,
decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
null),
decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
decode(bitand(ac.flags, 2), 2, 'NO',
decode(bitand(ac.flags, 4), 4, 'NO',
decode(bitand(ac.flags, 8), 8, 'NO',
'N/A')))),
decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
'NO')),
decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
'NO')),
decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
case when nvl(h.row_cnt,0) = 0 then 'NONE'
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then 'FREQUENCY'
else 'HEIGHT BALANCED'
end,
decode(bitand(c.property, 1024), 1024,
(select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
cl.intcol# = rc.intcol#(+)),
decode(bitand(c.property, 1), 0, c.name,
(select tc.name from sys.attrcol$ tc
where c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h,
sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut
where o.obj# = c.obj#
and bitand(o.flags, 128) = 0
and o.owner# = userenv('SCHEMAID')
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and (o.type# in (3, 4) /* cluster, view */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
