Oracle中编码与字符转换

最新推荐文章于 2024-07-25 11:08:25 发布

heqiyu34

最新推荐文章于 2024-07-25 11:08:25 发布

阅读量1w

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

161 篇文章 0 订阅

订阅专栏

一、ASCII码与字符相互转换

^{ASCII(x) gets the ASCII value of the character x，CHR() and ASCII() have the opposite effect.}

^例子：

^{SELECT ASCII('a'), ASCII('A'),ASCII('0') from dual}

^结果：^{ASCII('a') ASCII('A') ASCII('0')
97 65 48}

^{------------------------------------------------}

^例子：

^{select chr(65) from dual}

^{结果：
CHR(65)}^A

^{-------------------------------------------------------------}

^{值得注意的是：Ascii gives the ASCII value of the first character of a string。也即是说他只对一个字符串中的第一个字符起作用，你弄多长的字符串放进去它也只认第一个字符。}

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://wanqiufeng.blog.51cto.com/409430/438193

二、dump函数与utl_raw

函数式：

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=ZHS16GBK: 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=ZHS16GBK]: 61,62,63

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

len
len表示该值所占用的字节数。
对于汉字，ZHS16GBK编码一个汉字需要2个字节，UTF8需要3个字节。

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

SQL>  select to_number('3230','xxxx')from dual;

TO_NUMBER('3230','XXXX')
------------------------
                   12848
SQL>   select to_number('3430','xxxx')from dual;

TO_NUMBER('3430','XXXX')
------------------------
                   13360
SQL>      select to_number('3036','xxxx')from dual;

TO_NUMBER('3036','XXXX')
------------------------
                   12342

SQL>
SQL>      select chr(12848)from dual;

CHR(12848)
----------
20
SQL>       select chr(13360)from dual;

CHR(13360)
----------
40
SQL>      select chr(12342)from dual;

CHR(12342)
----------
06

将CHR(12848)+CHR(13360)+CHR(12342)=204006

utl_raw.cast_to_xxx()作为dump的逆函数

sql>select dump('201201',16) from dual;
dump('201201',16)
---------------------------------------------------------
Typ=96 Len=6: 32,30,31,32,30,31

sql>select utl_raw.cast_to_varchar2('323031323031') value from dual
201201

来源：http://www.jydba.net/oracle-dump%E5%87%BD%E6%95%B0%E7%9A%84%E4%B8%8Eutl_raw/
注：另外还可以使用dbms_stats.convert_raw_value函数转换。

三、自定义函数实现存储编码与日期等的转换（一）

本来打算研究DML操作中数据块、日志块和UNDO块之间的关系，但发现DUMP出来的数据都是16进制的，不易读懂。于是搜索了一些关于内部存储算法的文章，写了2个函数：bdump()和get_inner()。不尽完善，但足够读懂DUMP文件中的数据了。

bdump的意思是before dump，就是返回dump之前的数据原型。

get_inner的意思是获取内部数据的直观显示。字段类型需要作为入参手工输入，目前实现了以下3种类型：

-- 's'表示按照字符类型进行转换

-- 'n'表示按照数值类型进行转换

-- 'd'表示按照日期类型进行转换

使用举例：

SQL> select my_tool.BDUMP(dump(2390.293)) from dual;

MY_TOOL.BDUMP(DUMP(2390.293))
-----------------------------
2390.293

SQL> select my_tool.BDUMP(dump(';akdsfjowier2938')) from dual;

MY_TOOL.BDUMP(DUMP(';AKDSFJOWI
------------------------------
;akdsfjowier2938

SQL> select my_tool.BDUMP(dump(sysdate)) from dual;

MY_TOOL.BDUMP(DUMP(SYSDATE))
-------------------------------
2007-09-22 23:51:04

SQL> select my_tool.GET_INNER('[18] 59 55 45 43 48 41 4f 54 49 41 4e 5c 74 69 61 6e 79 63', 's') from dual;

MY_TOOL.GET_INNER('[18]5955454
-------------------------------
YUECHAOTIAN\tianyc

SQL> select my_tool.GET_INNER('[ 6] c5 0c 5a 1a 5d 2f', 'n') from dual;

MY_TOOL.GET_INNER('[6]C50C5A1A
-------------------------------
1189259246

SQL> select my_tool.GET_INNER('[ 7] 78 6b 09 08 16 30 1c', 'd') from dual;

MY_TOOL.GET_INNER('[7]786B0908
-------------------------------
2007-09-08 21:47:27

SQL>

相关代码如下。

my_tool包中增加2个函数，

/*------------------------------------------------------------
名称：BDUMP
目的：将DUMP出来的10进制数据返回数据原值
可以处理的类型：数值类型、字符类型、日期类型、时间戳类型
日期：2007-09-21
作者：yuechaotian
入参：为DUMP(<...>, 10)结果格式，。例如：Typ=96 Len=2: 97,100
------------------------------------------------------------*/
FUNCTION BDUMP(V_DUMP_IN VARCHAR2) RETURN VARCHAR2;

/*------------------------------------------------------------
名称：GET_INNER
目的：将DUMP出来的文件中16进制数值返回10进制数据
日期：2007-09-22
作者：yuechaotian
参数v_dump_in：带长度的16进制数据（例如[ 3] c2 08 2c）
参数v_type_in：数据类型
                     N -- NUMBER
                     S -- char, varchar, varchar2
                     D -- DATE
  ------------------------------------------------------------*/
FUNCTION GET_INNER(V_DUMP_IN VARCHAR2, V_TYPE_IN VARCHAR2) RETURN VARCHAR2;

my_tool包体中增加7个函数，其中 inner_to_<xx> 函数被 bdump和get_inner调用

/*--------------------------INNER_TO_NUMBER------------------------
目的：将内部数值转换成可读的10进制数值
例如，输入=>193,2   输出=>1
        输入=>62,100,102   输出=>-1
存在问题：如果负数总长度小于21个字节，最后加一个102。
            代码中直接将102改为101进行计算。
            若大于等于21字节时计算出错
-------------------------------------------------------------------*/
FUNCTION INNER_TO_NUMBER(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
    RETURN VARCHAR2 IS
    N_POWER      NUMBER(38); --指数
    V_DATA_MID   VARCHAR2(1000); --中间数据
    V_DATA_FIRST VARCHAR2(1000); --符号/指数位
    N_DATA_1     NUMBER(38, 10); --1位数据
    N_DATA       NUMBER(38, 10) := 0; --数据
    V_FLAG_NEG   BOOLEAN := FALSE; --负数标志
BEGIN
    V_DATA_MID := P_DATA_MID_IN || ',';
    --1.获取符号/指数位
    V_DATA_FIRST := TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1));
    --2获取指数
    --2.1正数
    IF LENGTH(V_DATA_FIRST) = 3 THEN
      N_POWER := TO_NUMBER(V_DATA_FIRST) - 193;
      --2.2负数
    ELSE
      N_POWER    := 62 - TO_NUMBER(V_DATA_FIRST);
      V_FLAG_NEG := TRUE;
    END IF;
    --3.截取中间数据（不包括指数）
    V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    --4.获取数据
    FOR I IN 1 .. P_LENGTH_IN - 1 LOOP
      --4.1截取1位数据
      N_DATA_1 := TO_NUMBER(SUBSTR(V_DATA_MID,
                                   1,
                                   INSTR(V_DATA_MID, ',') - 1));
      --4.2计算总数据
      --4.2.1负数
      IF V_FLAG_NEG THEN
        --4.2.1.1去掉最后一位102
        IF I = P_LENGTH_IN - 1 THEN
          N_DATA_1 := N_DATA_1 - 1;
        END IF;
        --4.2.1.2计算
        N_DATA := N_DATA - (101 - N_DATA_1) * POWER(100, N_POWER - (I - 1));
        --4.2.2正数
      ELSE
        N_DATA := N_DATA + (N_DATA_1 - 1) * POWER(100, N_POWER - (I - 1));
      END IF;
      --4.3剔除已处理的数据
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --5.返回结果
    RETURN TO_CHAR(N_DATA);
EXCEPTION
    WHEN OTHERS THEN
      RETURN '调用函数：INNER_TO_NUMBER 失败！错误原因：' || SQLERRM;
END INNER_TO_NUMBER;
/*------------------------INNER_TO_STRING------------------------------
目的：将内部ASCII码显示的字符以字符形式返回
例如，输入=>97,97 输出=>'aa'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_STRING(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
    RETURN VARCHAR2 IS
    V_RESULT   VARCHAR2(32767); --中间字符串
    V_DATA_MID VARCHAR2(1000); --中间数据
    V_DATA_1   VARCHAR2(32767); --1位数据
BEGIN
    V_DATA_MID := P_DATA_MID_IN || ',';
    --1.根据ASCII码，计算对应的字符
    FOR I IN 1 .. P_LENGTH_IN LOOP
      V_DATA_1   := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      V_RESULT   := V_RESULT || CHR(V_DATA_1);
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --2.返回结果
    RETURN V_RESULT;
EXCEPTION
    WHEN OTHERS THEN
      RETURN '调用函数：INNER_TO_STRING 失败！错误原因：' || SQLERRM;
END INNER_TO_STRING;
/*------------------------INNER_TO_DATE_1------------------------------
目的：将磁盘内部显示的日期以字符形式返回
例如，输入=>'120,107,9,22,13,32,4' 输出=>'2007-09-22 12:31:03'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_DATE_1(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
    RETURN VARCHAR2 IS
    V_RESULT   VARCHAR2(32767); --中间字符串
    V_DATA_MID VARCHAR2(1000); --中间数据
    V_DATA_1   VARCHAR2(32767); --1位数据
BEGIN
    V_DATA_MID := P_DATA_MID_IN || ',';
    --1.计算日期类型
    FOR I IN 1 .. P_LENGTH_IN LOOP
      V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      -- 1.1世纪、年度
      IF I < 3 THEN
        V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 100));
        -- 1.2时分秒
      ELSIF I > 4 THEN
        V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 1));
      END IF;
      V_RESULT := V_RESULT || LPAD(V_DATA_1, 2, '0');
      -- 1.3剔除已处理的数据
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --2.返回指定格式的日期类型
    RETURN TO_CHAR(TO_DATE(V_RESULT, 'yyyymmddhh24miss'),
                   'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
    WHEN OTHERS THEN
      RETURN '调用函数：INNER_TO_DATE_1 失败！错误原因：' || SQLERRM;
END INNER_TO_DATE_1;
/*------------------------INNER_TO_DATE_2------------------------------
目的：将内存内部显示的日期以字符形式返回
例如，输入=>'215,7,9,22,12,31,3,0' 输出=>'2007-09-22 12:31:03'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_DATE_2(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
    RETURN VARCHAR2 IS
    V_RESULT   VARCHAR2(32767); --中间字符串
    V_DATA_MID VARCHAR2(1000); --中间数据
    V_DATA_1   VARCHAR2(32767); --1位数据
BEGIN
    V_DATA_MID := P_DATA_MID_IN || ',';
    -- 1.处理世纪、年度
    --1.1获取第1位的16进制数据
    V_DATA_1 := TRIM(TO_CHAR(TO_NUMBER(SUBSTR(V_DATA_MID,
                                              1,
                                              INSTR(V_DATA_MID, ',') - 1)),
                             'xxx'));
    --1.2截取后7位
    V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    --1.3将前两位反拼
    V_DATA_1 := TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1)) ||
                V_DATA_1;
    --1.4转换成10进制的世纪、年度
    V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1, 'xxx'));
    V_RESULT := V_RESULT || V_DATA_1;
    /*处理世纪、年度的另一种方法
    -- 1.1 获取第1位
    V_DATA_1   := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
    --1.2截取后7位
    V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    --1.3计算世纪、年度
    V_DATA_1 := V_DATA_1 + TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1)) * 256;
    V_RESULT := V_RESULT || V_DATA_1;
    */
    --2截取后6位，用以计算月日时分秒
    V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    --3.计算月日时分秒
    FOR I IN 1 .. P_LENGTH_IN - 3 LOOP
      V_DATA_1   := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      V_RESULT   := V_RESULT || LPAD(V_DATA_1, 2, '0');
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --4.返回指定格式的日期类型
    RETURN TO_CHAR(TO_DATE(V_RESULT, 'yyyymmddhh24miss'),
                   'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
    WHEN OTHERS THEN
      RETURN '调用函数：INNER_TO_DATE_2 失败！错误原因：' || SQLERRM;
END INNER_TO_DATE_2;

/*------------------------INNER_TO_STAMP_1------------------------------
目的：将磁盘内部显示的时间戳以字符形式返回
例如，输入=>'120,107,9,22,14,41,5,14,230,178,128'
        输出=>'2007-09-22 13:40:04.250000000'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_STAMP_1(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
    RETURN VARCHAR2 IS
    V_RESULT   VARCHAR2(32767); --中间字符串
    V_DATA_MID VARCHAR2(1000); --中间数据
    V_DATA_1   VARCHAR2(32767); --1位数据
    V_FF       VARCHAR2(32767); --微秒
BEGIN
    V_DATA_MID := P_DATA_MID_IN || ',';
    --1.计算日期类型
    FOR I IN 1 .. P_LENGTH_IN - 4 LOOP
      V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      -- 1.1世纪、年度
      IF I < 3 THEN
        V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 100));
        -- 1.2时分秒
      ELSIF I > 4 THEN
        V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 1));
      END IF;
      V_RESULT := V_RESULT || LPAD(V_DATA_1, 2, '0');
      -- 1.3剔除已处理的数据
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --2.计算微秒
    --2.1 将10进制微秒拼成16进制串
    FOR I IN 1 .. 4 LOOP
      V_DATA_1   := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      V_FF       := V_FF || TRIM(TO_CHAR(TO_NUMBER(V_DATA_1), 'xxx'));
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    --2.2 将16进制微秒转换成10进制微秒
    V_FF := TRIM(TO_CHAR(TO_NUMBER(V_FF, 'xxxxxxxxxxxxx')));
    --3.返回指定格式的时间戳类型
    V_RESULT := V_RESULT || '.' || V_FF;
    RETURN TO_CHAR(TO_TIMESTAMP(V_RESULT, 'yyyymmddhh24miss.ff'),
                   'yyyy-mm-dd hh24:mi:ss.ff');
EXCEPTION
    WHEN OTHERS THEN
      RETURN '调用函数：INNER_TO_DATE_1 失败！错误原因：' || SQLERRM;
END INNER_TO_STAMP_1;

/*----------------------------------
目的：将DUMP出来的数值类型的数据返回数据原值
日期：2007-09-22
----------------------------------*/
FUNCTION BDUMP(V_DUMP_IN VARCHAR2) RETURN VARCHAR2 IS
    V_TYPE     VARCHAR2(20); --类型
    N_LENGTH   NUMBER(38); --长度
    V_DATA     VARCHAR2(1000); --数据
    V_DATA_MID VARCHAR2(1000); --中间数据
BEGIN
    --1.获取类型
    V_TYPE := SUBSTR(V_DUMP_IN,
                     INSTR(V_DUMP_IN, '=') + 1,
                     INSTR(V_DUMP_IN, ' ') - INSTR(V_DUMP_IN, '=') - 1);
    --2.获取总长度
    N_LENGTH := TO_NUMBER(SUBSTR(V_DUMP_IN,
                                 INSTR(V_DUMP_IN, '=', 5) + 1,
                                 INSTR(V_DUMP_IN, ':') -
                                 INSTR(V_DUMP_IN, '=', 5) - 1));

    --3.截取中间数据（去掉头信息）
    V_DATA_MID := SUBSTR(V_DUMP_IN, INSTR(V_DUMP_IN, ':') + 1);
    --4.获取数据
    CASE V_TYPE
    -- 4.1 number类型
      WHEN '2' THEN
        V_DATA := INNER_TO_NUMBER(V_DATA_MID, N_LENGTH);
        -- 4.2 char类型
      WHEN '96' THEN
        V_DATA := INNER_TO_STRING(V_DATA_MID, N_LENGTH);
        -- 4.2 varchar2和varchar类型
      WHEN '1' THEN
        V_DATA := INNER_TO_STRING(V_DATA_MID, N_LENGTH);
        -- 4.3 date类型（磁盘存储格式）
      WHEN '12' THEN
        V_DATA := INNER_TO_DATE_1(V_DATA_MID, N_LENGTH);
        -- 4.4 date类型（内存存储格式）
      WHEN '13' THEN
        V_DATA := INNER_TO_DATE_2(V_DATA_MID, N_LENGTH);
        -- 4.5 timestamp类型（磁盘存储格式）
      WHEN '180' THEN
        V_DATA := INNER_TO_STAMP_1(V_DATA_MID, N_LENGTH);
    END CASE;
    RETURN V_DATA;
EXCEPTION
    WHEN OTHERS THEN
      RETURN('调用函数BDUMP失败！错误原因:' || SQLERRM);
END BDUMP;

/*------------------------------------------------------------
目的：将DUMP出来的16进制数值返回10进制数据
日期：2007-09-22
作者：yuechaotian
输入=>[ 2] c1 02，输出=>
------------------------------------------------------------*/
FUNCTION GET_INNER(V_DUMP_IN VARCHAR2, V_TYPE_IN VARCHAR2) RETURN VARCHAR2 IS
    V_RESULT   VARCHAR2(32767); --返回结果
    V_DATA_MID VARCHAR2(32767); --中间数据
    V_TEN      VARCHAR2(32767); --中间数据（10进制）
    V_DATA_1   VARCHAR2(32767); --1位数据
    N_LENGTH   NUMBER(10); --长度
BEGIN
    V_DATA_MID := V_DUMP_IN;
    --获取长度
    N_LENGTH := TO_NUMBER(TRIM(SUBSTR(V_DATA_MID, 2, 2)));
    --截取DUMP数据（去掉头信息），并替换空格为逗号
    V_DATA_MID := REPLACE(SUBSTR(V_DATA_MID, 7), CHR(32), ',') || ',';
    --替换16进制数据为10进制数据
    FOR I IN 1 .. N_LENGTH LOOP
      V_DATA_1   := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
      V_TEN      := V_TEN || TO_NUMBER(V_DATA_1, 'xx') || ',';
      V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
    END LOOP;
    V_TEN := SUBSTR(V_TEN, 1, LENGTH(V_TEN) - 1);
    CASE UPPER(V_TYPE_IN)
      WHEN 'N' THEN
        V_RESULT := INNER_TO_NUMBER(V_TEN, N_LENGTH);
      WHEN 'S' THEN
        V_RESULT := INNER_TO_STRING(V_TEN, N_LENGTH);
      WHEN 'D' THEN
        V_RESULT := INNER_TO_DATE_1(V_TEN, N_LENGTH);
    END CASE;
    RETURN V_RESULT;
EXCEPTION
    WHEN OTHERS THEN
      RETURN('调用函数GET_INNER失败！错误原因:' || SQLERRM);
END GET_INNER;

来源：http://biancheng.dnbcw.info/oracle/300289.html

三、自定义函数实现存储编码与日期等的转换（二）

前一阵写了Oracle基本数据类型存储格式浅析，对各种数量类型的存储进行了简单的描述，而后又写了一篇repare包修复坏块，其中自己写了一个程序包来恢复DUMP后的数据。但是那个程序包主要是针对repare包生成的结果的，因此通用性不好。

这篇文章将那个程序包修改并简化，变为一个函数。下面给出这个函数的实现和使用例子：

SQL> CREATE OR REPLACE FUNCTION F_GET_FROM_DUMP
  2  (
  3 P_DUMP IN VARCHAR2,
  4 P_TYPE IN VARCHAR2
  5  )
  6  RETURN VARCHAR2 AS
  7 V_LENGTH_STR VARCHAR2(10);
  8 V_LENGTH NUMBER DEFAULT 7;
  9 V_DUMP_ROWID VARCHAR2(30000);
10
11 V_DATE_STR VARCHAR2(100);
12 TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13 V_DATE T_DATE;
14
15 FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2
16 AS
17 V_STR VARCHAR2(30000) := P_STR;
18 V_POSITION NUMBER := P_POSITION;
19 V_STR_PART VARCHAR2(2);
20 V_RETURN VARCHAR2(30000);
21 BEGIN
22 WHILE (V_POSITION != 0) LOOP
23    V_STR_PART := SUBSTR(V_STR, 1, V_POSITION - 1);
24    V_STR := SUBSTR(V_STR, V_POSITION + 1);
25
26    IF V_POSITION = 2 THEN
27    V_RETURN := V_RETURN || '0' || V_STR_PART;
28    ELSIF V_POSITION = 3 THEN
29    V_RETURN := V_RETURN || V_STR_PART;
30    ELSE
31    RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID');
32    END IF;
33
34    V_POSITION := INSTR(V_STR, ',');
35 END LOOP;
36 RETURN REPLACE(V_RETURN , ',');
37 END F_ADD_PREFIX_ZERO;
38
39  BEGIN
40 IF SUBSTR(P_DUMP, 1, 3) = 'Typ' THEN
41 V_DUMP_ROWID := SUBSTR(P_DUMP, INSTR(P_DUMP, ':') + 2);
42 ELSE
43 V_DUMP_ROWID := P_DUMP;
44 END IF;
45
46 IF P_TYPE = 'VARCHAR2' OR P_TYPE = 'CHAR' THEN
47
48 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
49
50 RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID));
51
52 ELSIF P_TYPE = 'NUMBER' THEN
53
54 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
55
56 RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID)));
57
58 ELSIF P_TYPE = 'DATE' THEN
59
60 V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';
61
62 FOR I IN 1..7 LOOP
63    V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,
64    INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');
65 END LOOP;
66
67 V_DATE(1) := V_DATE(1) - 100;
68 V_DATE(2) := V_DATE(2) - 100;
69
70 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN
71    V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '
00'));
72 ELSE
73    V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));
74 END IF;
75
76 V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||
77    TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1);
78 RETURN (V_DATE_STR);
79
80 ELSIF ((P_TYPE LIKE 'TIMESTAMP(_)') OR (P_TYPE = 'TIMESTAMP')) THEN
81
82 V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';
83
84 FOR I IN 1..11 LOOP
85    V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,
86    INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');
87 END LOOP;
88
89 V_DATE(1) := V_DATE(1) - 100;
90 V_DATE(2) := V_DATE(2) - 100;
91
92 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN
93    V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '
00'));
94 ELSE
95    V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));
96 END IF;
97
98 V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||
99    TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1) ||
'.' ||
100    SUBSTR(TO_CHAR(V_DATE(8) * POWER(256, 3) + V_DATE(9) * POWER(256, 2) + V_DATE(10) * 256 + V_
DATE(11)),
101    1, NVL(TO_NUMBER(SUBSTR(P_TYPE, 11, 1)), 6));
102 RETURN (V_DATE_STR);
103
104 ELSIF P_TYPE = 'RAW' THEN
105
106 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
107
108 RETURN(V_DUMP_ROWID);
109
110 ELSIF P_TYPE = 'ROWID' THEN
111
112 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
113    RETURN (DBMS_ROWID.ROWID_CREATE(
114    1,
115    TO_NUMBER(SUBSTR(V_DUMP_ROWID, 1, 8), 'XXXXXXXXXXX'),
116    TRUNC(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX')/64),
117    TO_NUMBER(MOD(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX'), 64) ||
118    TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), 'XXXXXXXXXXX')),
119    TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), 'XXXXXX')));
120
121 ELSE
122 RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' || P_TYPE || ' TYPE');
123 END IF;
124
125  END;
126  /

函数已创建。

SQL> SELECT F_GET_FROM_DUMP(DUMP(2342.231, 16), 'NUMBER') FROM DUAL;

F_GET_FROM_DUMP(DUMP(2342.231,16),'NUMBER')
--------------------------------------------
2342.231

SQL> SELECT F_GET_FROM_DUMP(DUMP(-0.00234, 16), 'NUMBER') FROM DUAL;

F_GET_FROM_DUMP(DUMP(-0.00234,16),'NUMBER')
---------------------------------------------
-.00234

SQL> SELECT F_GET_FROM_DUMP(DUMP('23EJF.M>', 16), 'VARCHAR2') FROM DUAL;

F_GET_FROM_DUMP(DUMP('23EJF.M>',16),'VARCHAR2')
------------------------------------------------
23EJF.M>

SQL> SELECT F_GET_FROM_DUMP(DUMP('测试', 16), 'VARCHAR2') FROM DUAL;

F_GET_FROM_DUMP(DUMP('测试',16),'VARCHAR2')
------------------------------------------------
测试

由于在SQL中直接使用DATE类型和Oracle存储的不一致，因此解析DATE和TIMESTAMP类型需要通过表中存储的数据，而不能通过SQL中的TO_DATE或SYSDATE。在SQL中直接使用的DATE类型的解析由于意义不大而没有给出。关于在SQL中直接使用DATE和存储在表中的DATE类型的区别，可以参考我的Oracle基本数据类型存储格式浅析中日期类型的文章，连接在文章末尾给出。

SQL> CREATE TABLE TEST_DATE (TIME1 DATE, TIME2 TIMESTAMP, TIME3 TIMESTAMP(9));

表已创建。

SQL> INSERT INTO TEST_DATE VALUES (SYSDATE,
  2  TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'),
  3  TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'));

已创建 1 行。

SQL> COL GET_DUMP FORMAT A30
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT TIME1, F_GET_FROM_DUMP(DUMP(TIME1, 16), 'DATE') GET_DUMP FROM TEST_DATE;

TIME1             GET_DUMP
------------------- ------------------------------
2005-04-09 23:00:04 2005-4-9 23:0:4

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

会话已更改。

SQL> SELECT TIME2, F_GET_FROM_DUMP(DUMP(TIME2, 16), 'TIMESTAMP') GET_DUMP
  2  FROM TEST_DATE;

TIME2                            GET_DUMP
---------------------------------- ------------------------
2004-04-09 22:59:43.234232       2004-4-9 22:59:43.234232

SQL> SELECT TIME3, F_GET_FROM_DUMP(DUMP(TIME3, 16), 'TIMESTAMP(9)') GET_DUMP
  2  FROM TEST_DATE;

TIME3                            GET_DUMP
---------------------------------- ------------------------
2004-04-09 22:59:43.234232222    2004-4-9 22:59:43.234232222

对于SQL中直接使用的DATE类型会报错：

[PHP]
SQL> SELECT SYSDATE, F_GET_FROM_DUMP(DUMP(SYSDATE, 16), 'DATE') GET_DUMP FROM DUAL;

SYSDATE          GET_DUMP
------------------- ------------------------------
2005-04-09 23:04:58 -###93-4-9 22:3:57

SQL> SELECT RAW_DATA, F_GET_FROM_DUMP(DUMP(RAW_DATA, 16), 'RAW') GET_DUMP
  2  FROM TEST_RAW;

RAW_DATA          GET_DUMP
-------------------- ------------------------------
F5021C             f5021c

这个函数目前支持CHAR、VARCHAR2、NUMBER、DATE、TIMESTAMP和RAW类型，上面分别举了例子。

函数的第一个参数可以是DUMP函数的输出，也可以是数据库中的直接存储信息（需要用逗号分隔）。

SQL> SELECT F_GET_FROM_DUMP('Typ=96 Len=4: 74,65,73,74', 'VARCHAR2') GET_DUMP
  2  FROM DUAL;

GET_DUMP
------------------------------
test

SQL> SELECT F_GET_FROM_DUMP('74,65,73,74', 'VARCHAR2') GET_DUMP
  2  FROM DUAL;

GET_DUMP
------------------------------
test

编写这个函数所根据的规则来自下列文章：

Oracle基本数据类型存储格式浅析（一）——字符类型： http://blog.itpub.net/post/468/9287

Oracle基本数据类型存储格式浅析（二）——数字类型： http://blog.itpub.net/post/468/9445

Oracle基本数据类型存储格式浅析（三）——日期类型（一）： http://blog.itpub.net/post/468/10113

Oracle基本数据类型存储格式浅析（三）——日期类型（二）： http://blog.itpub.net/post/468/10293

Oracle基本数据类型存储格式浅析（三）——日期类型（三）： http://blog.itpub.net/post/468/10582

Oracle基本数据类型存储格式浅析（三）——日期类型（四）： http://blog.itpub.net/post/468/13636

Oracle基本数据类型存储格式浅析（四）——ROWID类型（一）： http://blog.itpub.net/post/468/11046

Oracle基本数据类型存储格式浅析（四）——ROWID类型（二）： http://blog.itpub.net/post/468/11363

Oracle基本数据类型存储格式浅析（五）——RAW类型： http://blog.itpub.net/post/468/11490

这个函数是由下面这个文章中的包进行修改的：

DBMS_REPAIR的使用（二）： http://blog.itpub.net/post/468/13241

来源：http://www.itpub.net/forum.php?mod=viewthread&action=printable&tid=308317