Oracle十进制、二进制相互转换自定义函数

--十进制转二进制

create or replace function number_2_bit(v_num number) return varchar is
  v_rtn varchar(2000);
  v_n1 number;
  v_n2 number;
begin
  v_n1 := v_num;
  loop
    v_n2 := mod(v_n1, 2);
    v_n1 := trunc(v_n1 / 2);
    v_rtn := to_char(v_n2) || v_rtn;
    exit when v_n1 = 0;
  end loop;
  return v_rtn;
end;

--二进制转十进制

create or replace function bit_2_number(v_char varchar) return number is
  v_rtn number(10);
  v_n1 number;
  v_n2 number;
begin
  SELECT SUM(data1)
    into v_rtn
    FROM (SELECT substr(v_char, rownum, 1) *
                 power(2, length(v_char) - rownum) data1
            FROM dual
          CONNECT BY rownum <= length(v_char));
  return v_rtn;
end;


SQL> select bit_2_number('10001100000000011001111111111') from dual;
  BIT_2_NUMBER('1000110000000001

------------------------------

                     293614591

SQL> select number_2_bit(571231007) from dual;
 
NUMBER_2_BIT(571231007)
-----------------------------
100010000011000100101100011111