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

 

/*
  函数：f_36_tr_10，36进制转换为10进制
  作者：li0924
  时间：2013_07_02 
*/
create or replace function f_36_tr_10(v_36_data in varchar)
return number
is
v_data number(18);
begin
    select sum(data) into v_data from 
 (
    select (
    case substr(upper(v_36_data),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'
    when 'G' then '16'
    when 'H' then '17'
    when 'I' then '18'
    when 'J' then '19'
    when 'K' then '20'
    when 'L' then '21'
    when 'M' then '22'
    when 'N' then '23'
    when 'O' then '24'
    when 'P' then '25'
    when 'Q' then '26'
    when 'R' then '27'
    when 'S' then '28'
    when 'T' then '29'
    when 'U' then '30'
    when 'V' then '31'
    when 'W' then '32'
    when 'X' then '33'
    when 'Y' then '34'
    when 'Z' then '35'
    else substr(v_36_data,rownum,1) end
         )*power(36,length(v_36_data)-rownum) data
    from dual 
    connect by rownum<=length(v_36_data)     
    );
    return v_data;
exception
  when others then
  return null;
end;

 

/*
  函数：N10_TO_36，10进制转换为36进制
  作者：li0924
  时间：2015_04_02 
*/

CREATE OR REPLACE FUNCTION N10_TO_36(V_NUM NUMBER)
RETURN VARCHAR IS RESULT VARCHAR(8);
  NUM   NUMBER;
  TMP  NUMBER;
  TEMP  NUMBER;
BEGIN
NUM := V_NUM;
TMP := TRUNC(NUM / 36);
   WHILE NUM > 0 LOOP
    TEMP := MOD(NUM, 36);
    IF TEMP < 10 THEN
      RESULT := TEMP || RESULT;
    ELSE
      RESULT := CHR(TEMP + 55) || RESULT;
    END IF;
    NUM := TMP;
    TMP := TRUNC(NUM / 36);
  END LOOP;
return RESULT;
end;