Oracle里二进制与十进制的相互转换

Oracle里有内建函数bin_to_num可以将二进制数转换为十进制数，但这个函数的入参是个数不定的0或1：

SELECTBIN_TO_NUM(1,1,1,1,0,1,1)FROMDUAL;

有时候入参使用一个二进制字符串会更加方便，另外Oracle本身没有提供num_to_bin这样将十进制转换成二进制的函数，于是写了两个自定义函数，算法很简单，不过这次追求的是用最少的代码实现。

CREATEORREPLACEFUNCTIONbin_to_num1(p_binstrVARCHAR2)RETURNNUMBER
IS
l_lenNUMBER:=LENGTH(p_binstr);
r_numNUMBER:=0;
BEGIN
FORiIN1..l_lenLOOP
IFSUBSTR(p_binstr,i,1)='1'THEN
r_num:=r_num+POWER(2,l_len-i);
ENDIF;
ENDLOOP;

RETURNr_num;
ENDbin_to_num1;
/

SELECTbin_to_num1('1111011')FROMDUAL;

CREATEORREPLACEFUNCTIONnum_to_bin(p_numNUMBER)RETURNVARCHAR2
IS
r_binstrVARCHAR2(32767);
l_numNUMBER:=p_num;
BEGIN
WHILEl_num!=0LOOP
r_binstr:=TO_CHAR(MOD(l_num,2))||r_binstr;
l_num:=TRUNC(l_num/2);
ENDLOOP;

RETURNr_binstr;
ENDnum_to_bin;
/

SELECTnum_to_bin(123)FROMDUAL;