文章目录
Oracle循环分类:
1.循环关键字
1.1 循环关键字
- for
- loop
- while
1.2 退出循环关键字
- exit:跳出当前循环,是循环结束
- return:结束当前过程、函数等
- contince:结束本次循环,进入下一次循环
- goto:跳到指定goto位置
2.for循环
2.1 for游标循环
--for循环遍历select结果集
begin
for thisrow in (select id,
provider_id,
provider_code,
provider_name,
setup_code
from zc where id <10)
loop
dbms_output.put_line('id:'||thisrow.id || ' provider_id:'|| thisrow.provider_id);
end loop;
end;
结果:
id:1 provider_id:10295977
id:2 provider_id:10295978
id:3 provider_id:10295979
id:4 provider_id:10295980
id:5 provider_id:10295981
id:6 provider_id:10295982
id:7 provider_id:10295983
id:8 provider_id:10295984
id:9 provider_id:10295985
2.2 for普通循环
begin
for i in 1..10 loop
dbms_output.put_line('for:'|| i);
end loop;
end;
结果:
for:1
for:2
for:3
for:4
for:5
for:6
for:7
for:8
for:9
for:10
3. while循环
declare
x number := 0;
begin
while x<9 loop
dbms_output.put_line('while:'||x);
x := x + 1;
end loop;
end;
结果:
while:0
while:1
while:2
while:3
while:4
while:5
while:6
while:7
while:8
4. loop循环
4.1 exit when
declare
x number := 0;
begin
loop
x := x + 1;
exit when x > 5;
dbms_output.put_line('loop:' || x);
end loop;
end;
结果:
loop:1
loop:2
loop:3
loop:4
loop:5
4.2 if
declare x number := 0;
begin
loop
x := x + 1;
dbms_output.put_line('loop:' || x);
if x > 10 then
exit;
end if;
end loop;
end;
结果:
loop:1
loop:2
loop:3
loop:4
loop:5
loop:6
备注:
执行语句放在 exit when 之前和之后是有区别的。
执行语句放在 exit when之前,先执行语句后循环
执行语句放在 exit when之后,先循环后执行语句
5. goto循环
declare
x number := 0;
begin
<<flag>>
x := x + 1;
dbms_output.put_line('goto:' || x);
if x < 5 then
goto flag;
end if;
end;
结果:
goto:1
goto:2
goto:3
goto:4
goto:5
备注:goto语句使用时,注意循环点和变量递增的先后,避免进入死递归!