oracle进制转换
源代码
|
CREATE OR REPLACE PACKAGE Base_Num_Change IS --============================================= --1.0 16进制转10进制 FUNCTION Hex_To_Dec(p_Str VARCHAR2) RETURN NUMBER; --1.1 16进制转8进制 FUNCTION Hex_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2; --1.2 16进制转2进制 FUNCTION Hex_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2; --2.0 10进制转16进制 FUNCTION Dec_To_Hex(p_Dec NUMBER) RETURN VARCHAR2; --2.1 10进制转8进制 FUNCTION Dec_To_Oct(p_Dec NUMBER) RETURN VARCHAR2; --2.2 10进制转2进制 FUNCTION Dec_To_Bin(p_Dec NUMBER) RETURN VARCHAR2; --3.0 8进制转16进制 FUNCTION Oct_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2; --3.1 8进制转10进制 FUNCTION Oct_To_Dec(p_Str VARCHAR2) RETURN NUMBER; --3.2 8进制转2进制 FUNCTION Oct_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2; --4.0 2进制转16进制 FUNCTION Bin_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2; --4.1 2进制转10进制 FUNCTION Bin_To_Dec(p_Str VARCHAR2) RETURN NUMBER; --4.2 2进制转8进制 FUNCTION Bin_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2;
END Base_Num_Change; / CREATE OR REPLACE PACKAGE BODY Base_Num_Change IS --============================================= --1.0 16进制转10进制 FUNCTION Hex_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS p_Len NUMBER(9); p_Char VARCHAR2(1); p_Sub_Out NUMBER(3); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out NUMBER(18) := 0; p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '0123456789ABCDEF') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为2进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Length(p_Str); FOR i IN 1 .. p_Len LOOP p_Char := Substr(p_Sub_Str, 1, 1); SELECT Decode(p_Char, 'A', 10, 'B', 11, 'C', 12, 'D', 13, 'E', 14, 'F', 15, To_Number(p_Char)) INTO p_Sub_Out FROM Dual; p_Sub_Str := Substr(p_Sub_Str, 2); p_Out := p_Out * 16 + p_Sub_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Hex_To_Dec; --1.1 16进制转8进制 FUNCTION Hex_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN --得到基本数据 p_Out := Bin_To_Oct(Hex_To_Bin(p_Str)); -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Hex_To_Oct;
--1.2 16进制转2进制 FUNCTION Hex_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Len NUMBER(9); p_Char VARCHAR2(1); p_Sub_Out VARCHAR2(4); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '0123456789ABCDEF') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为2进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Length(p_Str); FOR i IN 1 .. p_Len LOOP p_Char := Substr(p_Sub_Str, 1, 1); SELECT Decode(p_Char, '0', '0000', '1', '0001', '2', '0010', '3', '0011', '4', '0100', '5', '0101', '6', '0110', '7', '0111', '8', '1000', '9', '1001', 'A', '1010', 'B', '1011', 'C', '1100', 'D', '1101', 'E', '1110', 'F', '1111', '2222') INTO p_Sub_Out FROM Dual; p_Sub_Str := Substr(p_Sub_Str, 2); p_Out := p_Out || p_Sub_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Hex_To_Bin;
--2.0 10进制转16进制 FUNCTION Dec_To_Hex(p_Dec NUMBER) RETURN VARCHAR2 AS p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN --得到基本数据 p_Out := Bin_To_Hex(Dec_To_Bin(p_Dec)); -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Dec_To_Hex; --2.1 10进制转8进制 FUNCTION Dec_To_Oct(p_Dec NUMBER) RETURN VARCHAR2 AS p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN --得到基本数据 p_Out := Bin_To_Oct(Dec_To_Bin(p_Dec)); -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Dec_To_Oct; --2.2 10进制转2进制 FUNCTION Dec_To_Bin(p_Dec NUMBER) RETURN VARCHAR2 AS p_Sub_Out VARCHAR2(4); p_Sub_Dec NUMBER(18) := p_Dec; p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Dec IS NULL THEN RETURN NULL; END IF; IF Trunc(p_Dec) <> p_Dec THEN p_Errstr := p_Dec || '不是整数，无法转换为2进制！'; RAISE p_Integrity; END IF;
LOOP p_Sub_Out := MOD(p_Sub_Dec, 2); p_Sub_Dec := Trunc(p_Sub_Dec / 2); p_Out := p_Sub_Out || p_Out; EXIT WHEN(p_Sub_Dec = 0); END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Dec_To_Bin; --3.0 8进制转16进制 FUNCTION Oct_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN --得到基本数据 p_Out := Bin_To_Hex(Oct_To_Bin(p_Str)); -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Oct_To_Hex; --3.1 8进制转10进制 FUNCTION Oct_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS p_Len NUMBER(9); p_Char VARCHAR2(1); p_Sub_Out NUMBER(3); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out NUMBER(18) := 0; p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '01234567') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为2进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Length(p_Str); FOR i IN 1 .. p_Len LOOP p_Char := Substr(p_Sub_Str, 1, 1); p_Sub_Out := To_Number(p_Char); p_Sub_Str := Substr(p_Sub_Str, 2); p_Out := p_Out * 8 + p_Sub_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Oct_To_Dec; --3.2 8进制转2进制 FUNCTION Oct_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Len NUMBER(9); p_Char VARCHAR2(1); p_Sub_Out VARCHAR2(4); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '01234567') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为2进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Length(p_Str); FOR i IN 1 .. p_Len LOOP p_Char := Substr(p_Sub_Str, 1, 1); SELECT Decode(p_Char, '0', '000', '1', '001', '2', '010', '3', '011', '4', '100', '5', '101', '6', '110', '7', '111', '222') INTO p_Sub_Out FROM Dual; p_Sub_Str := Substr(p_Sub_Str, 2); p_Out := p_Out || p_Sub_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Oct_To_Bin; --4.0 2进制转16进制 FUNCTION Bin_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Len NUMBER(9); p_Char VARCHAR2(4); p_Sub_Out VARCHAR2(4); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '01') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为16进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Ceil(Length(p_Str) / 4); FOR i IN 1 .. p_Len LOOP p_Char := Lpad(Substr(p_Sub_Str,(CASE WHEN Length(p_Str) + 1 - 4 * i > 1 THEN Length(p_Str) + 1 - 4 * i ELSE 1 END)), 4, '0'); SELECT Decode(p_Char, '0000', '0', '0001', '1', '0010', '2', '0011', '3', '0100', '4', '0101', '5', '0110', '6', '0111', '7', '1000', '8', '1001', '9', '1010', 'A', '1011', 'B', '1100', 'C', '1101', 'D', '1110', 'E', '1111', 'F', 'G') INTO p_Sub_Out FROM Dual; p_Sub_Str := Substr(p_Sub_Str, 1, Length(p_Sub_Str) - 4); p_Out := p_Sub_Out || p_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Bin_To_Hex; --4.1 2进制转10进制 FUNCTION Bin_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS p_Len NUMBER(9); p_Char VARCHAR2(1); p_Sub_Out NUMBER(3); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out NUMBER(18) := 0; p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '01') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为10进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Length(p_Str); FOR i IN 1 .. p_Len LOOP p_Char := Substr(p_Sub_Str, 1, 1); p_Sub_Out := To_Number(p_Char); p_Sub_Str := Substr(p_Sub_Str, 2); p_Out := p_Out * 2 + p_Sub_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Bin_To_Dec; --4.2 2进制转8进制 FUNCTION Bin_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2 AS p_Len NUMBER(9); p_Char VARCHAR2(3); p_Sub_Out VARCHAR2(4); p_Sub_Str VARCHAR2(200) := Upper(p_Str); p_Out VARCHAR2(2000); p_Integrity EXCEPTION; p_Errstr VARCHAR2(200); BEGIN IF p_Str IS NULL THEN RETURN NULL; END IF; IF Ltrim(p_Sub_Str, '01') IS NOT NULL THEN p_Errstr := p_Str || '包含非法字符，无法转换为8进制！'; RAISE p_Integrity; END IF; --得到基本数据 p_Len := Ceil(Length(p_Str) / 3); FOR i IN 1 .. p_Len LOOP p_Char := Lpad(Substr(p_Sub_Str,(CASE WHEN Length(p_Str) + 1 - 3 * i > 1 THEN Length(p_Str) + 1 - 3 * i ELSE 1 END)), 3, '0'); SELECT Decode(p_Char, '000', '0', '001', '1', '010', '2', '011', '3', '100', '4', '101', '5', '110', '6', '111', '7', '9') INTO p_Sub_Out FROM Dual; p_Sub_Str := Substr(p_Sub_Str, 1, Length(p_Sub_Str) - 3); p_Out := p_Sub_Out || p_Out; END LOOP; -- RETURN p_Out; EXCEPTION WHEN p_Integrity THEN Raise_Application_Error(-20001, p_Errstr); END Bin_To_Oct;
END Base_Num_Change;
/ |
16进制转10进制
|
SQL> select Base_Num_Change.Hex_To_Dec('ABC') from dual;
BASE_NUM_CHANGE.HEX_TO_DEC('ABC') --------------------------------- 2748 |
10进制转2进制
|
SQL> select Base_Num_Change.Dec_To_Bin(2748) from dual;
BASE_NUM_CHANGE.DEC_TO_BIN(2748) ------------------------------------------------------------------------------------------------- 101010111100 |
2进制转10进制
|
SQL> select Base_Num_Change.Bin_To_Dec(101010111100) from dual;
BASE_NUM_CHANGE.BIN_TO_DEC(101010111100) ---------------------------------------- 2748 |
2进制转16进制
|
SQL> select Base_Num_Change.Bin_To_Hex(101010111100) from dual;
BASE_NUM_CHANGE.BIN_TO_HEX(101010111100) ------------------------------------------------------------------------------------------------- ABC 其实，Oracle自带函数 to_number()，就可以搞定
SQL> select to_number('ABC','XXX') from dual;
TO_NUMBER('ABC','XXX') 2748 |
10进制转16进制
|
SQL> select Base_Num_Change.Dec_To_Hex(2748) from dual;
BASE_NUM_CHANGE.DEC_TO_HEX(2748) ------------------------------------------------------------------------------------------------- ABC
其实，Oracle自带to_char()函数
SQL> select to_char('2748','XXX') from dual;
TO_C ---- ABC |
16进制转2进制
|
SQL> select Base_Num_Change.Hex_To_Bin('ABC') from dual;
BASE_NUM_CHANGE.HEX_TO_BIN('ABC') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 101010111100 |
