http://blog.csdn.net/yxc369/article/details/1470780
1,简单的循环
以loop关键字开始,exit when子句确定何时推出循环,end loop子句为循环结束标志。
declare
pi constant NUMBER(9,7) :=3.1415727
radius INTEGER(5);
area NUMBER(14,2);
begin
radius:=3;
loop
area:=pi*power(radius,2);
insert into AREAS values (radius,area);
radius:=radius+1;
exit when area >100；
end loop;
end;
2,简单的游标循环
%FOUND 可在游标中取一个记录
%NOTFOUND 不能从游标中再取一个记录
%ISOPEN 游标已经打开
%ROWCOUNT 迄今为止从游标中取出的行数
exit when rad_cursor%NOTFOUND;/*如果游标中取不到值则推出循环*/
3,FOR循环
for循环是指循环次数已经指定的循环
declare
pi constant NUMBER(9,7) :=3.1415727
radius INTEGER(5);
area NUMBER(14,2);
begin
for radius in 1..7 loop
area:=pi*power(radius,2);
insert into AREAS values (radius,area);
end loop;
end;
4,游标For循环
declare
pi constant NUMBER(9,7) :=3.1415727
area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VAS;
begin
for rad_val in rad_cursor
loop
area:=pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius,area);
end loop；
end;
5,WHILE循环
declare
pi constant NUMBER(9,7) :=3.1415727
radius INTEGER(5);
area NUMBER(14,2);
begin
radius:=3;
while radius<=7
loop
area:=pi*power(radius,2);
insert into AREAS values (radius,area);
radius:=radius+1;
end loop;
end;
---------------------------------------------------------------------------------------------------
CASE语句
declare
pi constant NUMBER(9,7) :=3.1415727
area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit wehn rad_cursor%NOTFOUND;
area:=pi*power(rad_val.radius,2);
case
when rad_val.Radius=3 then insert into AREAS values (rad_val.radius,area);
when rad_val.Radius=4 then insert into AREAS values (rad_val.radius,area);
when rad_val.Radius=10 then insert into AREAS values (0,0);
else raise CASE_NOT_FOUND;
end case;
end loop;
end;
---------------------------------------------------------------------------------------------------
exception异常:
exception
when ZERO_DIVIDE
then insert into AREAS values(0,0);
当PL/SQL块产生一个错误时,它会扫描定义的异常处理过程。 ZERO_DIVIDE是一个系统定义的异常。
可以用when other子句处理在异常处理部分未定义过的异常。