oracle的dump函数应用

骑猪看日落 2022-08-13 00:49 262阅读 0赞

一、函数用法  
    函数的标准格式是：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：需要返回的字符长度  
   
举几个例子：  
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  
   
二、结果分析  
   
    结果的格式一般都是类似： typ=96 Len=3 \[CharacterSet=UTF8\]: 61,62,63  
   
    1、type  
   
    其中typ表示了当前的expr值的类型，例如2表示NUMBER，96表示CHAR等等。  
    具体的所有格式列表在SQL Reference文档中没有找到，但是在网上找到了，网址：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表示该值所占用的字节数。  
   
    这个没有什么好解释的，但是有时我们也可以使用这个特性发现一些问题，例如：  
   
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个字节来存储1个汉字，而我们常用的ZHS16GBK只需要2个字节。  
   
   
    3、Value  
   
    最后就是具体的存储值了，这里的讲究就比较多了，不是几句话就能够讲得清楚的，更何况我自己就不清楚。恩，简单得来说，这些返回的数值就是Oracle在自己内部对前面的这个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  
   
    而对于汉字的存储，就不太好测试了，而且也没有搞清楚原理，应该是直接套用字符集的汉字编码规则的。  
   
    对于数字的存储，并不像字符那么简单，而是应用了Oracle自己的一个算法，eygle有过很详细的说明：  
    具体的算法原理可以参见eygle的这篇文章：http://www.eygle.com/archives/2005/12/how\_oracle\_stor.html  
   
   
三、关于其他  
   
    顺带介绍一下怎么查找Oracle对字符集类型的编码(dump文件的前2个字节)：  
   
    SELECT NLS\_CHARSET\_NAME(1) FROM DUAL;  --返回数值对应的字符集名称  
    SELECT NLS\_CHARSET\_ID('US7ASCII') FROM DUAL;  --返回字符集对应的数值