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 '<' is true */
  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>