oracle進位-10進位跟2進位互轉

標籤：

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER)RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列長度  V_N1  NUMBER;  V_N2  NUMBER;BEGINV_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 WHENV_N1 = 0;    END LOOP;--返回二進位長度 SELECT lpad(V_RTN,8,0)    INTO   V_RTN    FROM dual;return V_RTN;
END;

CREATE OR REPLACEFUNCTION BIT_TO_NUMBER(P_BIN INVARCHAR2) RETURNNUMBER 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!‘);  ENDIF;  IF LTRIM(P_BIN, ‘01‘) IS NOT NULL THEN    RAISE_APPLICATION_ERROR(-20002, ‘INPUT STR IS NOT VALID BIN VALUE!‘);  END IF;  FORI 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;

 

oracle進位-10進位跟2進位互轉