首页 » ORACLE 9i-23c » HEXTORAW、utl_raw.cast_to_raw、RAWTOHEX一点认识

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
打赏

对不起,这篇文章暂时关闭评论。