链接:http://www.xifenfei.com/2897.html
作者:惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
十进制转换二进制
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
|