oracle中如何实现十进制和二进制的相互转换？

Question

oracle中如何实现十进制和二进制的相互转换？最好是sql语句

Answer 1

create or replace FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    v_return  VARCHAR2(4000);
  BEGIN
    SELECT SUM(data1) INTO v_return
      FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END f_bin_to_dec;
  
  
Create or replace  FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2 IS
    v_return VARCHAR2(4000);
    v_hex    VARCHAR2(4000);
  BEGIN
    SELECT pkg_number_trans.f_dec_to_hex(p_int) INTO v_hex FROM dual;
    SELECT pkg_number_trans.f_hex_to_bin(v_hex) INTO v_return FROM dual;
    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END f_dec_to_bin;

Answer 2

没有找到内部函数。  :(((CREATE OR REPLACE FUNCTION dec_to_bin(p_i INT) RETURN VARCHAR2 IS
  l_r VARCHAR2(2000);
BEGIN
SELECT ltrim(REPLACE(BIN, '/'),'0') INTO l_r
  FROM (SELECT SYS_CONNECT_BY_PATH(HEX, '/') BIN
          FROM (SELECT decode(UPPER(SUBSTR(ltrim(to_char(p_i,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')), ROWNUM, 1))
                         ,'0','0000'
                         ,'1','0001'
                         ,'2','0010'
                         ,'3','0011'
                         ,'4','0100'
                         ,'5','0101'
                         ,'6','0110'
                         ,'7','0111'
                         ,'8','1000'
                         ,'9','1001'
                         ,'A','1010'
                         ,'B','1011'
                         ,'C','1100'
                         ,'D' ,'1101'
                         ,'E','1110'
                         ,'F','1111') HEX,
                       ROW_NUMBER() OVER(ORDER BY NULL) RN1,
                       ROW_NUMBER() OVER(ORDER BY NULL) - 1 RN2
                  FROM DUAL
                CONNECT BY ROWNUM <= LENGTH(ltrim(to_char(p_i,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))))WHERE CONNECT_BY_ISLEAF=1
         START WITH RN1 = 1
        CONNECT BY PRIOR RN1 = RN2) ;
  RETURN l_r;
END;
/
SELECT dec_to_bin(15),dec_to_bin(1024) FROM dual;
输出:
1111 10000000000

Answer 3

从Oracle9i开始,提供函数bin_to_num进行2进制到10进制的转换 SQL> select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;A B
----- ----------
13 2SQL> select bin_to_num(1,1,1,0,1) from dual;BIN_TO_NUM(1,1,1,0,1)
---------------------
29

Answer 4

16进制转换为10进制可以通过to_number函数实现 SQL> select to_number('19f','xxx') from dual;TO_NUMBER('19F','XXX')
----------------------
415SQL> select to_number('f','xx') from dual;TO_NUMBER('F','XX')
-------------------
15