已知 15=2的3次方+2的2次方+2的1次方+2的0次方
7=2的2次方+2的1次方+2的0次方
17=2的4次方+2的0次方
16=2的4次方
要根据sql或者函数 查出 15的结果为“0,1,2,3”; 查7的结果为“0,1,2”求教 这个sql或者函数该如何写?
7=2的2次方+2的1次方+2的0次方
17=2的4次方+2的0次方
16=2的4次方
要根据sql或者函数 查出 15的结果为“0,1,2,3”; 查7的结果为“0,1,2”求教 这个sql或者函数该如何写?
调试欢乐多
是null,null,null,4吗?
还有如果是负数怎么显示?
--大概写个
create or replace function number_varchar2(n_num number) return varchar is
v_rtn varchar(2000);
v_n1 number;
v_n2 number;
v_sign char(2);
v_cnt number:=0;
begin
if n_num<0 then
v_sign:='负';
else
v_sign:='';
end if;
v_n1:= abs(n_num);
loop
v_n2 := mod(v_n1, 2);
v_n1 := trunc(v_n1 / 2);
if v_n2=1 then
v_rtn := v_cnt||','|| v_rtn;
else
v_rtn := 'null'||','|| v_rtn;
end if;
v_cnt:=v_cnt+1;
exit when v_n1 = 0;
end loop;
return v_sign||v_rtn;
exception
when others then
return(sqlerrm);
end;--测试函数的结果
scott@YPCOST> select number_varchar2(16) from dual;NUMBER_VARCHAR2(16)
----------------------------------------------------------
4,null,null,null,null,scott@YPCOST> select number_varchar2(15) from dual;NUMBER_VARCHAR2(15)
----------------------------------------------------------
3,2,1,0,scott@YPCOST> select number_varchar2(0) from dual;NUMBER_VARCHAR2(0)
----------------------------------------------------------
null,scott@YPCOST> select number_varchar2(-26) from dual;NUMBER_VARCHAR2(-26)
----------------------------------------------------------
负4,3,null,1,null,
DECLARE @Digit AS INT
SET @Digit = 69DECLARE @Return AS VARCHAR(4000)
SET @Return = ''DECLARE @tmpDigit AS INT
DECLARE @tmpPower AS INTSET @tmpDigit = ABS(@Digit)
SET @tmpPower = FLOOR(POWER(@tmpDigit, 1.0 / 2))WHILE (@tmpPower > -1) BEGIN
IF (@tmpDigit >= POWER(2, @tmpPower)) BEGIN
SET @Return = CONVERT(VARCHAR, @tmpPower) + ', ' + @Return
SET @tmpDigit = @tmpDigit - POWER(2, @tmpPower)
END SET @tmpPower = @tmpPower - 1
ENDSELECT @Return---------------------
0, 2, 6,