oracle进制--16进制与10进制互转

 

/*
  函数：f_16_tr_10，16进制转换为10进制
  作者：li0924
  时间：2013_07_19 
  16进制是0x开头的；
*/

create or replace function f_16_tr_10(v_16_data in varchar)
return number
is
v_data number(18);
v_char varchar2(18);
begin
    v_char:=substr(v_16_data,2);
    select sum(data) into v_data from 
 (
    select (
    case substr(upper(v_char),rownum,1)
    when 'A' then '10'
    when 'B' then '11'
    when 'C' then '12'
    when 'D' then '13'
    when 'E' then '14'
    when 'F' then '15'
    else substr(v_char,rownum,1) end
         )*power(16,length(v_char)-rownum) data
    from dual 
    connect by rownum<=length(v_char)     
    );
    return v_data;
exception
  when others then
  return null;
end;

 

/*
  函数：N10_TO_36，10进制转换为16进制
  作者：li0924
  时间：2015_07_02 
  16进制是0x开头的；
*/
CREATE OR REPLACE FUNCTION N10_TO_16(V_NUM NUMBER)
RETURN VARCHAR IS RESULT VARCHAR(8);
  NUM   NUMBER;
  TMP  NUMBER;
  TEMP  NUMBER;
BEGIN
NUM := V_NUM;
TMP := TRUNC(NUM / 16);
   WHILE NUM > 0 LOOP
    TEMP := MOD(NUM, 16);
    IF TEMP < 10 THEN
      RESULT := TEMP || RESULT;
    ELSE
      RESULT := CHR(TEMP + 55) || RESULT;
    END IF;
    NUM := TMP;
    TMP := TRUNC(NUM / 16);
  END LOOP;
return RESULT;
end;