在Oracle中实现数字进制转换完全版
以下内容包括:
1).提供两个进制转换包
2).提供进制转换的一些简便方法
3).提供进制转换的不同方法的性能评估
1.两个进制转换包
1)包PKG_DM_BASE_CONV(推荐)
CREATE OR REPLACE PACKAGE PKG_DM_BASE_CONV AS
FUNCTION hex_to_dec (hexnum IN char) RETURN NUMBER;
PRAGMA restrict_references (HEX_TO_DEC,WNDS);
FUNCTION dec_to_hex (N IN NUMBER) RETURN VARCHAR2;
PRAGMA restrict_references (DEC_TO_HEX,WNDS);
FUNCTION oct_to_dec (octin IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (OCT_TO_DEC,WNDS);
FUNCTION dec_to_oct (decin IN NUMBER) RETURN VARCHAR2;
PRAGMA restrict_references (DEC_TO_OCT,WNDS);
FUNCTION bin_to_dec (binin IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (BIN_TO_DEC,WNDS);
FUNCTION dec_to_bin (decin IN NUMBER) RETURN VARCHAR2;
PRAGMA restrict_references (DEC_TO_BIN,WNDS);
FUNCTION hex_to_bin (hexin IN VARCHAR2) RETURN NUMBER;
PRAGMA restrict_references (HEX_TO_BIN,WNDS);
FUNCTION bin_to_hex (binin IN NUMBER) RETURN VARCHAR2;
PRAGMA restrict_references (BIN_TO_HEX,WNDS);
FUNCTION oct_to_bin (octin IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (OCT_TO_BIN,WNDS);
FUNCTION bin_to_oct (binin IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (BIN_TO_OCT,WNDS);
FUNCTION oct_to_hex (octin IN NUMBER) RETURN VARCHAR2;
PRAGMA restrict_references (OCT_TO_HEX,WNDS);
FUNCTION hex_to_oct (hexin IN VARCHAR2) RETURN NUMBER;
PRAGMA restrict_references (HEX_TO_OCT,WNDS);
--十六进制字符转换成ASCII码字符
FUNCTION raw_to_char(v_raw LONG RAW) RETURN VARCHAR2;
PRAGMA restrict_references (raw_to_char,WNDS);
--ASCII码字符转换成十六进制字符
FUNCTION char_to_raw(v_char varchar2) RETURN LONG RAW;
PRAGMA restrict_references (char_to_raw,WNDS);
END PKG_DM_BASE_CONV;
/
CREATE OR REPLACE PACKAGE BODY PKG_DM_BASE_CONV AS
FUNCTION hex_to_dec (hexnum in char) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(hexnum);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(hexnum, i, 1);
IF current_digit IN ('A','B','C','D','E','F') THEN
current_digit_dec := ascii(current_digit) - ascii('A') + 10;
ELSE
current_digit_dec := to_number(current_digit);
END IF;
result := (result * 16) + current_digit_dec;
END LOOP;
RETURN result;
END hex_to_dec;
FUNCTION dec_to_hex (N IN NUMBER) RETURN VARCHAR2 IS
H VARCHAR2(64) :='';
N2 INTEGER := N;
BEGIN
LOOP
SELECT rawtohex(chr(N2))||H
INTO H
FROM dual;
N2 := trunc(N2 / 256);
EXIT WHEN N2=0;
END LOOP;
RETURN H;
END dec_to_hex;
FUNCTION oct_to_dec (octin IN NUMBER) RETURN NUMBER IS
v_charpos NUMBER;
v_charval CHAR(1);
v_return NUMBER DEFAULT 0;
v_power NUMBER DEFAULT 0;
v_string VARCHAR2(2000);
BEGIN
v_string := TO_CHAR(octin);
v_charpos := LENGTH(v_string);
WHILE v_charpos > 0 LOOP
v_charval := SUBSTR(v_string,v_charpos,1);
IF v_charval BETWEEN '0' AND '7' THEN
v_return := v_return + TO_NUMBER(v_charval) * POWER(8,v_power);
ELSE
raise_application_error(-20621,'Invalid input');
END IF;
v_charpos := v_charpos - 1;
v_power := v_power + 1;
END LOOP;
RETURN v_return;
END oct_to_dec;
FUNCTION dec_to_oct (decin IN NUMBER) RETURN VARCHAR2 IS
v_decin NUMBER;
v_next_digit NUMBER;
v_result varchar(2000);
BEGIN
v_decin := decin;
WHILE v_decin > 0 LOOP
v_next_digit := mod(v_decin,8);
v_result := to_char(v_next_digit) || v_result;
v_decin := floor(v_decin / 8);
END LOOP;
RETURN v_result;
END dec_to_oct;
FUNCTION bin_to_dec (binin IN NUMBER) RETURN NUMBER IS
v_charpos NUMBER;
v_charval CHAR(1);
v_return NUMBER DEFAULT 0;
v_power NUMBER DEFAULT 0;
v_string VARCHAR2(2000);
BEGIN
v_string := TO_CHAR(binin);
v_charpos := LENGTH(v_string);
WHILE v_charpos > 0 LOOP
v_charval := SUBSTR(v_string,v_charpos,1);
IF v_charval BETWEEN '0' AND '1' THEN
v_return := v_return + TO_NUMBER(v_charval) * POWER(2,v_power);
ELSE
raise_application_error(-20621,'Invalid input');
END IF;
v_charpos := v_charpos - 1;
v_power := v_power + 1;
END LOOP;
RETURN v_return;
END bin_to_dec;
FUNCTION dec_to_bin (decin IN NUMBER) RETURN VARCHAR2 IS
v_decin NUMBER;
v_next_digit NUMBER;
v_result varchar(2000);
BEGIN
v_decin := decin;
WHILE v_decin > 0 LOOP
v_next_digit := mod(v_decin,2);
v_result := to_char(v_next_digit) || v_result;
v_decin := floor(v_decin / 2);
END LOOP;
RETURN v_result;
END dec_to_bin;
FUNCTION hex_to_bin (hexin IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN dec_to_bin(hex_to_dec(hexin));
END hex_to_bin;
FUNCTION bin_to_hex (binin IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN dec_to_hex(bin_to_dec(binin));
END bin_to_hex;
FUNCTION oct_to_bin (octin IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN dec_to_bin(oct_to_dec(octin));
END oct_to_bin;
FUNCTION bin_to_oct (binin IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN dec_to_oct(bin_to_dec(binin));
END bin_to_oct;
FUNCTION oct_to_hex (octin IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN dec_to_hex(oct_to_dec(octin));
END oct_to_hex;
FUNCTION hex_to_oct (hexin IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN dec_to_oct(hex_to_dec(hexin));
END hex_to_oct;
FUNCTION raw_to_char(v_raw LONG RAW) RETURN VARCHAR2 IS
rawlen NUMBER;
hex VARCHAR2(32760);
rawparam VARCHAR2(32760);
i NUMBER;
BEGIN
hex := rawtohex(v_raw);
rawlen := length(hex);
i := 1;
WHILE i <= rawlen
LOOP
rawparam := rawparam||CHR(hex_to_dec(substrb(hex,i,2)));
i := i + 2;
END LOOP;
RETURN rawparam;
END raw_to_char;
FUNCTION char_to_raw(v_char varchar2) RETURN LONG RAW IS
rawdata LONG RAW;
rawlen NUMBER;
hex VARCHAR2(32760);
i NUMBER;
BEGIN
rawlen := length(v_char);
i := 1;
WHILE i <= rawlen
LOOP
hex := dec_to_hex(ascii(substrb(v_char,i,1)));
rawdata := rawdata || HEXTORAW(hex);
i := i + 1;
END LOOP;
RETURN rawdata;
END;
在Oracle中实现数字进制转换完全版
最新推荐文章于 2021-04-15 22:56:10 发布