PL/SQL入门

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;