Oracle中编码与字符转换
⼀、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。也即是说他只对⼀个字符串中的第⼀个字符起作⽤，你弄多长的字符串放进去它也只认第⼀个字符。原创作品，允许转载，转载时请务必以超链接形式标明⽂章 、作者信息和本声明。否则将追究法律责任。
⼆、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)oracle 时间转换
----------
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
来源：www.jydba/oracle-dump%E5%87%BD%E6%95%B0%E7%9A%84%E4%B8%8Eutl_raw/
注：另外还可以使⽤vert_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码显⽰的字符以字符形式返回
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论