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

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

[sql] view plain copy

1. SELECT BIN_TO_NUM (1, 1, 1, 1, 0, 1, 1) FROM DUAL;

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

[sql] view plain copy

1. CREATE OR REPLACE FUNCTION bin_to_num1 (p_binstr VARCHAR2) RETURN NUMBER
2. IS
3. l_len NUMBER := LENGTH (p_binstr);
4. r_num NUMBER := 0;
5. BEGIN
6. FOR i IN 1 .. l_len LOOP
7. IF SUBSTR (p_binstr, i, 1) = '1' THEN
8. r_num := r_num POWER (2, l_len - i);
9. END IF;
10. END LOOP;
12. RETURN r_num;
13. END bin_to_num1;
14. /

[sql] view plain copy

1. SELECT bin_to_num1 ('1111011') FROM DUAL;

[sql] view plain copy

1. CREATE OR REPLACE FUNCTION num_to_bin (p_num NUMBER) RETURN VARCHAR2
2. IS
3. r_binstr VARCHAR2 (32767);
4. l_num NUMBER := p_num;
5. BEGIN
6. WHILE l_num != 0 LOOP
7. r_binstr := TO_CHAR (MOD (l_num, 2)) || r_binstr;
8. l_num := TRUNC (l_num / 2);
9. END LOOP;
11. RETURN r_binstr;
12. END num_to_bin;
13. /

[sql] view plain copy

1. SELECT num_to_bin (123) FROM DUAL;