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;