HEXTORAW、utl_raw.cast_to_raw、RAWTOHEX一点认识

RAW 可变长度二进制类型，不受字符集的影响，目前已被LOB类型替代，结构非常像VARCHAR2，做为COLUMN 最大2000字节，做为变量32767个字节
HEXTORAW
把16进制转为RAW，该参数是把传入的字符串直接当16进制字符，如果不是有效字符会报错
utl_raw.cast_to_raw 则不同于上面，会转换为传入的字符串的每一个字符的ASCII码的16进制
RAWTOHEX
它是HEXTORAW的反向操作，把RAW转换为16进制，如果传入的是字符串，那返回的也是字符串的每个字符的ASCII码的16进制
下面看我几个例子就明白了
先看一下16进制和10进制的简单转换

SQL> select to_char('10','xxxxx') from dual;
TO_CHAR('10','XXXX
------------------
     a
SQL> select to_number('10','xxxxx') from dual;
TO_NUMBER('10','XXXXX')
-----------------------
                     16

SQL> drop table testraw purge;
Table dropped.
SQL> create table testraw (id int,value raw(10));
Table created.

SQL> select hextoraw('10') from dual;
HE
--
10
SQL> select hextoraw('1') from dual;
HE
--
01
SQL> select hextoraw('a') from dual;
HE
--
0A
SQL> select hextoraw('g') from dual;
select hextoraw('g') from dual
                *
ERROR at line 1:
ORA-01465: invalid hex number

SQL> insert into testraw values(1,hextoraw(1));
1 row created.
SQL> insert into testraw values(2,hextoraw('f'));
1 row created.
SQL> insert into testraw values(3,hextoraw('10'));
1 row created.
SQL> commit;
Commit complete.
SQL> select value, rawtohex(value) raw2hex,dump(value,16) dump16,to_number(value,'xxxxxx') hexto10 from testraw;
VALUE                          RAW2HEX    DUMP16          HEXTO10
------------------------------ ---------- -------------------- ----------
01                             01         Typ=23 Len=1: 1               1
0F                             0F         Typ=23 Len=1: f              15
10                             10         Typ=23 Len=1: 10             16

SQL> select rawtohex('01a') from dual;
RAWTOHEX('01A')
------------------
303161
SQL> select to_number(30,'xxxx') from dual;
TO_NUMBER(30,'XXXX')
--------------------
                  48
SQL> select ascii(0) from dual;
  ASCII(0)
----------
        48
SQL> select to_number('A','xxxx') from dual;
TO_NUMBER('A','XXXX')
---------------------
                   10

SQL> select to_number(61,'xxxx') from dual;
TO_NUMBER(61,'XXXX')
--------------------
                  97
SQL> select chr(97) from dual;
CHR
---
a
SQL> insert into testraw values(4,UTL_RAW.CAST_TO_RAW('01a'));
1 row created.
SQL> select * from testraw;
        ID VALUE
---------- ------------------------------
         1 01
         2 0F
         3 10
         4 303161
SQL> select value,dump(value,16) dump16 from testraw;
VALUE                          DUMP16
------------------------------ ------------------------------
01                             Typ=23 Len=1: 1
0F                             Typ=23 Len=1: f
10                             Typ=23 Len=1: 10
303161                         Typ=23 Len=3: 30,31,61