3,491
社区成员
发帖
与我相关
我的任务
分享
--1.创建自定义函数
create or replace function number_to_bit(v_num number)
return varchar is v_rtn varchar(200);--注意返回列长度
v_n1 varchar(200);
v_n2 number;
begin
v_n1 := v_num;
loop
v_n2 := abs(mod(v_n1, 2));
v_n1 := to_char(abs(trunc(v_n1 / 2)));
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
--返回二进制长度
return v_rtn;
end number_to_bit;
--2.SQL实现
select number_to_bit(to_char(to_number('FFF7FFFFFFFFFFFFFFFFFFFFFFFFFFFF','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))) from dual;
select number_to_bit(to_charto_number('FFF7FFFFFFFFFFFFFFFFFFFFFFFFFFFF','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))) from dual;
--自定义十进制转二进制函数
create or replace function number_to_bit(v_num number)
return varchar is v_rtn varchar(8);--注意返回列长度
v_n1 number;
v_n2 number;
begin
v_n1 := v_num;
loop
v_n2 := mod(v_n1, 2);
v_n1 := abs(trunc(v_n1 / 2));
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
--返回二进制长度
select lpad(v_rtn,8,0) into v_rtn from dual;
return v_rtn;
end number_to_bit;
解决方法:
select number_to_bit(to_number('FFF7FFFFFFFFFFFFFFFFFFFFFFFFFFFF','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')) from dual;