孩子找父親 ，父親找孩子的寫法

---今天面試 一面試官說寫出孩子找父親，父親找孩子的算法 我就試了下

1 孩子找父親

[email protected]> 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 父親找孩子

[email protected]> 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  /

函數已建立。

[email protected]> select replace(func_jd3('A1'),',',chr(10)) FROM DUAL

  2  /

REPLACE(FUNC_JD3('A1'),',',CHR(10))

--------------------------------------------------------------------------------

A1

A11

A12

---如有什麼不很完美的地方，您有更合适的就請您提出謝謝