• -------------------------------------------------------------
  • ====================================

Oracle中编码与字符转换

大数据库 dewbay 5年前 (2019-04-12) 1852次浏览 已收录 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


露水湾 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:Oracle中编码与字符转换
喜欢 (0)
[]
分享 (0)
关于作者:
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址