孩子找父亲 ，父亲找孩子的写法

---今天面试 一面试官说写出孩子找父亲，父亲找孩子的算法 我就试了下

 

 

 

1 孩子找父亲

 

scott@ORCL> select * from ms
  2  /

ASSEMBLY_ITEM_NO     COMPONENT_ITEM_NO      QUANTITY
-------------------- -------------------- ----------
A                    A1                            1
A                    A2                            1
A1                   A11                           1
A1                   A12                           1
A2                   A21                           1
A2                   A22                           1

已选择6行。

 

 

create or replace function fun_jd(v_COMPONENT_ITEM_NO ms.component_item_no%type) return varchar2
as
res varchar2(1000):='';
str1 varchar2(100);
str2 varchar2(100);
str3 varchar2(100);
n number:=0;
begin
str1:=v_COMPONENT_ITEM_NO;
str3:=str1;
loop
n:=n+1;----控制在12层
begin
select ASSEMBLY_ITEM_NO into str2 from ms where COMPONENT_ITEM_NO=str1;
res:=res||','||str2;
str1:=str2;
exit when n>12;
exception
when others then
null;
end;
exit when n>12;
end loop;
res:=str3||res;
return res;
end;

 

Function created
 
SQL> select fun_jd('A22') FROM DUAL
  2  /
 
FUN_JD('A22')
--------------------------------------------------------------------------------
A22,A2,A
 

 

 

2 父亲找孩子

scott@ORCL> create or replace function func_jd3(v_COMPONENT_ITEM_NO ms.component_item_no%type)
  2  return varchar2
  3  as
  4  res varchar2(1000);
  5  str1 varchar2(500);
  6  N NUMBER:=0;
  7  begin
  8  str1:=v_COMPONENT_ITEM_NO;
  9  WHILE N<=12 loop
 10  N:=N+1;
 11  BEGIN
 12  res:=res||','||str1;
 13  select wm_concat(a.component_item_no) INTO str1
 14  from ms a where instr(','||str1||',',','||ASSEMBLY_ITEM_NO||',')>0;
 15  exception
 16  when no_data_found then
 17  EXIT;
 18  END;
 19  end loop;
 20  return rtrim(ltrim(RES,','),',');
 21  end;
 22  /

函数已创建。

scott@ORCL> select replace(func_jd3('A1'),',',chr(10)) FROM DUAL
  2  /

REPLACE(FUNC_JD3('A1'),',',CHR(10))
--------------------------------------------------------------------------------
A1
A11
A12

 

 

---如有什么不很完美的地方，您有更合适的就请您提出谢谢