Oracle HowTo:Oracle中如何进行进制转换(转载)

SQL> select to_number('19f','xxx') from dual;

TO_NUMBER('19F','XXX')ITPUB个人空间'{ f/|o(q/b D2_
----------------------
,V.M$OrKC0415

SQL> select to_number('f','xx') from dual;

TO_NUMBER('F','XX')
@*z.f8|;t8r0-------------------
f\0yne"`K015

SQL> select to_char(123,'xxx') from dual;

TO_CITPUB个人空间N!B,`Gt5[U_
----
@m ^NC~AF07b

SQL> select to_char(4567,'xxxx') from dual;

TO_CHITPUB个人空间K!UczG
-----ITPUB个人空间 o,X0q1O'X
11d7

SQL> select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;

A BITPUB个人空间 nl4S,}/x
----- ----------ITPUB个人空间)s^?{]8m#Fn.^ ]m
13 2

SQL> select bin_to_num(1,1,1,0,1) from dual;

BIN_TO_NUM(1,1,1,0,1)ITPUB个人空间0~LAUR9]8A#vSj
---------------------
ykUS-T]/j/[ A029

create or replace function to_base( p_dec in number, p_base in number )ITPUB个人空间l6w]6P0c c-?w~
return varchar2
5iM5D Ei2B/|0isITPUB个人空间#V5|2n ?%`0@Y6uH6p
l_str varchar2(255) default NULL;ITPUB个人空间6pn(@.{E"h8C
l_num number default p_dec;ITPUB个人空间"MK;t3^;\E2c;x u
l_hex varchar2(16) default '0123456789ABCDEF';ITPUB个人空间)mZ|+yQ
beginITPUB个人空间0qV}r7NN9pf
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
'~"]+_s{,Je0raise PROGRAM_ERROR;
B~ug RC&PH:`7E:b0end if;
.z
d,|%vms/k0loopITPUB个人空间?(xM,zz*L*I
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
R0x0|}G8H0l_num := trunc( l_num/p_base );
['b)Fca(z`\T'Ml0exit when ( l_num = 0 );
?0rW!oS-l@!Cx0end loop;ITPUB个人空间xcmGb,Bx
return l_str;ITPUB个人空间Kg Q#G,EH
end to_base;ITPUB个人空间;VxG*}
b"_/j{
/

|S8m;rljr/q0create or replace function to_dec
}O;AxHL9A0( p_str in varchar2,ITPUB个人空间(b4i{t\!fNx!TG2Hd
p_from_base in number default 16 ) return numberITPUB个人空间 pO D,qH
isITPUB个人空间 C U?V3F:h|
l_num number default 0;
|'gQ,?,HTif\:~G0l_hex varchar2(16) default '0123456789ABCDEF';
5brO
RU7qa0beginITPUB个人空间 x'\A:SfOp
for i in 1 .. length(p_str) loop
,_.a{0B |;bo^0l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;ITPUB个人空间asl_(K_
end loop;ITPUB个人空间3K%bl&OW,n]1h
return l_num;
kY`K,Q
U9S0end to_dec;
D$Tj'j@||@
p0/ITPUB个人空间r:[s*U/^
show errors

create or replace function to_hex( p_dec in number ) return varchar2ITPUB个人空间$B$o/k+N)X [&x;c2n#A
isITPUB个人空间LuM%O5a(v q
beginITPUB个人空间k&?2S g/Db
return to_base( p_dec, 16 );
Sm rd5D H0end to_hex;
2N&E#o-M-L*U$Y-`kt~0/
e](uW,G*u0create or replace function to_bin( p_dec in number ) return varchar2ITPUB个人空间6qm lji k)a_)R
is

r&wf
ISb0kSF:Ufz3k0begin
,?1B/l*nXG~0return to_base( p_dec, 2 );ITPUB个人空间w:Sy$^sr
end to_bin;
#ZiRY&{&yP vz0/ITPUB个人空间V{?&Q~0wpq
create or replace function to_oct( p_dec in number ) return varchar2
;y-c(`p6c~CE.gNv0is
,r6M+cr]%a$a1z.l;^0beginITPUB个人空间 f#_%tr;vu0P
return to_base( p_dec, 8 );ITPUB个人空间6T`qK,pa,p
end to_oct;ITPUB个人空间g#QCD,Sae~
/