oracle casttoraw,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

打赏

微信扫一扫，打赏作者吧～