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>