Oracle中主要有以下五种循环——Exit When、Loop、While、For(普通循环)、For(游标循环):
1、Exit When循环:
create or replace procedure proc_test_exit_when is
i number;
begin
i := 0;
LOOP
Exit When(i > 5);
Dbms_Output.put_line(i);
i := i + 1;
END LOOP;
end proc_test_exit_when;
2、Loop循环:
create or replace procedure proc_test_loop is
i number;
begin
i := 0;
loop
i := i + 1;
dbms_output.put_line(i);
if i > 5 then
exit;
end if;
end loop;
end proc_test_loop;
3、While循环:
create or replace procedure proc_test_while is
i number;
begin
i := 0;
while i < 5 loop
i := i + 1;
dbms_output.put_line(i);
end loop;
end proc_test_while;
4、For普通循环:
create or replace procedure proc_test_for is
i number;
begin
i:=0;
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end proc_test_for;
5、For游标循环:
create or replace procedure proc_test_cursor is
userRow test%rowtype;
cursor userRows is
select * from test;
begin
for userRow in userRows loop
dbms_output.put_line(userRow.id || ’,'||userRow.Name||’,' || userRows%rowcount);
end loop;
end proc_test_cursor;
⼀般循环语句有两种:
1)使⽤for循环实现
declare
cursor cur is
select * from tablename;
aw_row tablename%rowtype;
begin
for raw_row in cur
loop
dbms_output.put_line('test');
end loop;
end;
注意:for语句直接帮我们做了游标的打开关闭,以及判断⼯作;所以⽐较常⽤。
2)使⽤while实现:
declare
cursor cur is
select * from iss2_foc_response;
raw_row iss2_foc_response%rowtype;
begin
open cur;
FETCH cur
into raw_row;
while cur%found
loop
dbms_output.put_line('test while');
FETCH cur
into raw_row;
end loop;
close cur;
end;