create function DecToHex(p_Dec number) is varchar2 begin return to_char(p_dec,'FMXXXX'); end; /create function HexToDec(p_Hex varchar2) is number begin return to_number(p_Hex,'XXXX'); exception when else .... end; /
问一问弱水三千,在什么地方可以找到你的这种写法呢? 我看了看ORACLE的一些教材,没有看到的, 唉,我还为此专门写过代码的. :( 弱水三千并不弱呀!create or replace function f_num_to_hex (v_num in number) return varchar2 is v_char char(2); v_hex varchar2(20) := ''; v_x number; Begin v_x := v_num; loop select decode(mod(v_x,16),0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6', 7,'7',8,'8',9,'9',10,'A',11,'B',12,'C',13,'D',14,'E',15,'F') into v_char from dual; v_x := (v_x - mod(v_x,16))/16; v_hex := v_char||v_hex; if v_x = 0 then exit; end if; end loop; return v_hex; end f_num_to_hex;CREATE OR REPLACE Function f_Hex_To_Num ( data in char) return number as n_len number; n_index number; n_result Number; n_char number; c_char char; Begin n_Result:=0; n_len:=Length(data); for n_index in 1..n_len loop c_char:=substr(data,n_index,1); select decode(c_char,'0',0,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6, '7',7,'8',8,'9',9,'A',10,'B',11,'C',12,'D',13,'E',14,'F',15) into n_char from dual; n_Result:=n_Result*16+n_char; end loop; return n_result; end;
oracle 参考或详细的oracle文档上都有啊
oracle 8i支持这种写法,以前的版本不支持,只好自己写函数
KingSunSha(弱水三千) 的第二种写法得的结果不对:select to_number(16,'xxxx') mum from dual;num ------------------------ 22 第一种是正确的。
select to_char(30,'XXXX') num from dual;num ----------------------- 1E
is varchar2
begin
return to_char(p_dec,'FMXXXX');
end;
/create function HexToDec(p_Hex varchar2)
is number
begin
return to_number(p_Hex,'XXXX');
exception when else
....
end;
/
我看了看ORACLE的一些教材,没有看到的,
唉,我还为此专门写过代码的. :(
弱水三千并不弱呀!create or replace function f_num_to_hex
(v_num in number) return varchar2 is
v_char char(2);
v_hex varchar2(20) := '';
v_x number;
Begin
v_x := v_num;
loop
select decode(mod(v_x,16),0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',
7,'7',8,'8',9,'9',10,'A',11,'B',12,'C',13,'D',14,'E',15,'F')
into v_char from dual;
v_x := (v_x - mod(v_x,16))/16;
v_hex := v_char||v_hex;
if v_x = 0 then
exit;
end if;
end loop;
return v_hex;
end f_num_to_hex;CREATE OR REPLACE Function f_Hex_To_Num
(
data in char)
return number
as
n_len number;
n_index number;
n_result Number;
n_char number;
c_char char;
Begin
n_Result:=0;
n_len:=Length(data);
for n_index in 1..n_len loop
c_char:=substr(data,n_index,1);
select decode(c_char,'0',0,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,
'7',7,'8',8,'9',9,'A',10,'B',11,'C',12,'D',13,'E',14,'F',15) into n_char from dual;
n_Result:=n_Result*16+n_char;
end loop;
return n_result;
end;
------------------------
22
第一种是正确的。
-----------------------
1E