但灵活性还是远远不如pl/sql,最近我在学习with递归语法是,发现使用with递归也可实现简单的嵌套循环功能;
例子:
--for i in 1..3 loop
-- for j in 1..3 loop
-- putline...;
-- end loop;
--end loop;
with t (v_str, i,j)
as
(
select '@' v_str, 1 i,1 j from dual
union all
select cast (
case when mod(t.i,2) = 0 then
t.v_str||'#'
else
t.v_str||'$'
end
as varchar2(1000)) v_str,
case when t.j=3 then
t.i+1
else
t.i
end i,
case when t.j=3 then
1
else
t.j+1
end j
from t
where t.i<=3
)
CYCLE i,j SET CYCLE_FLAG TO 'Y' DEFAULT 'N'
select * from t where t.i<=3
"V_STR" "I" "J" "CYCLE_FLAG"
"@" "1" "1" "N"
"@$" "1" "2" "N"
"@$$" "1" "3" "N"
"@$$$" "2" "1" "N"
"@$$$#" "2" "2" "N"
"@$$$##" "2" "3" "N"
"@$$$###" "3" "1" "N"
"@$$$###$" "3" "2" "N"
"@$$$###$$" "3" "3" "N"
也许你会觉得这个普通sql也能做到,但仔细想想看,这种方式的循环过程是可控的,某些条件下功能可媲美pl/sql 了;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28692050/viewspace-776309/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28692050/viewspace-776309/