Oracle 进制转换

一.16进制转换为10进制to_number函数

-- 此处必须为大于或等于16进制字符数的xxxxxx,此处可以为>=6个x

select to_number('1195A5','xxxxxx') from dual

TO_NUMBER('1195A5','XXXXXX')
----------------------------
1152421

select to_number('f','x') from dual;

TO_NUMBER('F','X')
------------------
15

二.10进制转换为16进制to_char函数

--此处必须为大于或等于16进制字符数的xx,此处可以为>=2个x

select to_char(123,'xx'),length(to_char(123,'xx')) from dual; --转换为16进制后前面加一个空格,长度为3

TO_ LENGTH(TO_CHAR(123,'XX'))
--- -------------------------
7b 3

三.2进制转换为10进制

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

A B
----- ----------
13 2

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

BIN_TO_NUM(1,1,1,0,1)
---------------------
29

四.十进制转二进制

SQL> select number_2_bit(4) from dual;

NUMBER_2_BIT(4)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100

SQL> select number_2_bit(2) from dual;

NUMBER_2_BIT(2)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10

create or replace function number_2_bit(v_num number) return varchar is
v_rtn varchar(2000);
v_n1 number;
v_n2 number;
begin
v_n1 := v_num;
loop
v_n2 := mod(v_n1, 2);
v_n1 := trunc(v_n1 / 2);
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
return v_rtn;
end;