Oracle 在PLSQL中将任意长度的数字从十进制转换为二进制到十进制

嗨，
我们在oracle数据库表列中的varchar2字段中存储100位长度编号。
在PLSQL代码中，我们需要将此100数字转换为其二进制等效数字 (300位)，将更多数字添加到二进制中，然后将其转换回十进制并再次存储在数据库中。

数字字段只有38位数字支持。我们如何在PLSQL中执行此转换？
是否有可重用的API或过程？
请推荐

谢谢，
Manoj

我们本来没有什么可以做的...

假设这里唯一的实际值是位 (不是十进制数)，也许另一个存储选项是原始的？然后，您可以使用UTL_RAW中的本机按位操作进行任何位操作。无论如何... 如果您做不到，那么我们需要建立自己的例程。我们可以使用嵌套表 (数组) 来存储数字，因此可以进行任意长度的算术运算

SQL> create or replace
  2  function dec_to_bit(p_decimal varchar2) return varchar2 is
  3
  4    l_num varchar2(1000) := p_decimal;
  5    l_res varchar2(1000);
  6    l_last_dig pls_integer;
  7
  8    function div2(p_dec varchar2) return varchar2 is
  9      type   integer_array is  table of integer;
 10      n1     integer_array := integer_array();
 11      res    integer_array := integer_array();
 12      carry  pls_integer := 0;
 13      tmp    pls_integer;
 14      str    varchar2(1000);
 15    begin
 16      for i in 1 .. length(p_dec) loop
 17        n1.extend;
 18        n1(i) := to_number(substr(p_dec,i,1));
 19      end loop;
 20      res.extend(n1.count);
 21
 22      for i in 1 .. n1.count
 23      loop
 24         str := str || trunc(((10*carry)+n1(i))/2);
 25         carry := mod(n1(i),2);
 26      end loop;
 27      return ltrim(str,'0');
 28    end;
 29  begin
 30    loop
 31       l_last_dig := to_number(substr(l_num,-1,1));
 32       l_res := to_char(mod(l_last_dig,2)) || l_res;
 33       l_num := div2(l_num);
 34       exit when l_num is null;
 35    end loop;
 36    return l_res;
 37  end;
 38  /

Function created.

SQL> select dec_to_bit(1232123123109837192837918273918273) from dual;

DEC_TO_BIT(1232123123109837192837918273918273)
----------------------------------------------------------------------------------------------------------------------------------
11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001

1 row selected.

SQL>

然后是另一个处理反向的例程。我已经把这个限制在2 ^ 512，所以512位是你的最大值。

SQL> create or replace
  2  function bit_to_dec(p_bitstr varchar2) return varchar2 is
  3    type integer_array is  table of number;
  4    type powers_of_two is table of integer_array;
  5    p powers_of_two := powers_of_two();
  6    cumtot integer_array := integer_array();
  7    l_str varchar2(512);
  8      flag boolean := false;
  9
 10    function str_as_array(p_str varchar2) return integer_array is
 11      l_ret integer_array := integer_array();
 12    begin
 13      l_ret.extend(length(p_str)+1);
 14      l_ret(1) := 0;
 15      for i in 2 ..  l_ret.count
 16      loop
 17        l_ret(i) := to_number(substr(p_str,i-1,1));
 18      end loop;
 19      return l_ret;
 20    end;
 21
 22    procedure double(a1 integer_array, r in out integer_array) is
 23      carry pls_integer := 0;
 24      tmp pls_integer;
 25    begin
 26      r := integer_array();
 27      r.extend(a1.count);
 28      for i in reverse 1 .. a1.count
 29      loop
 30          tmp :=  a1(i)+a1(i)+carry;
 31          if tmp > 9 then
 32             carry := 1;
 33             tmp := tmp-10;
 34          else
 35             carry := 0;
 36          end if;
 37          r(i) := tmp;
 38      end loop;
 39    end;
 40
 41    procedure inc(a1 integer_array, tot in out integer_array) is
 42      l_pad    integer_array := a1;
 43      carry    pls_integer := 0;
 44      tmp      pls_integer;
 45      last_dig pls_integer := 512;
 46    begin
 47      for i in reverse 1 .. a1.count
 48      loop
 49        tmp :=  a1(i)+tot(last_dig)+carry;
 50        if tmp > 9 then
 51           carry := 1;
 52           tmp := tmp-10;
 53        else
 54           carry := 0;
 55        end if;
 56        tot(last_dig) := tmp;
 57        last_dig := last_dig - 1;
 58      end loop;
 59      tot(last_dig) := carry;
 60    end;
 61
 62  begin
 63    p.extend(512);
 64
 65    for i in 1 .. 128
 66    loop
 67      p(i) := str_as_array(to_char(power(2,i-1)));
 68    end loop;
 69    for i in 128 .. 511
 70    loop
 71      double(p(i),p(i+1));
 72    end loop;
 73
 74    cumtot.extend(512);
 75    for i in 1 .. 512
 76    loop
 77      cumtot(i) := 0;
 78    end loop;
 79
 80    for i in 1 .. length(p_bitstr)
 81    loop
 82      if substr(p_bitstr,-i,1) = '1' then
 83        inc(p(i) , cumtot);
 84      end if;
 85    end loop;
 86
 87    for i in 1 .. cumtot.count
 88    loop
 89      if cumtot(i) > 0 or flag then
 90        l_str := l_str || cumtot(i);
 91        flag := true;
 92      end if;
 93    end loop;
 94
 95    return l_str;
 96  end;
 97  /

Function created.

SQL>
SQL> select bit_to_dec('11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001') from dual;

BIT_TO_DEC('11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001')
----------------------------------------------------------------------------------------------------------------------------------
1232123123109837192837918273918273

1 row selected.

SQL>

如果您打算在实际实现中使用这些功能，则将使用一个包并执行一次 (而不是每次) 2次初始化的功能。