SQL--瞭解DUMP函式(轉載)

轉載：http://vongates.itpub.net/post/2553/17275

作者：vongates

我們知道可能用dump這個函式來檢視錶中某列在datafile中的儲存內容，它的標準格式為：DUMP(expr[,number_format[,start_position][,length]]) 它將返回一個包含expr內部表示資訊的varchar2值

我們常用的函式格式為：dump(col_name,8|10|16|17)其中的8|10|16|17為number_formant的取值，分別指八進位制|十進位制|十六進位制|單字元 其中10為default的值，例項

編碼 資料型別 oracle版本
1 varchar2 7
2 number 7
8 long 7
12 date 7
23 raw 7
24 long raw 7
69 rowid 7
96 char 7
112 clob 8
113 blob 8
114 bfile 8
180 timestamp 9i
181 timestamp with timezone 9i
182 interval year to month 9i
183 interval day to second 9i
208 urowid 8i
231 timestamp with local timezone 9i

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as scott

SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y

SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected

SQL> select ename,dump(ename) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: 83,77,73,84,72
ALLEN Typ=1 Len=5: 65,76,76,69,78
WARD Typ=1 Len=4: 87,65,82,68
JONES Typ=1 Len=5: 74,79,78,69,83
MARTIN Typ=1 Len=6: 77,65,82,84,73,78
BLAKE Typ=1 Len=5: 66,76,65,75,69
CLARK Typ=1 Len=5: 67,76,65,82,75
SCOTT Typ=1 Len=5: 83,67,79,84,84
KING Typ=1 Len=4: 75,73,78,71
TURNER Typ=1 Len=6: 84,85,82,78,69,82
ADAMS Typ=1 Len=5: 65,68,65,77,83
JAMES Typ=1 Len=5: 74,65,77,69,83
FORD Typ=1 Len=4: 70,79,82,68
MILLER Typ=1 Len=6: 77,73,76,76,69,82

14 rows selected

SQL> select ename,dump(ename,17) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: S,M,I,T,H
ALLEN Typ=1 Len=5: A,L,L,E,N
WARD Typ=1 Len=4: W,A,R,D
JONES Typ=1 Len=5: J,O,N,E,S
MARTIN Typ=1 Len=6: M,A,R,T,I,N
BLAKE Typ=1 Len=5: B,L,A,K,E
CLARK Typ=1 Len=5: C,L,A,R,K
SCOTT Typ=1 Len=5: S,C,O,T,T
KING Typ=1 Len=4: K,I,N,G
TURNER Typ=1 Len=6: T,U,R,N,E,R
ADAMS Typ=1 Len=5: A,D,A,M,S
JAMES Typ=1 Len=5: J,A,M,E,S
FORD Typ=1 Len=4: F,O,R,D
MILLER Typ=1 Len=6: M,I,L,L,E,R

14 rows selected

SQL> select ename,dump(ename,17,2,4) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: M,I,T,H
ALLEN Typ=1 Len=5: L,L,E,N
WARD Typ=1 Len=4: A,R,D
JONES Typ=1 Len=5: O,N,E,S
MARTIN Typ=1 Len=6: A,R,T,I
BLAKE Typ=1 Len=5: L,A,K,E
CLARK Typ=1 Len=5: L,A,R,K
SCOTT Typ=1 Len=5: C,O,T,T
KING Typ=1 Len=4: I,N,G
TURNER Typ=1 Len=6: U,R,N,E
ADAMS Typ=1 Len=5: D,A,M,S
JAMES Typ=1 Len=5: A,M,E,S
FORD Typ=1 Len=4: O,R,D
MILLER Typ=1 Len=6: I,L,L,E

14 rows selected

SQL>

[@more@]