Oracle中的进制转换

文章作者 100test 发表时间 2007:03:14 13:44:41
来源 100Test.Com百考试题网

1.16进制转换为10进制

可以通过to_number函数实现

SQL> 0select to_number(19f,xxx) from dual.
TO_NUMBER(19F,XXX)
----------------------
415
SQL> 0select to_number(f,xx) from dual.
TO_NUMBER(F,XX)
-------------------
15

2.10进制转换为16进制

可以通过to_char函数转换

SQL> 0select to_char(123,xxx) from dual.
TO_C
----
7b
SQL> 0select to_char(4567,xxxx) from dual.
TO_CH
-----
11d7

3.2进制转换为10进制

从Oracle9i开始,提供函数bin_to_num进行2进制到10进制的转换

SQL> 0select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual.
A B
----- ----------
13 2
SQL> 0select bin_to_num(1,1,1,0,1) from dual.
BIN_TO_NUM(1,1,1,0,1)
---------------------
29

4.进制转换也可以通过自定义函数实现

以下函数来自AskTom网站,是Tom给出的例子,供参考:

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 ( 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
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.
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 ).
end to_hex.
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 ).
end to_bin.
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 ).
end to_oct.
/