|
select parent,child,level1,sys_connect_by_path(childqty/parentqty,'*') rn,
strtonumber(1,substr(trim(sys_connect_by_path(childqty/parentqty,'*')),2))
from tt start with (level1=1) CONNECT BY PRIOR child=parent
strtonumber写的比较差,懒得完善了
create or replace function strtonumber(preadd number,str in varchar2 ) return number
as
n1 number;
n2 number;
strleft varchar2(100);
midadd number;
begin
if str is null or str='' then
return preadd ;
end if;
select decode(substr(str,1,instr(str,'*')-1),null,0,substr(str,1,instr(str,'*')-1)) into n1 from dual;
if n1=0 then
select preadd*str into n1 from dual;
return n1;
end if;
select substr(str,instr(str,'*')+1,decode(sign(instr(str,'*',1,2)-1),1,instr(str,'*',1,2)-instr(str,'*')-1,length(str)-instr(str,'*'))) into n2 from dual;
midadd:=preadd*n1*n2;
if(instr(str,'*',1,2)>1) then
strleft:=substr(str,instr(str,'*',1,2)+1,length(str)-instr(str,'*',1,2));
return strtonumber(midadd,strleft);
else
return midadd;
end if;
end; |
|