进制转换完全版
进制转换完全版
以下内容包括：
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;
END PKG_DM_BASE_CONV;
/
2）包bitwise
CREATE OR REPLACE PACKAGE bitwise IS
FUNCTION numtohexchar (pi_number IN NUMBER)
RETURN CHAR;
FUNCTION hexchartonum (pi_hexchar IN CHAR)
RETURN NUMBER;
FUNCTION hexconverter (pi_number IN NUMBER)
RETURN CHAR;
FUNCTION hexconverter (pi_hexstr IN CHAR)
RETURN NUMBER;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
END bitwise;
/
CREATE OR REPLACE PACKAGE BODY bitwise
IS
FUNCTION numtohexchar (pi_number IN NUMBER)
RETURN CHAR
IS
v_hextoreturn CHAR (1);
BEGIN
IF pi_number = 0 THEN
v_hextoreturn := '0';
ELSIF pi_number = 1 THEN
v_hextoreturn := '1';
ELSIF pi_number = 2 THEN
v_hextoreturn := '2';
ELSIF pi_number = 3 THEN
v_hextoreturn := '3';
ELSIF pi_number = 4 THEN
v_hextoreturn := '4';
ELSIF pi_number = 5 THEN
v_hextoreturn := '5';
ELSIF pi_number = 6 THEN
v_hextoreturn := '6';
ELSIF pi_number = 7 THEN
v_hextoreturn := '7';
ELSIF pi_number = 8 THEN
v_hextoreturn := '8';
ELSIF pi_number = 9 THEN
v_hextoreturn := '9';
ELSIF pi_number = 10 THEN
v_hextoreturn := 'A';
ELSIF pi_number = 11 THEN
v_hextoreturn := 'B';
ELSIF pi_number = 12 THEN
v_hextoreturn := 'C';
ELSIF pi_number = 13 THEN
v_hextoreturn := 'D';
ELSIF pi_number = 14 THEN
v_hextoreturn := 'E';
ELSIF pi_number = 15 THEN
v_hextoreturn := 'F';
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_hextoreturn;
END numtohexchar;
FUNCTION hexchartonum (pi_hexchar IN CHAR)
RETURN NUMBER
IS
v_numtoreturn NUMBER (2);
BEGIN
IF pi_hexchar = '0' THEN
v_numtoreturn := 0;
ELSIF pi_hexchar = '1' THEN
v_numtoreturn := 1;
ELSIF pi_hexchar = '2' THEN
v_numtoreturn := 2;
ELSIF pi_hexchar = '3' THEN
v_numtoreturn := 3;
ELSIF pi_hexchar = '4' THEN
v_numtoreturn := 4;
ELSIF pi_hexchar = '5' THEN
v_numtoreturn := 5;
ELSIF pi_hexchar = '6' THEN
v_numtoreturn := 6;
ELSIF pi_hexchar = '7' THEN
v_numtoreturn := 7;
ELSIF pi_hexchar = '8' THEN
v_numtoreturn := 8;
ELSIF pi_hexchar = '9' THEN
v_numtoreturn := 9;
ELSIF pi_hexchar = 'A' THEN
v_numtoreturn := 10;
ELSIF pi_hexchar = 'B' THEN
v_numtoreturn := 11;
ELSIF pi_hexchar = 'C' THEN
v_numtoreturn := 12;
ELSIF pi_hexchar = 'D' THEN
v_numtoreturn := 13;
ELSIF pi_hexchar = 'E' THEN
v_numtoreturn := 14;
ELSIF pi_hexchar = 'F' THEN
v_numtoreturn := 15;
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_numtoreturn;
END hexchartonum;
FUNCTION hexconverter (pi_number IN NUMBER)
RETURN CHAR
IS
i NUMBER;
v_digit NUMBER (2);
v_hexstr VARCHAR2 (16);
BEGIN
v_hexstr := '';
FOR i IN REVERSE 0 .. 15
LOOP
v_digit := MOD (TRUNC (pi_number / POWER (16, i)), 16);
v_hexstr := v_hexstr || numtohexchar (v_digit);
END LOOP;
RETURN v_hexstr;
END hexconverter;
FUNCTION hexconverter (pi_hexstr IN CHAR)
RETURN NUMBER
IS
i NUMBER;
v_digit NUMBER (2);
v_numtoreturn NUMBER;
v_hexstr16 CHAR (16);
BEGIN
v_hexstr16 := LPAD (pi_hexstr, 16, '0');
v_numtoreturn := 0;
FOR i IN 0 .. 16
LOOP
V_digit := hexchartonum (SUBSTR (v_hexstr16, i, 1));
v_numtoreturn := v_numtoreturn + v_digit * POWER (16, 16 - i);
END LOOP;
RETURN v_numtoreturn;
END hexconverter;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_and (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitand;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_or (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitor;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_xor (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitxor;
END bitwise;
2.实现进制转换的一些简便方法
1)
以上的pkg_dm_base_conv.hex_to_dec
bitwise.hexchartonum --该功能有一定的限制,不要采用这种方式
该功能可以用to_number实现
SQL> select to_number('41','xx') from dual;
TO_NUMBER('41','XX')
--------------------
65
SQL> select pkg_dm_base_conv.hex_to_dec('41') from dual;
PKG_DM_BASE_CONV.HEX_TO_DEC('41')
---------------------------------
65
SQL> Select Utl_Raw.cast_to_binary_integer('41') from dual;
UTL_RAW.CAST_TO_BINARY_INTEGER('41')
------------------------------------
65
SQL> select bitwise.hexchartonum('D') from dual;
BITWISE.HEXCHARTONUM('D')
-------------------------
13
2)提供进制转换的一些简便方法
以上的pkg_dm_base_conv.dec_to_hex
bitwise.numtohexchar
该功能可以用to_char实现
SQL> select pkg_dm_base_conv.dec_to_hex(65) from dual;
PKG_DM_BASE_CONV.DEC_TO_HEX(65)
-------------------------------------------------------------------
41
SQL> select to_char(65,'xx') from dual;
to_char(65,'xx')
---
41
SQL> select Utl_Raw.cast_from_binary_integer(65) from dual;
UTL_RAW.CAST_FROM_BINARY_INTEGER(65)
-------------------------------------------------------------
00000041
3.进制转换性能测试
整形转换成十六进制
提供主要的两种方式：
方法一：
select substr(translate(to_char(129497293,'xxxxxxxx'),' ','0'),2)
INTO v_hexchar from dual;
07b7f8cd
'xxxxxxxx'表示转换成4个字节的十六进制字符串
select HexToRaw(ltrim(rtrim(v_hexchar))) into v_hex from dual;
FFFFFFFD
优点：效率高，用时少，可支持UINT4（大于2147483647的整形）
缺点：程序代码不如第二种清晰
建议：对于INT大于两个字节的整形用此方法
方法二
select Utl_Raw.cast_from_binary_integer(2147483647) into v_hex from dual;
优点：程序代码清晰
缺点：执行效率不如第一种方式
建议：对于INT2或更小的整形可以采用此函数
十六进制转换成整形
提供主要的两种方式：
方法一
Select to_number('fffffffd','xxxxxxxx') from dual;
4294967293
建议：对于大于INT2的整形可以采用此函数
方法二
Select Utl_Raw.cast_to_binary_integer('7FFFFFFF') from dual;
建议：对于INT2或更小的整形可以采用此函数
字符串转换成十六进制
Select Utl_Raw.Cast_To_Raw('18') from dual；
3138
BCD码转换
Select HexToRaw('18') from dual;
18
十六进制转换成字符串
方法一（推荐）
Select Utl_Raw.Cast_To_Varchar2('3138') from dual
18
方法二
SELECT chr(to_number( '3138', 'xxxx' ) ) from dual;
程序另外提供一个包base_convert
hex_to_bin 提供十六进制转换成二进制
Select base_convert.hex_to_bin(12) From Dual
10010
另外提供十六进制与八进制的转换、八进制于十进制的转换、二进制与八进制的转换。
十六进制翻转
select utl_raw.reverse('123') from dual
2301
字符串翻转
select utl_raw.reverse('123') from dual
321
字符到二进制的原样转换(同BCD码)
方法一
select cast('12DDDDDDDDDDDDDDDDDD' as raw(10)) from dual;
12DDDDDDDDDDDDDDDDDD
方法二
r RAW（10） := '12DDDDDDDDDDDDDDDDDD';
12DDDDDDDDDDDDDDDDDD
注意不能有空格等非法字符
方法三
select HexToRaw('12DDDDDDDDDDDDDDDDDD') from dual;
推荐用用方法三
另外提供十六进制与八进制的转换、八进制于十进制的转换、二进制与八进制的转换。
对于Str类型的处理
HexToRaw(substr(translate(to_char(nvl(Lengthb(ltrim(rtrim(Str))), 0), 'xx'), ' ', '0'), 2) || ||
Utl_Raw.Cast_To_Raw(ltrim(rtrim(Str)))
)
xx表示一个字节的长度
对于UINT类型的处理
utl_raw.reverse(substr(translate(to_char(Uint, 'xxxx'), ' ', '0'), 2))
xxxx表示两个字节的长度
对于CHAR类型的处理
rpad(Utl_Raw.Cast_To_Raw(ltrim(rtrim(char))),Lengthb(char)*2,'00')
注：由于Oracle提供了大部分进制转换功能，所以从性能可读性均采用Oracle提供内置转换函数，而不采用JAVA等语言来写。
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
BIN_TO_NUM(1,0,1,0)
-------------------
10
其实进制转换想要自己写的话
参考我FAQ中的两个脚本
就可以完成十进制到任意进制的转换了
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
以下内容包括：
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;
END PKG_DM_BASE_CONV;
/
2）包bitwise
CREATE OR REPLACE PACKAGE bitwise IS
FUNCTION numtohexchar (pi_number IN NUMBER)
RETURN CHAR;
FUNCTION hexchartonum (pi_hexchar IN CHAR)
RETURN NUMBER;
FUNCTION hexconverter (pi_number IN NUMBER)
RETURN CHAR;
FUNCTION hexconverter (pi_hexstr IN CHAR)
RETURN NUMBER;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
END bitwise;
/
CREATE OR REPLACE PACKAGE BODY bitwise
IS
FUNCTION numtohexchar (pi_number IN NUMBER)
RETURN CHAR
IS
v_hextoreturn CHAR (1);
BEGIN
IF pi_number = 0 THEN
v_hextoreturn := '0';
ELSIF pi_number = 1 THEN
v_hextoreturn := '1';
ELSIF pi_number = 2 THEN
v_hextoreturn := '2';
ELSIF pi_number = 3 THEN
v_hextoreturn := '3';
ELSIF pi_number = 4 THEN
v_hextoreturn := '4';
ELSIF pi_number = 5 THEN
v_hextoreturn := '5';
ELSIF pi_number = 6 THEN
v_hextoreturn := '6';
ELSIF pi_number = 7 THEN
v_hextoreturn := '7';
ELSIF pi_number = 8 THEN
v_hextoreturn := '8';
ELSIF pi_number = 9 THEN
v_hextoreturn := '9';
ELSIF pi_number = 10 THEN
v_hextoreturn := 'A';
ELSIF pi_number = 11 THEN
v_hextoreturn := 'B';
ELSIF pi_number = 12 THEN
v_hextoreturn := 'C';
ELSIF pi_number = 13 THEN
v_hextoreturn := 'D';
ELSIF pi_number = 14 THEN
v_hextoreturn := 'E';
ELSIF pi_number = 15 THEN
v_hextoreturn := 'F';
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_hextoreturn;
END numtohexchar;
FUNCTION hexchartonum (pi_hexchar IN CHAR)
RETURN NUMBER
IS
v_numtoreturn NUMBER (2);
BEGIN
IF pi_hexchar = '0' THEN
v_numtoreturn := 0;
ELSIF pi_hexchar = '1' THEN
v_numtoreturn := 1;
ELSIF pi_hexchar = '2' THEN
v_numtoreturn := 2;
ELSIF pi_hexchar = '3' THEN
v_numtoreturn := 3;
ELSIF pi_hexchar = '4' THEN
v_numtoreturn := 4;
ELSIF pi_hexchar = '5' THEN
v_numtoreturn := 5;
ELSIF pi_hexchar = '6' THEN
v_numtoreturn := 6;
ELSIF pi_hexchar = '7' THEN
v_numtoreturn := 7;
ELSIF pi_hexchar = '8' THEN
v_numtoreturn := 8;
ELSIF pi_hexchar = '9' THEN
v_numtoreturn := 9;
ELSIF pi_hexchar = 'A' THEN
v_numtoreturn := 10;
ELSIF pi_hexchar = 'B' THEN
v_numtoreturn := 11;
ELSIF pi_hexchar = 'C' THEN
v_numtoreturn := 12;
ELSIF pi_hexchar = 'D' THEN
v_numtoreturn := 13;
ELSIF pi_hexchar = 'E' THEN
v_numtoreturn := 14;
ELSIF pi_hexchar = 'F' THEN
v_numtoreturn := 15;
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_numtoreturn;
END hexchartonum;
FUNCTION hexconverter (pi_number IN NUMBER)
RETURN CHAR
IS
i NUMBER;
v_digit NUMBER (2);
v_hexstr VARCHAR2 (16);
BEGIN
v_hexstr := '';
FOR i IN REVERSE 0 .. 15
LOOP
v_digit := MOD (TRUNC (pi_number / POWER (16, i)), 16);
v_hexstr := v_hexstr || numtohexchar (v_digit);
END LOOP;
RETURN v_hexstr;
END hexconverter;
FUNCTION hexconverter (pi_hexstr IN CHAR)
RETURN NUMBER
IS
i NUMBER;
v_digit NUMBER (2);
v_numtoreturn NUMBER;
v_hexstr16 CHAR (16);
BEGIN
v_hexstr16 := LPAD (pi_hexstr, 16, '0');
v_numtoreturn := 0;
FOR i IN 0 .. 16
LOOP
V_digit := hexchartonum (SUBSTR (v_hexstr16, i, 1));
v_numtoreturn := v_numtoreturn + v_digit * POWER (16, 16 - i);
END LOOP;
RETURN v_numtoreturn;
END hexconverter;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_and (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitand;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_or (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitor;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_xor (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitxor;
END bitwise;
2.实现进制转换的一些简便方法
1)
以上的pkg_dm_base_conv.hex_to_dec
bitwise.hexchartonum --该功能有一定的限制,不要采用这种方式
该功能可以用to_number实现
SQL> select to_number('41','xx') from dual;
TO_NUMBER('41','XX')
--------------------
65
SQL> select pkg_dm_base_conv.hex_to_dec('41') from dual;
PKG_DM_BASE_CONV.HEX_TO_DEC('41')
---------------------------------
65
SQL> Select Utl_Raw.cast_to_binary_integer('41') from dual;
UTL_RAW.CAST_TO_BINARY_INTEGER('41')
------------------------------------
65
SQL> select bitwise.hexchartonum('D') from dual;
BITWISE.HEXCHARTONUM('D')
-------------------------
13
2)提供进制转换的一些简便方法
以上的pkg_dm_base_conv.dec_to_hex
bitwise.numtohexchar
该功能可以用to_char实现
SQL> select pkg_dm_base_conv.dec_to_hex(65) from dual;
PKG_DM_BASE_CONV.DEC_TO_HEX(65)
-------------------------------------------------------------------
41
SQL> select to_char(65,'xx') from dual;
to_char(65,'xx')
---
41
SQL> select Utl_Raw.cast_from_binary_integer(65) from dual;
UTL_RAW.CAST_FROM_BINARY_INTEGER(65)
-------------------------------------------------------------
00000041
3.进制转换性能测试
整形转换成十六进制
提供主要的两种方式：
方法一：
select substr(translate(to_char(129497293,'xxxxxxxx'),' ','0'),2)
INTO v_hexchar from dual;
07b7f8cd
'xxxxxxxx'表示转换成4个字节的十六进制字符串
select HexToRaw(ltrim(rtrim(v_hexchar))) into v_hex from dual;
FFFFFFFD
优点：效率高，用时少，可支持UINT4（大于2147483647的整形）
缺点：程序代码不如第二种清晰
建议：对于INT大于两个字节的整形用此方法
方法二
select Utl_Raw.cast_from_binary_integer(2147483647) into v_hex from dual;
优点：程序代码清晰
缺点：执行效率不如第一种方式
建议：对于INT2或更小的整形可以采用此函数
十六进制转换成整形
提供主要的两种方式：
方法一
Select to_number('fffffffd','xxxxxxxx') from dual;
4294967293
建议：对于大于INT2的整形可以采用此函数
方法二
Select Utl_Raw.cast_to_binary_integer('7FFFFFFF') from dual;
建议：对于INT2或更小的整形可以采用此函数
字符串转换成十六进制
Select Utl_Raw.Cast_To_Raw('18') from dual；
3138
BCD码转换
Select HexToRaw('18') from dual;
18
十六进制转换成字符串
方法一（推荐）
Select Utl_Raw.Cast_To_Varchar2('3138') from dual
18
方法二
SELECT chr(to_number( '3138', 'xxxx' ) ) from dual;
程序另外提供一个包base_convert
hex_to_bin 提供十六进制转换成二进制
Select base_convert.hex_to_bin(12) From Dual
10010
另外提供十六进制与八进制的转换、八进制于十进制的转换、二进制与八进制的转换。
十六进制翻转
select utl_raw.reverse('123') from dual
2301
字符串翻转
select utl_raw.reverse('123') from dual
321
字符到二进制的原样转换(同BCD码)
方法一
select cast('12DDDDDDDDDDDDDDDDDD' as raw(10)) from dual;
12DDDDDDDDDDDDDDDDDD
方法二
r RAW（10） := '12DDDDDDDDDDDDDDDDDD';
12DDDDDDDDDDDDDDDDDD
注意不能有空格等非法字符
方法三
select HexToRaw('12DDDDDDDDDDDDDDDDDD') from dual;
推荐用用方法三
另外提供十六进制与八进制的转换、八进制于十进制的转换、二进制与八进制的转换。
对于Str类型的处理
HexToRaw(substr(translate(to_char(nvl(Lengthb(ltrim(rtrim(Str))), 0), 'xx'), ' ', '0'), 2) || ||
Utl_Raw.Cast_To_Raw(ltrim(rtrim(Str)))
)
xx表示一个字节的长度
对于UINT类型的处理
utl_raw.reverse(substr(translate(to_char(Uint, 'xxxx'), ' ', '0'), 2))
xxxx表示两个字节的长度
对于CHAR类型的处理
rpad(Utl_Raw.Cast_To_Raw(ltrim(rtrim(char))),Lengthb(char)*2,'00')
注：由于Oracle提供了大部分进制转换功能，所以从性能可读性均采用Oracle提供内置转换函数，而不采用JAVA等语言来写。
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
BIN_TO_NUM(1,0,1,0)
-------------------
10
其实进制转换想要自己写的话
参考我FAQ中的两个脚本
就可以完成十进制到任意进制的转换了
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
