天萃荷净
ORACLE 十进制与二进制互转函数,开发DBA向我咨询数值转换的方法,Oracle数值转换函数NUMBER_TO_BIT等函数可用
1.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
2.Oracle数值转换函数之二进制转换十进制
CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
V_SQL VARCHAR2(30000) := 'SELECT BIN_TO_N