Oracle十进制转二进制

CREATE OR REPLACE EDITIONABLE FUNCTION NUM_TO_BIT (V_NUM NUMBER) RETURN VARCHAR IS
  V_RTN VARCHAR(2000);
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
  V_N1 := ABS(V_NUM);
  -- 如果为正数
  IF SIGN(V_NUM) > 0 THEN
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1  2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;


    -- 补全32位高位0
    SELECT lpad(V_RTN,32,0)
    INTO   V_RTN
    FROM dual;


  ELSE
    -- 转换为二进制同时按位取反
    LOOP
      V_N2 := MOD(V_N1, 2);
      IF V_N2 = 1 THEN
        V_N2 := 0;
      ELSIF V_N2 = 0 THEN
        V_N2 := 1;
      END IF;
      V_N1  := ABS(TRUNC(V_N1  2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;


    -- 补全32位高位1
    SELECT lpad(V_RTN,32,1)
    INTO   V_RTN
    FROM dual;


    -- 二进制转换为10机制，同时+1
    SELECT SUM(data1) + 1
    INTO V_N1
      FROM (SELECT substr(V_RTN, rownum, 1) * power(2, length(V_RTN) - rownum) data1
              FROM dual CONNECT BY rownum <= length(V_RTN));


    -- 转换为二进制
    LOOP
      V_N2 := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1  2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;


    -- 补全32位高位0
    SELECT lpad(V_RTN,32,0)
    INTO   V_RTN
    FROM dual;


  END IF;


  RETURN V_RTN;
END;