|
13#
12C环境下,先给出小例子的情况:
SQL> with
2 function similar(p_str1 in varchar2,p_str2 in varchar2)
3 return int
4 is
5 l_cnt int :=0;
6 begin
7 for i in 1..length(p_str1) loop
8 if substr(p_str1,i,1) = substr(p_str2,i,1) then
9 l_cnt := l_cnt + 1;
10 end if;
11 end loop;
12 if l_cnt >= length(p_str1) - 1 then
13 return 1;
14 else
15 return 0;
16 end if;
17 end;
18 function similar_multi(p1_str in varchar2,p2_str in varchar2)
19 return int
20 is
21 l_sum int default 0;
22 begin
23 for rec in (
24 select regexp_substr(p1_str,'[^,]+',1,level) item
25 from dual
26 connect by level<= regexp_count(p1_str,',',1)+1
27 ) loop
28
29 if similar(rec.item,p2_str) = 0 then
30 l_sum := l_sum +1;
31 end if;
32 end loop;
33 if l_sum = regexp_count(p1_str,',',1)+1 then
34 return 0;
35 else
36 return 1;
37 end if;
38 end;
39 t as (select chr(64+level) c from dual connect by level<=2),
40 s(lvl,v) as (select 1 lvl,c v
41 from t
42 union all
43 select lvl+1,
44 s.v||t.c
45 from s,t
46 where lvl<3),
47 s2 as (select v from s where lvl=3),
48 x(lvl,str) as (select 1 lvl,v str
49 from s2
50 where v='AAA' --路径很多,仅取以'AAA'开头的路径
51 union all
52 select x.lvl+1,
53 x.str||','||s2.v
54 from x,s2
55 where x.lvl<power(2,3)
56 and similar_multi(x.str,s2.v) = 0
57 )
58 select lvl,str from x where lvl=(select max(lvl) from x)
59
60 /
LVL STR
---------- --------------------------------------------------------------------------------
4 AAA,ABB,BAB,BBA
4 AAA,ABB,BBA,BAB
4 AAA,BAB,ABB,BBA
4 AAA,BAB,BBA,ABB
4 AAA,BBA,ABB,BAB
4 AAA,BBA,BAB,ABB
6 rows selected |
|