联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
十进制转换二进制
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