【Oracle】Oracle中dump函数的用法

ゞ浴缸里的玫瑰 2022-04-04 15:55 335阅读 0赞

# [【Oracle】Oracle中dump函数的用法][Oracle_Oracle_dump] #

一、函数标准格式：

DUMP(expr[,return_fmt[,start_position][,length]])

基本参数时4个，最少可以填的参数是0个。当完全没有参数时，直接返回null。另外3个参数也都有各自的默认值：

expr：这个参数是要进行分析的表达式（数字或字符串等，可以是各个类型的值）

return\_fmt：指返回参数的格式，有5种用法：

1）8：以8进制返回结果的值

2）10：以10进制返回结果的值（默认）

3）16：以16进制返回结果的值

4）17：以单字符的形式返回结果的值

5）1000：以上4种加上1000，表示在返回值中加上当前字符集

start\_position：开始进行返回的字符位置

length：需要返回的字符长度

二、示例

SYS@ tsid> select dump('abc') from dual;

DUMP('ABC')

\----------------------

Typ=96 Len=3: 97,98,99

SYS@ tsid> select dump('abc',16) from dual;

DUMP('ABC',16)

\----------------------

Typ=96 Len=3: 61,62,63

SYS@ tsid> select dump('abc',1016) from dual;

DUMP('ABC',1016)

\--------------------------------------------

Typ=96 Len=3 CharacterSet=ZHS16GBK: 61,62,63

SYS@ tsid> select dump('abc',17,2,2) from dual;

DUMP('ABC',17,2,2

\-----------------

Typ=96 Len=3: b,c

结果的格式一般都是类似：Typ=96 Len=3 \[CharacterSet=ZHS16GBK\]: 61,62,63

1、type

typ表示当前的expr值的类型。如：2表示NUMBER，96表示CHAR。

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

2、len

len表示该值所占用的字节数。

对于汉字，ZHS16GBK编码一个汉字需要2个字节，UTF8需要3个字节。

SQL> select dump('多多',1010) from dual;  
  
DUMP('多多',1010)  
\-------------------------------------------------------  
Typ=96 Len=6 CharacterSet=UTF8: 229,164,154,229,164,154

SQL> select dump('多多',1010) from dual;  
  
DUMP('多多',1010)  
\---------------------------------------------------  
Typ=96 Len=4 CharacterSet=ZHS16GBK: 182,224,182,224

3、 Value

具体的存储值。返回的数值就是[Oracle][]在自己内部对前面的这个expr值得存储形式。对于非汉字的普通字符串，可以理解为就是它的ASCII码。举例证明：

SYS@ tsid > select dump('a=?5') from dual;

DUMP('A=?5')

\-------------------------

Typ=96 Len=4: 97,61,63,53

SYS@ tsid > select chr(97),chr(61),chr(63),chr(53) from dual;

C C C C

\- - - -

a = ? 5

SYS@ tsid > select ascii('a'),ascii('='),ascii('?'),ascii('5') from dual;

ASCII('A') ASCII('=') ASCII('?') ASCII('5')

\---------- ---------- ---------- ----------

97         61         63         53

**ASCII码表：**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> ASCII值 </td> 
 <td style="vertical-align:top;"> 控制字符 </td> 
 <td style="vertical-align:top;"> ASCII值 </td> 
 <td style="vertical-align:top;"> 控制字符 </td> 
 <td style="vertical-align:top;"> ASCII值 </td> 
 <td style="vertical-align:top;"> 控制字符 </td> 
 <td style="vertical-align:top;"> ASCII值 </td> 
 <td style="vertical-align:top;"> 控制字符 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 0 </td> 
 <td style="vertical-align:top;"> NUT </td> 
 <td style="vertical-align:top;"> 32 </td> 
 <td style="vertical-align:top;"> (space) </td> 
 <td style="vertical-align:top;"> 64 </td> 
 <td style="vertical-align:top;"> @ </td> 
 <td style="vertical-align:top;"> 96 </td> 
 <td style="vertical-align:top;"> 、 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> SOH </td> 
 <td style="vertical-align:top;"> 33 </td> 
 <td style="vertical-align:top;"> ！ </td> 
 <td style="vertical-align:top;"> 65 </td> 
 <td style="vertical-align:top;"> A </td> 
 <td style="vertical-align:top;"> 97 </td> 
 <td style="vertical-align:top;"> a </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> STX </td> 
 <td style="vertical-align:top;"> 34 </td> 
 <td style="vertical-align:top;"> ” </td> 
 <td style="vertical-align:top;"> 66 </td> 
 <td style="vertical-align:top;"> B </td> 
 <td style="vertical-align:top;"> 98 </td> 
 <td style="vertical-align:top;"> b </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> ETX </td> 
 <td style="vertical-align:top;"> 35 </td> 
 <td style="vertical-align:top;"> # </td> 
 <td style="vertical-align:top;"> 67 </td> 
 <td style="vertical-align:top;"> C </td> 
 <td style="vertical-align:top;"> 99 </td> 
 <td style="vertical-align:top;"> c </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 4 </td> 
 <td style="vertical-align:top;"> EOT </td> 
 <td style="vertical-align:top;"> 36 </td> 
 <td style="vertical-align:top;"> $ </td> 
 <td style="vertical-align:top;"> 68 </td> 
 <td style="vertical-align:top;"> D </td> 
 <td style="vertical-align:top;"> 100 </td> 
 <td style="vertical-align:top;"> d </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 5 </td> 
 <td style="vertical-align:top;"> ENQ </td> 
 <td style="vertical-align:top;"> 37 </td> 
 <td style="vertical-align:top;"> % </td> 
 <td style="vertical-align:top;"> 69 </td> 
 <td style="vertical-align:top;"> E </td> 
 <td style="vertical-align:top;"> 101 </td> 
 <td style="vertical-align:top;"> e </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 6 </td> 
 <td style="vertical-align:top;"> ACK </td> 
 <td style="vertical-align:top;"> 38 </td> 
 <td style="vertical-align:top;"> &amp; </td> 
 <td style="vertical-align:top;"> 70 </td> 
 <td style="vertical-align:top;"> F </td> 
 <td style="vertical-align:top;"> 102 </td> 
 <td style="vertical-align:top;"> f </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 7 </td> 
 <td style="vertical-align:top;"> BEL </td> 
 <td style="vertical-align:top;"> 39 </td> 
 <td style="vertical-align:top;"> , </td> 
 <td style="vertical-align:top;"> 71 </td> 
 <td style="vertical-align:top;"> G </td> 
 <td style="vertical-align:top;"> 103 </td> 
 <td style="vertical-align:top;"> g </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 8 </td> 
 <td style="vertical-align:top;"> BS </td> 
 <td style="vertical-align:top;"> 40 </td> 
 <td style="vertical-align:top;"> ( </td> 
 <td style="vertical-align:top;"> 72 </td> 
 <td style="vertical-align:top;"> H </td> 
 <td style="vertical-align:top;"> 104 </td> 
 <td style="vertical-align:top;"> h </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 9 </td> 
 <td style="vertical-align:top;"> HT </td> 
 <td style="vertical-align:top;"> 41 </td> 
 <td style="vertical-align:top;"> ) </td> 
 <td style="vertical-align:top;"> 73 </td> 
 <td style="vertical-align:top;"> I </td> 
 <td style="vertical-align:top;"> 105 </td> 
 <td style="vertical-align:top;"> i </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 10 </td> 
 <td style="vertical-align:top;"> LF </td> 
 <td style="vertical-align:top;"> 42 </td> 
 <td style="vertical-align:top;"> * </td> 
 <td style="vertical-align:top;"> 74 </td> 
 <td style="vertical-align:top;"> J </td> 
 <td style="vertical-align:top;"> 106 </td> 
 <td style="vertical-align:top;"> j </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 11 </td> 
 <td style="vertical-align:top;"> VT </td> 
 <td style="vertical-align:top;"> 43 </td> 
 <td style="vertical-align:top;"> + </td> 
 <td style="vertical-align:top;"> 75 </td> 
 <td style="vertical-align:top;"> K </td> 
 <td style="vertical-align:top;"> 107 </td> 
 <td style="vertical-align:top;"> k </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 12 </td> 
 <td style="vertical-align:top;"> FF </td> 
 <td style="vertical-align:top;"> 44 </td> 
 <td style="vertical-align:top;"> , </td> 
 <td style="vertical-align:top;"> 76 </td> 
 <td style="vertical-align:top;"> L </td> 
 <td style="vertical-align:top;"> 108 </td> 
 <td style="vertical-align:top;"> l </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 13 </td> 
 <td style="vertical-align:top;"> CR </td> 
 <td style="vertical-align:top;"> 45 </td> 
 <td style="vertical-align:top;"> - </td> 
 <td style="vertical-align:top;"> 77 </td> 
 <td style="vertical-align:top;"> M </td> 
 <td style="vertical-align:top;"> 109 </td> 
 <td style="vertical-align:top;"> m </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 14 </td> 
 <td style="vertical-align:top;"> SO </td> 
 <td style="vertical-align:top;"> 46 </td> 
 <td style="vertical-align:top;"> . </td> 
 <td style="vertical-align:top;"> 78 </td> 
 <td style="vertical-align:top;"> N </td> 
 <td style="vertical-align:top;"> 110 </td> 
 <td style="vertical-align:top;"> n </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 15 </td> 
 <td style="vertical-align:top;"> SI </td> 
 <td style="vertical-align:top;"> 47 </td> 
 <td style="vertical-align:top;"> / </td> 
 <td style="vertical-align:top;"> 79 </td> 
 <td style="vertical-align:top;"> O </td> 
 <td style="vertical-align:top;"> 111 </td> 
 <td style="vertical-align:top;"> o </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 16 </td> 
 <td style="vertical-align:top;"> DLE </td> 
 <td style="vertical-align:top;"> 48 </td> 
 <td style="vertical-align:top;"> 0 </td> 
 <td style="vertical-align:top;"> 80 </td> 
 <td style="vertical-align:top;"> P </td> 
 <td style="vertical-align:top;"> 112 </td> 
 <td style="vertical-align:top;"> p </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 17 </td> 
 <td style="vertical-align:top;"> DCI </td> 
 <td style="vertical-align:top;"> 49 </td> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> 81 </td> 
 <td style="vertical-align:top;"> Q </td> 
 <td style="vertical-align:top;"> 113 </td> 
 <td style="vertical-align:top;"> q </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 18 </td> 
 <td style="vertical-align:top;"> DC2 </td> 
 <td style="vertical-align:top;"> 50 </td> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> 82 </td> 
 <td style="vertical-align:top;"> R </td> 
 <td style="vertical-align:top;"> 114 </td> 
 <td style="vertical-align:top;"> r </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 19 </td> 
 <td style="vertical-align:top;"> DC3 </td> 
 <td style="vertical-align:top;"> 51 </td> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> 83 </td> 
 <td style="vertical-align:top;"> X </td> 
 <td style="vertical-align:top;"> 115 </td> 
 <td style="vertical-align:top;"> s </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 20 </td> 
 <td style="vertical-align:top;"> DC4 </td> 
 <td style="vertical-align:top;"> 52 </td> 
 <td style="vertical-align:top;"> 4 </td> 
 <td style="vertical-align:top;"> 84 </td> 
 <td style="vertical-align:top;"> T </td> 
 <td style="vertical-align:top;"> 116 </td> 
 <td style="vertical-align:top;"> t </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 21 </td> 
 <td style="vertical-align:top;"> NAK </td> 
 <td style="vertical-align:top;"> 53 </td> 
 <td style="vertical-align:top;"> 5 </td> 
 <td style="vertical-align:top;"> 85 </td> 
 <td style="vertical-align:top;"> U </td> 
 <td style="vertical-align:top;"> 117 </td> 
 <td style="vertical-align:top;"> u </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 22 </td> 
 <td style="vertical-align:top;"> SYN </td> 
 <td style="vertical-align:top;"> 54 </td> 
 <td style="vertical-align:top;"> 6 </td> 
 <td style="vertical-align:top;"> 86 </td> 
 <td style="vertical-align:top;"> V </td> 
 <td style="vertical-align:top;"> 118 </td> 
 <td style="vertical-align:top;"> v </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 23 </td> 
 <td style="vertical-align:top;"> TB </td> 
 <td style="vertical-align:top;"> 55 </td> 
 <td style="vertical-align:top;"> 7 </td> 
 <td style="vertical-align:top;"> 87 </td> 
 <td style="vertical-align:top;"> W </td> 
 <td style="vertical-align:top;"> 119 </td> 
 <td style="vertical-align:top;"> w </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 24 </td> 
 <td style="vertical-align:top;"> CAN </td> 
 <td style="vertical-align:top;"> 56 </td> 
 <td style="vertical-align:top;"> 8 </td> 
 <td style="vertical-align:top;"> 88 </td> 
 <td style="vertical-align:top;"> X </td> 
 <td style="vertical-align:top;"> 120 </td> 
 <td style="vertical-align:top;"> x </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 25 </td> 
 <td style="vertical-align:top;"> EM </td> 
 <td style="vertical-align:top;"> 57 </td> 
 <td style="vertical-align:top;"> 9 </td> 
 <td style="vertical-align:top;"> 89 </td> 
 <td style="vertical-align:top;"> Y </td> 
 <td style="vertical-align:top;"> 121 </td> 
 <td style="vertical-align:top;"> y </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 26 </td> 
 <td style="vertical-align:top;"> SUB </td> 
 <td style="vertical-align:top;"> 58 </td> 
 <td style="vertical-align:top;"> : </td> 
 <td style="vertical-align:top;"> 90 </td> 
 <td style="vertical-align:top;"> Z </td> 
 <td style="vertical-align:top;"> 122 </td> 
 <td style="vertical-align:top;"> z </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 27 </td> 
 <td style="vertical-align:top;"> ESC </td> 
 <td style="vertical-align:top;"> 59 </td> 
 <td style="vertical-align:top;"> ; </td> 
 <td style="vertical-align:top;"> 91 </td> 
 <td style="vertical-align:top;"> [ </td> 
 <td style="vertical-align:top;"> 123 </td> 
 <td style="vertical-align:top;"> { </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 28 </td> 
 <td style="vertical-align:top;"> FS </td> 
 <td style="vertical-align:top;"> 60 </td> 
 <td style="vertical-align:top;"> &lt; </td> 
 <td style="vertical-align:top;"> 92 </td> 
 <td style="vertical-align:top;"> / </td> 
 <td style="vertical-align:top;"> 124 </td> 
 <td style="vertical-align:top;"> | </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 29 </td> 
 <td style="vertical-align:top;"> GS </td> 
 <td style="vertical-align:top;"> 61 </td> 
 <td style="vertical-align:top;"> = </td> 
 <td style="vertical-align:top;"> 93 </td> 
 <td style="vertical-align:top;"> ] </td> 
 <td style="vertical-align:top;"> 125 </td> 
 <td style="vertical-align:top;"> } </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 30 </td> 
 <td style="vertical-align:top;"> RS </td> 
 <td style="vertical-align:top;"> 62 </td> 
 <td style="vertical-align:top;"> &gt; </td> 
 <td style="vertical-align:top;"> 94 </td> 
 <td style="vertical-align:top;"> ^ </td> 
 <td style="vertical-align:top;"> 126 </td> 
 <td style="vertical-align:top;"> ~ </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 31 </td> 
 <td style="vertical-align:top;"> US </td> 
 <td style="vertical-align:top;"> 63 </td> 
 <td style="vertical-align:top;"> ? </td> 
 <td style="vertical-align:top;"> 95 </td> 
 <td style="vertical-align:top;"> — </td> 
 <td style="vertical-align:top;"> 127 </td> 
 <td style="vertical-align:top;"> DEL </td> 
 </tr> 
 </tbody> 
</table>

对于数字的存储，不像字符那么简单，而是应用Oracle自己的一个[算法][Link 1]。

分类: [DB\_R\_Oracle][DB_R_Oracle]

[Oracle_Oracle_dump]: https://www.cnblogs.com/HDK2016/p/6898045.html
[Oracle]: http://lib.csdn.net/base/oracle
[Link 1]: http://lib.csdn.net/base/datastructure
[DB_R_Oracle]: https://www.cnblogs.com/HDK2016/category/918237.html