对其它进制可以根据例子将power的底数改成相应的进制就可以了。 本文只是一个例子,大家可以把它封装成一个通用函数进行实用。 大家在试的时候将里面相应的其它进制的数值换成自己的数据就可以了(有多处)。 -----二进制转换十进制-----------------
select sum(data1)
from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1
from dual
connect by rownum <= length('1101')) -----八进制转换十进制-----------------
select sum(data1)
from (select substr('1101', rownum, 1) * power(8, length('1101') - rownum) data1
from dual
connect by rownum <= length('1101')) -----十六进制转换十进制-----------------
select sum(data1)
from (select (CASE upper(substr('2D', rownum, 1))
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
WHEN 'D' THEN '13'
WHEN 'E' THEN '14'
WHEN 'F' THEN '15'
ELSE substr('2D', rownum, 1)
END) * power(16, length('2D') - rownum) data1
from dual
connect by rownum <= length('2D')) http://develop.csai.cn/dbms/200701121134351943.htm
select sum(data1)
from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1
from dual
connect by rownum <= length('1101')) -----八进制转换十进制-----------------
select sum(data1)
from (select substr('1101', rownum, 1) * power(8, length('1101') - rownum) data1
from dual
connect by rownum <= length('1101')) -----十六进制转换十进制-----------------
select sum(data1)
from (select (CASE upper(substr('2D', rownum, 1))
WHEN 'A' THEN '10'
WHEN 'B' THEN '11'
WHEN 'C' THEN '12'
WHEN 'D' THEN '13'
WHEN 'E' THEN '14'
WHEN 'F' THEN '15'
ELSE substr('2D', rownum, 1)
END) * power(16, length('2D') - rownum) data1
from dual
connect by rownum <= length('2D')) http://develop.csai.cn/dbms/200701121134351943.htm
解决方案 »
- 关于组合主键合并成一列的问题
- sql语句求助!!!!
- 中国省市区,怎么在数据库中存储,求好的方案!
- 急!!!如何在Linux6.2下的Oracle8i自带的Pro*c/c++编译C程序?
- 字符串字段行转列(类交叉)CASE的应用问题
- 新手请教:如何进行数据库的连接???/
- 不使用游标在oracle9i中得到一个结果集
- 请问LOCK TABLE hatSTR_TEN IN ROW EXCLUSIVE MODE NOWAIT怎么样解除LOCK?
- Oracle有没有类似SQL那样的事件探查器工器啊?
- oracle 逐行计算(总价 = 单价*数量)
- 服务器字符集是WE8ISO8859P1,属于单字符集,一个汉字占两个字节。我的oracle版本是8.1.7或7.3.4。有没有一个能够完全转换WE8ISO8859P1的父
- 50分求解决oracle分页存储过程的错误!
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null )
then
return null;
end if;
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_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;
/
how to use them:
SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2 /
BIN HEX OCT
--------------- --------------- ---------------
1111011 7B 173
SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2 to_dec('173',8) base8
3 from dual
4 /
BASE2 BASE16 BASE8
---------- ---------- ----------
123 123 123
SQL> select to_char(10,'000x') from dual;TO_CHAR(10,'000X')
------------------
000a