進制轉換完全版

以下內容包括：

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

鏈接:

http://********************/view ... acle%2B%BA%AF%CA%FD

總結

以上是生活随笔為你收集整理的oracle十六进制转数字,16进制如何转化为10进制?的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。