Oracle 存储过程中的循环,条件判断
for...in...loop
end loop;循环
for x in 1..100 loop
deal with
end loop;
loop循环
loop
执行语句
exit when 条件
end loop
while循环
i:=1;
while i<99
Loop
begin
i:=i+1;
end;
end loop;
条件判断
1.if...elsif...else...end if
if conditions
then
consitions result;
elsif xxx and xxx
then
conditions result;
else
conditions result;
end if
2.case when end case判断
v_num:=1
case v_num
when 1 then
conditions reuslt;
when 2 then
conditions result;
else
null;
end case;
游标cursor
create or replace procedure pro_1(
checknum in number,
ref_cursor out sys_refcursor
)
as
begin
open ref_cursor for select × from.......
end pro_1;
sys_refcursor 的三个状态属性
%notfound
%found
%roucount 当前游标所指向的行位置
example1:通过游标返回一个结果集并将这个游标遍历输出
create or replace procedure pro_1(
checknum in number,
ref_cursor out sys_refcursor
)
as
begin
open ref_cursor for select × from.......;
loop
fetch ref_cursor into t_tmp;
exit when ref_cursor%notfound;
dbms_output.put_line(t_tmp.id);
update table....
where id=t_tmp.id;
commit;
end loop;
close ref_cursor;
end pro_1;
for...in...loop
end loop;循环
for x in 1..100 loop
deal with
end loop;
loop循环
loop
执行语句
exit when 条件
end loop
while循环
i:=1;
while i<99
Loop
begin
i:=i+1;
end;
end loop;
条件判断
1.if...elsif...else...end if
if conditions
then
consitions result;
elsif xxx and xxx
then
conditions result;
else
conditions result;
end if
2.case when end case判断
v_num:=1
case v_num
when 1 then
conditions reuslt;
when 2 then
conditions result;
else
null;
end case;
游标cursor
create or replace procedure pro_1(
checknum in number,
ref_cursor out sys_refcursor
)
as
begin
open ref_cursor for select × from.......
end pro_1;
sys_refcursor 的三个状态属性
%notfound
%found
%roucount 当前游标所指向的行位置
example1:通过游标返回一个结果集并将这个游标遍历输出
create or replace procedure pro_1(
checknum in number,
ref_cursor out sys_refcursor
)
as
begin
open ref_cursor for select × from.......;
loop
fetch ref_cursor into t_tmp;
exit when ref_cursor%notfound;
dbms_output.put_line(t_tmp.id);
update table....
where id=t_tmp.id;
commit;
end loop;
close ref_cursor;
end pro_1;