日期:2014-05-17  浏览次数:21123 次

在oracle中,如何编写一个函数实现十进制和二进制的转换
在oracle中,如何编写一个函数实现十进制和二进制的转换
比如输入10,显示的结果是1010。

------解决方案--------------------
CREATE OR REPLACE FUNCTION decimal_to_binary (p_input INTEGER)
RETURN VARCHAR2 IS
TYPE typ_hex_list IS TABLE OF VARCHAR2 (4)
INDEX BY VARCHAR2 (1);

w_hex_list typ_hex_list;
w_len INTEGER;
w_hex_str VARCHAR2 (50);
w_output VARCHAR2 (200);
BEGIN
w_hex_list ('0') := '0000';
w_hex_list ('1') := '0001';
w_hex_list ('2') := '0010';
w_hex_list ('3') := '0011';
w_hex_list ('4') := '0100';
w_hex_list ('5') := '0101';
w_hex_list ('6') := '0110';
w_hex_list ('7') := '0111';
w_hex_list ('8') := '1000';
w_hex_list ('9') := '1001';
w_hex_list ('a') := '1010';
w_hex_list ('b') := '1011';
w_hex_list ('c') := '1100';
w_hex_list ('d') := '1101';
w_hex_list ('e') := '1110';
w_hex_list ('f') := '1111';

SELECT TO_CHAR (p_input, RPAD ('FM', 50, 'x'))
INTO w_hex_str
FROM DUAL;

w_len := LENGTH (w_hex_str);

FOR i IN 1 .. w_len LOOP
w_output := w_output || w_hex_list (SUBSTR (w_hex_str, i, 1));
END LOOP;

RETURN TO_NUMBER (w_output);
END decimal_to_binary;
/

------解决方案--------------------
alter proc prc_num @num bigint
as
declare @numstr varchar(50)
set @numstr=''
while (@num<>0)
begin
set @numstr=@numstr+convert(char(1),@num%2)
set @num=@num/2
end
select reverse(@numstr)


exec prc_num 8


百度上搜到的
------解决方案--------------------
2进制转换为10进制

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

SQL> 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