mysql十进制转二进制函数_ORACLE 十进制与二进制互转函数 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429...

十进制转换二进制

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