oracle bom层级sql,oracle bom_tree.sql

oracle bom_tree.sql

CREATE OR REPLACE FUNCTION BOM_TREE(vpart_no IN parts.part_no%TYPE,mm in varchar default '')

RETURN clob IS

str clob;

tmp clob;

/*

create by prey

vpart_no 传入的组件编码

mm 展开的层数,默认不输入 则展开至最底层

*/

cursor vbom is select a.*,level as le from

(select part_no,child_part,'('||assembly_qty||','||unit_qty||')' assemblyunit from bom ) a

start with a.part_no=vpart_no connect by prior a.child_part=a.part_no;

cursor vbom1 is select * from(select a.*,level as le from

(select part_no,child_part,'('||assembly_qty||','||unit_qty||')' assemblyunit from bom ) a

start with a.part_no=vpart_no connect by prior a.child_part=a.part_no) d

where d.le <=mm;

begin

str:='*';

tmp:=vpart_no;

dbms_lob.append(str,tmp);

--str:=vpart_no;

if mm is not null then

for dd in vbom1 loop

if (dd.child_part is not null) then

tmp:=chr(10);

dbms_lob.append(str,tmp);

tmp:=get_space(' ',dd.le)||dd.child_part||' '||dd.assemblyunit;

dbms_lob.append(str,tmp);

end if;

end loop;

else

for dd in vbom loop

if (dd.child_part is not null) then

tmp:=chr(10);

dbms_lob.append(str,tmp);

tmp:=get_space(' ',dd.le)||dd.child_part||' '||dd.assemblyunit;

dbms_lob.append(str,tmp);

end if;

end loop;

end if;

return str;

end;

CREATE OR REPLACE FUNCTION get_space(vspace in varchar2, vnum in number default 1 )

RETURN VARCHAR2 IS

sp varchar2(100);

begin

for i in 1..vnum loop

sp:=sp||vspace;

end loop;

return sp;

end;