PL/SQL语法
[Declare]
--declaration statements
begin
--executeable statemets
[Exception]
--excptioin statements
End
1、一个简单的例子
declare
pi constant number(9,7):=3.1415926; // pi numbe(9,7) defauult 3.1415926;
radius integer(5);
area number(14,2);
begin
radius:=3;
area:=pi*power(radius,2);
insert into Areas values(radius,area);
end;
本例是计算一个圆的面积并把计算结果存储到表Area中。
2、引用rad_cursor游标
declare
pi constant number(9,7) :=3.1415927;
area number(14,2);
cursor rad_cursor is select * from radius_vals;
rad_val rad_cursor%rowtype;
begin
openn rad_cursor;
fetch rad_cursor into rad_val;
area :=pi*power(rad_val.radius,2);
insert into Areas values(rad_val.radius,area);
close rad-cursor;
end;
3、条件逻辑
declare
pi constant number(9,7) :=3.1415927;
area number(14,2);
cursor rad_cursor is select * from radius_vals;
rad_val rad_cursor%rowtype;
begin
openn rad_cursor;
fetch rad_cursor into rad_val;
area :=pi*power(rad_val.radius,2);
if area>30
then
insert into Areas values(rad_val.radius,area);
end if;
close rad-cursor;
end;
4、循环
(1)、简单循环
declare
pi constant number(9,7):=3.1415927;
radius integer(5);
area number(14,2);
begin
radius :=3;
loop
area:=pi*power(radius,2)
insert into Areas vlues(radius,area);
radius:=radius+1;
Exit when area>100;
end loop;
end;
(2)简单的游标循环
游标有4个属性:%Found,%NotFound,%isOpen,%rowCount
declare
pi constant number(9,7):=3.1415927;
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 when rad_cursr%notFound;
area:=pi*power(rad_val.radius,2);
insert into areas values(rad_val.radius,area);
end loop;
close rad_cuursor;
end;
(3)FOR循环
declare
pi constant number(9,7):=3.1415927;
radius integer(5);
area number(14,2);
begin
for radius is 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.1415927;
area number(14,2);
corsor rad_cursor is select * from radius;
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.1415926;
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;
5、CASE语句
declare
pi constant number(9,7):=3.1415927;
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 when rad_cursor%NotFound;
area:=pi*power(radius,2);
case
when rad_val.radius=3;
then
insert into Areas values(rad_val.radius,area0;
when rad_val.radius=4;
then
insert into Areas values(rad_val.radius,area0;
when rad_val.radius=10
then
insert into Area values(0,0);
else raise case_not_found;
end loop;
close rad_cursor;
end;
6、异常处理
declare
pi constant number(9,7):=3.1415926;
radius integer(5);
area number(14,2);
some_variable number(14,2);
begin
radius:=3;
loop
some_variable:=1/(radius-4);
area:=pi*power(radius,2)
insert into Areas vlues(radius,area);
radius:=radius+1;
Exit when area>100;
end loop;
Exception
when zero_divide
then insert into Areas values(0,0);
end;