Oracle中二进制、八进制、十进制和十六进制相互转换函数

最近，工作中需要查看某些字段的二进制格式，索性写了一些进制相互转换的函数，以方便调用。

记录下这些代码，以备使用。

create or replace package pkg_digit_conv as

  --将字符串转换为其在数据库中的二进制存储格式
  function fun_str2bin(par_str in varchar2) return varchar2;

  --将二进制转换为10进制
  function fun_bin2dec(par_bin in varchar2) return varchar2;
  --将二进制转换为8进制
  function fun_bin2oct(par_bin in varchar2) return varchar2;
  --将二进制转换为16进制
  function fun_bin2hex(par_bin in varchar2) return varchar2;
  --将10进制转换为二进制
  function fun_dec2bin(par_dec in varchar2) return varchar2;
  --将8进制转换为二进制
  function fun_oct2bin(par_oct in varchar2) return varchar2;
  --将16进制转换为二进制
  function fun_hex2bin(par_hex in varchar2) return varchar2;

end;


create or replace package body pkg_digit_conv as 

  --将字符串转换为其在数据库中的二进制存储格式
  function fun_str2bin(par_str in varchar2) return varchar2
  as
  v_strlen pls_integer;
  v_detemp number;
  v_bintemp varchar2(20);
  v_retval varchar2(1000);
  begin
    select length(par_str) into v_strlen from dual;
    for i in 1 .. v_strlen loop
      select to_number(ascii(substr(par_str,i,1))) into v_detemp from dual;
      select fun_dec2bin(v_detemp) into v_bintemp from dual;
      v_retval :=nvl(v_retval,'')|| v_bintemp;
    end loop;
    return v_retval;
  end;

  --将二进制转换为10进制
  function fun_bin2dec(par_bin in varchar2) return varchar2
  as
  v_retval varchar2(100);
  begin
    select sum(d) into v_retval from (
      select substr(par_bin,rownum,1)*power(2,length(par_bin)-rownum) d from dual connect by rownum <= length(par_bin)
    );
    return v_retval;
  end;

  --将二进制转换为8进制
  function fun_bin2oct(par_bin in varchar2) return varchar2
  as 
  v_octlen pls_integer;
  v_octtemp varchar2(3);
  v_retemp varchar2(1);
  v_retval varchar2(1000);
  begin
    select ceil(length(par_bin)/3) into v_octlen from dual; 

    for i in 1..v_octlen loop
      if(3*i<=length(par_bin)) then
        select substr(par_bin,0-3*i,3) into v_octtemp from dual; 
      else 
        select substr(par_bin,0-length(par_bin),length(par_bin)-3*i+3) into v_octtemp from dual;
      end if;
      select fun_bin2dec(v_octtemp) into v_retemp from dual;
      v_retval := v_retemp||nvl(v_retval,'');
    end loop;
    return v_retval;
  end;

  --将二进制转换为16进制
  function fun_bin2hex(par_bin in varchar2) return varchar2
  as 
  v_hexlen pls_integer;
  v_hextemp varchar2(4);
  v_retemp varchar2(1);
  v_retval varchar2(1000);
  begin
  select ceil(length(par_bin)/4) into v_hexlen from dual; 

    for i in 1..v_hexlen loop
      if(4*i<=length(par_bin)) then
        select substr(par_bin,0-4*i,4) into v_hextemp from dual; 
      else 
        select substr(par_bin,0-length(par_bin),length(par_bin)-4*i+4) into v_hextemp from dual;
        select substr('000'||v_hextemp,-4,4) into v_hextemp from dual;
      end if;
      case v_hextemp
         WHEN '0000' THEN v_retemp :='0' ;
         WHEN '0001' THEN v_retemp :='1' ;
         WHEN '0010' THEN v_retemp :='2' ;
         WHEN '0011' THEN v_retemp :='3' ;
         WHEN '0100' THEN v_retemp :='4' ;
         WHEN '0101' THEN v_retemp :='5' ;
         WHEN '0110' THEN v_retemp :='6' ;
         WHEN '0111' THEN v_retemp :='7' ;
         WHEN '1000' THEN v_retemp :='8' ;
         WHEN '1001' THEN v_retemp :='9' ;
         WHEN '1010' THEN v_retemp :='A' ;
         WHEN '1011' THEN v_retemp :='B' ;
         WHEN '1100' THEN v_retemp :='C' ;
         WHEN '1101' THEN v_retemp :='D' ;
         WHEN '1110' THEN v_retemp :='E' ;
         else v_retemp :='F' ;
      end case;
      v_retval := v_retemp||nvl(v_retval,'');
    end loop;
    return v_retval;
  end;

  --将10进制转换为二进制
  function fun_dec2bin(par_dec in varchar2) return varchar2
  as
    yushu number;
    retemp varchar2(1);
    retval varchar2(1000);
  begin
    select to_number(par_dec) into yushu from dual;
    while yushu >0 loop
      select mod(yushu,2) into retemp from dual;
      retval := retemp || nvl(retval,'');
      select trunc(yushu/2) into yushu from dual;
    end loop;
    return retval;
  end;

  --将8进制转换为二进制
  function fun_oct2bin(par_oct in varchar2) return varchar2
  as 
  v_octlen pls_integer;
  v_octchar varchar2(1);
  v_dectemp number := 0;
  v_retval varchar2(1000);
  begin
    select length(par_oct) into v_octlen from dual;
    for i in 1..v_octlen loop
      v_dectemp := v_dectemp + to_number(substr(par_oct,i,1))*power(8,v_octlen-i);
    end loop;
    select fun_dec2bin(to_char(v_dectemp)) into v_retval from dual;
    return v_retval;
  end;

  --将16进制转换为二进制
  function fun_hex2bin(par_hex in varchar2) return varchar2
  as 
  v_hexlen pls_integer;
  v_dectemp number;
  begin
    select length(par_hex) into v_hexlen from dual;
    select to_number(par_hex,lpad('x',v_hexlen,'x')) into v_dectemp from dual;
    return fun_dec2bin(v_dectemp);
  end;

end;