视图和递归查询

1、递归查询
SELECT * FROM tableName
START WITH BAE024 = '410222040505'
CONNECT BY PRIOR AAB301 = BAE024
union all
select '410422'
from dual

select aab301, aae044
from tableName
start with aab301 = '410222'
connect by prior aab301 = bae024

2、视图

create or replace view viewName as
select t.aab301, aae044, bae024, jb, replace(ls1,'-->419100','-->') ls1, ls2 ,
(case
when jb = '1' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 1) + 3,INSTR(ls1, '-->', 1, 2) - INSTR(ls1, '-->', 1, 1) - 3)
end) 省编码,
(case
when jb = '1' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 1) + 3,INSTR(ls2, '-->', 1, 2) - INSTR(ls2, '-->', 1, 1) - 3)
end) 省,
(case
when jb = '2' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 2) + 3,INSTR(ls1, '-->', 1, 3) - INSTR(ls1, '-->', 1, 2) - 3)
end) 市编码,
(case
when jb = '2' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 2) + 3,INSTR(ls2, '-->', 1, 3) - INSTR(ls2, '-->', 1, 2) - 3)
end) 市,
(case
when jb = '3' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 3) + 3,INSTR(ls1, '-->', 1, 4) - INSTR(ls1, '-->', 1, 3) - 3)
end) 县区编码,
(case
when jb = '3' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 3) + 3,INSTR(ls2, '-->', 1, 4) - INSTR(ls2, '-->', 1, 3) - 3)
end) 县区,
(case
when jb = '4' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 4) + 3,INSTR(ls1, '-->', 1, 5) - INSTR(ls1, '-->', 1, 4) - 3)
end) 乡镇编码,
(case
when jb = '4' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 4) + 3,INSTR(ls2, '-->', 1, 5) - INSTR(ls2, '-->', 1, 4) - 3)
end) 乡镇,
(case
when jb = '5' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 5) + 3,INSTR(ls1, '-->', 1, 6) - INSTR(ls1, '-->', 1, 5) - 3)
end) 村编码,
(case
when jb = '5' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 5) + 3,INSTR(ls2, '-->', 1, 6) - INSTR(ls2, '-->', 1, 5) - 3)
end) 村,
(case
when jb = '6' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 6) + 3,INSTR(ls1, '-->', 1, 7) - INSTR(ls1, '-->', 1, 6) - 3)
end) 组编码,
(case
when jb = '6' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 6) + 3,INSTR(ls2, '-->', 1, 7) - INSTR(ls2, '-->', 1, 6) - 3)
end) 组,
(case
when jb = '7' then
aab301
else
substr(ls1,INSTR(ls1, '-->', 1, 6) + 3,INSTR(ls1, '-->', 1, 7) - INSTR(ls1, '-->', 1, 6) - 3)
end) 小组编码,
(case
when jb = '7' then
aae044
else
substr(ls2,INSTR(ls2, '-->', 1, 6) + 3,INSTR(ls2, '-->', 1, 7) - INSTR(ls2, '-->', 1, 6) - 3)
end) 小组

from (select w.aab301,
w.aae044,
w.bae024,
w.aab021,
level jb,
sys_connect_by_path(w.aab301, '-->') ls1,
sys_connect_by_path(w.aae044, '-->') ls2
from (select aab301, bae024, aae044, aab021
from ab51
where aab301 like '41%'
and aab301 not in ('410181','410225','410482','410526','410728','411381','411481','411525','411628','411729')
union
select aab301, '419100' bae024, aae044, aab021
from ab51
where aab301 like '41%'
and aab301 in ('410181','410225','410482','410526','410728','411381','411481','411525','411628','411729')
union
select '419100' aab301, '410000' bae024, '' aae044, '' aab021
from ab51) w
start with w.aab301 = '410000'
connect by prior w.aab301 = w.bae024
order by level) t
where aab301<>'419100' /*and aab301 like '410421%'*/
;