十进制转换二进制
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')
-------------------------
208
加我QQ(107644445)
