进制转换是开发中经常需要用到的本文简单介绍几种常用的进制转化方法
一 进制转换为进制
可以通过to_number函数实现
SQL> select to_number(fxxx) from dual;
TO_NUMBER(FXXX)
SQL> select to_number(fxx) from dual;
TO_NUMBER(FXX)
二 进制转换为进制
可以通过to_char函数转换
SQL> select to_char(xxx) from dual;
TO_C
b
SQL> select to_char(xxxx) from dual;
TO_CH
d
三 进制转换为进制
从Oraclei开始提供函数bin_to_num进行进制到进制的转换
SQL> select bin_to_num() abin_to_num() b from dual;
A B
SQL> select bin_to_num() from dual;
BIN_TO_NUM()
四 通过自定义函数实现进制转换
以下函数来自AskTom网站是Tom给出的例子供参考:
create or replace function to_base( p_dec in number p_base in number )
return varchar
is
l_str varchar() default NULL;
l_num number default p_dec;
l_hex varchar() default ABCDEF;
begin
if ( trunc(p_dec) <> p_dec OR p_dec < ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex mod(l_nump_base)+ ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar
p_from_base in number default ) return number
is
l_num number default ;
l_hex varchar() default ABCDEF;
begin
for i in length(p_str) loop
l_num := l_num * p_from_base + instr(l_hexupper(substr(p_stri)));
end loop;
return l_num;
end to_dec;
/
show errors
create or replace function to_hex( p_dec in number ) return varchar
is
begin
return to_base( p_dec );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar
is
begin
return to_base( p_dec );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar
is
begin
return to_base( p_dec );
end to_oct;
/
