ORACLE 十进制与二进制互转函数

十进制转换二进制

代码如下

复制代码

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER)
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
V_N1 NUMBER;
V_N2 NUMBER;
BEGIN
V_N1 := V_NUM;
    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;
--返回二进制长度
SELECT lpad(V_RTN,8,0)
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;

SQL> select NUMBER_TO_BIT(208) from dual;

NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

代码如下

复制代码

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
V_RETURN NUMBER;
BEGIN
IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
END IF;
IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
END IF;
FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
END LOOP;
V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
RETURN V_RETURN;
END;

SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;

BIT_TO_NUMBER('11010000')

-------------------------

二进制转换八进制

代码如下

复制代码

FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_bin_to_oct
    -- 对象描述: 二进制转换八进制
    -- 输入参数: p_str 二进制字符串
    -- 返回结果: 八进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    v_bin := substr('00' || p_str, -3 * ceil(length(p_str) / 3));
    SELECT f_stragg(data1) INTO v_return
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
                     WHEN '000' THEN '0'
                     WHEN '001' THEN '1'
                     WHEN '010' THEN '2'
                     WHEN '011' THEN '3'
                     WHEN '100' THEN '4'
                     WHEN '101' THEN '5'
                     WHEN '110' THEN '6'
                     WHEN '111' THEN '7'
                   END) data1
              FROM dual
            CONNECT BY rownum <= length(v_bin) / 3);
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_bin_to_oct;

补充：数据库,Oracle教程