oracle 转成十六进制,Oracle 数据库中 十六进制转换为number类型的函数

Oracle 数据库中 十六进制转换为number类型的函数

本文摘自：

Hexidecimal to Number Conversion. (文档 ID 119698.1)

函数文本如下：

create or replace function HEXTONUM(v_hex varchar2) return number

is

v_pos number := 0; -- The position counter (reverse)

v_len number := 0; -- The length of the number string

v_tot number := 0; -- The total to be returned

v_tempc char(1); -- Working char set

v_tempn number; -- Working number set

begin

/* Get the length of the hex string */

select vsize(v_hex) into v_len from dual;

/* Since we have a 1 based offset when only go while '

while ( v_pos < v_len )

loop

/* Extract the bytes from teh char from right to left (thus v_len-v_pos)

*/

v_tempc := upper( substrb(v_hex,v_len-v_pos,1) );

/* Get the numeric value for the current character */

if ( v_tempc >= '0' and v_tempc <= '9' ) then

v_tempn := to_number(v_tempc);

end if;

if v_tempc = 'A' then v_tempn := 10; end if;

if v_tempc = 'B' then v_tempn := 11; end if;

if v_tempc = 'C' then v_tempn := 12; end if;

if v_tempc = 'D' then v_tempn := 13; end if;

if v_tempc = 'E' then v_tempn := 14; end if;

if v_tempc = 'F' then v_tempn := 15; end if;

/* Times by the appropriate power of 16, and add to the total */

v_tempn := v_tempn * power(16,v_pos);

v_tot := v_tot + v_tempn;

/* Increment the counter and loop */

v_pos := v_pos + 1;

end loop;

return v_tot;

end;

/

本函数的测试如下：

SQL> set serveroutput on

declare

ret_val number;

begin

ret_val := hextonum('03360fac');

dbms_output.put_line(ret_val);

end;

/SQL> 2 3 4 5 6 7 8

53874604--->该数值就是十六进制转换为十进制之后的值

PL/SQL procedure successfully completed.

SQL>

或者这么写：

SQL> select hextonum('03360fac') from dual;

HEXTONUM('03360FAC')

--------------------

53874604

SQL> show user;

USER is "SCOTT"

SQL>