dump函数

select dump(chr(10),16) from dual;    --a
    select dump(chr(13),16) from dual;    --d
    select 'B'||chr(13)||'A'||CHR(10)||'C'||CHR(10)||chr(13)||'D'||chr(13)||chr(10)||'E' FROM DUAL;
    ---
    B
    C
    D
    E

一、函数用法


    函数的标准格式是：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;  --返回字符集对应的数值





附：dump函数对number的存储表示
http://www.eygle.com/archives/2005/12/how_oracle_stor.html
--------------------------------------------------------------------------------
How Oracle Store Number internal?
作者：eygle |English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/12/how_oracle_stor.html
--------------------------------------------------------------------------------

Oracle在数据库内部通过相应的算法转换来进行数据存储,本文简单介绍Oracle的Number型数值存储及转换.这个内容是为了回答留言板上的2119号问题.

我们可以通过DUMP函数来转换数字的存储形式,一个简单的输出类似如下格式:

SQL> select dump(1) from dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2

DUMP函数的输出格式类似:

类型 <[长度]>，符号/指数位 [数字1，数字2，数字3，......，数字20]

各位的含义如下:

1.类型: Number型,Type=2 (类型代码可以从Oracle的文档上查到)

2.长度:指存储的字节数

3.符号/指数位

在存储上,Oracle对正数和负数分别进行存储转换:

正数：加1存储(为了避免Null)
负数：被101减,如果总长度小于21个字节，最后加一个102(是为了排序的需要)

指数位换算:

正数：指数=符号/指数位 - 193 (最高位为1是代表正数)
负数：指数=62 - 第一字节

4.从<数字1>开始是有效的数据位

从<数字1>开始是最高有效位,所存储的数值计算方法为：

将下面计算的结果加起来：

每个<数字位>乘以100^(指数-N) (N是有效位数的顺序位，第一个有效位的N=0)

5. 举例说明

SQL> select dump(123456.789) from dual;
DUMP(123456.789)
-------------------------------
Typ=2 Len=6: 195,13,35,57,79,91


<指数>:   195 - 193 = 2
<数字1>    13 - 1    = 12 *100^(2-0) 120000
<数字2>    35 - 1    = 34 *100^(2-1) 3400
<数字3>    57 - 1    = 56 *100^(2-2) 56
<数字4>    79 - 1    = 78 *100^(2-3) .78
<数字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


<指数>     62 - 60 = 2(最高位是0，代表为负数)
<数字1> 101 - 89 = 12 *100^(2-0) 120000
<数字2> 101 - 67 = 34 *100^(2-1) 3400
<数字3> 101 - 45 = 56 *100^(2-2) 56
<数字4> 101 - 23 = 78 *100^(2-3) .78
<数字5> 101 - 11 = 90 *100^(2-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的情况。

对于2119号提问,第一个问题是:

1.请问为什么193,2各代表什么意思？

从上面就可以看到答案了.

2.还有NUMBER数字类型为什么有2个字节的长度呢？

对于这个问题,我想我们应该知道,所有数据类型最终在计算机里都以二进制存储,实际上所谓的数据类型都是我们定义的.所以存储只由算法决定.

所以这个问题是不成立的.比如:

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字节,而110却占了3个字节.