PL/SQL 简介

PL/SQL 包含在称之为块的结构中；
如果要创建一个存储过程或程序包，应当给PL/SQL代码块起一个名字；

如果没有给PL/SQL代码块起名字，给代码块被称为匿名块。

1. 声明Declaration：定义并初始化块中使用的变量和游标
2. 可执行命令Executable Command：用流控制命令（如if命令和循环）执行命令并给声明的变量赋值
3. 异常处理Exception Handing：提供对错误情况的定制处理

典型的PL/SQL块结构如下所示：

declare
 <declaration section>
begin
 <executable command>
exception
 <exception handing>
end;

 

声明部分：

声明部分是P/SQL块的开始，声明部分以declare关键字开始，后面是变量和游标的定义列表。

用户可以定义具有常量值的变量，并且变量能够继承已存在的列和查询结构中的数据类型。

 

程序：

计算一个圆的面积，并将结果保存到一个名为AREAS的表中，AREAS表有两列，分别用于存储半径和面积。

计算圆面积的公式为半径的平方乘以常量π。

declare
 pi constant NUMBER(9,7) := 3.1415927;
 radius INTEGER(5);
 area NUMBER(14,2);
begin
 radius := 3;
 area := pi*power(radius, 2);
 insert into AREAS values (radius, area);
end;
/

/ - 用于执行PL/SQL块。

执行之后，将会得到Oracle的响应信息：

PL/SQL procedure successfully completed.

 

程序：

声明一个游标来检索RADIUS_VALS表中的记录。

RADIUS_VALS表只有一列-Radius列，它用来保存示例使用的半径。

该游标在声明部分声明，rad_val变量被声明为基于游标结果的数据类型。

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;
 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;
/

采用%ROWTYPE %TYPE定义数据类型的优点：可以使PL/SQL代码中数据类型的定义与基础数据结构无关。

 

可执行部分：

在关键字begin之后，PL/SQL块开始工作。

 

条件逻辑：

在PL/SQL中，可以使用if，else，elsif，continue和case命令来控制可执行命令部分的命令流。

if <some condition>
 then <some command>
elsif <some condition>
 then <some command>
else <some command>
end if;

if条件可以相互嵌套

if <some condition>
 then
  if <some condition>
   then <some command>
  end if;
else <some command>
end if;

通过if嵌套，可以快速开发出复杂的逻辑流程；

但是，不要使流程过于复杂，应当经常检查逻辑条件是否能够组合成更简单的形式。

 

程序：

如果圆面值大于，插入AREAS表。

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;
 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;
/

 

循环：

1. 简单循环 - 直到循环中遇到exit或exit或exit when语句时，才跳出循环
   
2. FOR循环 - 指定循环次数的循环
   
3. WHILE循环 - 在满足某个条件时循环

 

程序 - 简单循环：

在AREAS表中生成多行，此循环一loop关键字开始，exit when子句确定何时退出循环。

end loop子句标识循环的结束。

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 values (radius, area);
  radius := radius+1;
  exit when area > 100;
 end loop;
end;
/

程序 - 简单的游标循环：

可以使用游标的属性作为退出循环的条件；

1. %FOUND - 可以从游标中取出1条记录
   
2. %NOTFOUND - 不能从游标中取到记录
   
3. %ISOPEN - 游标已经打开
   
4. %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_cursor%NOTFOUND;
  area := pi*power(rad_val.radius, 2);
  insert into AREAS values (rad_val.radius, area);
 end loop;
 close rad_cursor;
end;
/

程序 - FOR循环：

在FOR循环中，循环执行指定次数。

FOR循环的开始由关键字for指定，后面是用来决定循环过程中何时完成以及循环何时退出的条件。

由于循环执行的次数在循环开始的时候设置，因此在循环中不需要exit命令。

declare
 pi constant NUMBER(9,7) := 3.1415927;
 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;
/

程序 - 游标FOR循环：

在游标FOR循环中，循环执行的次数由查询的结果动态决定。

在游标FOR循环中，打开游标，取出游标和关闭游标隐式完成，不需要显式指定这些操作。

declare
 pi constant NUMBER(9,7) := 3.1415927;
 area NUMBER(14,2);
 cursor rad_cursor is
  select * from RADIUS_VALS;
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;
/

请注意：rad_val没有在块中显式地声明。

程序 - WHILE循环：

知道满足退出条件，WHILE循环才会结束。

在循环中用while命令指定退出循环的条件。

delete from AREAS;

declare
 pi constant NUMBER(9,7) := 3.1415927;
 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;
/

使用CONTINUE 和 CONTINUE WHEN语句

可以在循环中使用continue语句退出循环的当前迭代，并将控制权转交给下一次迭代。

例如：如果通过一组计数器值进行迭代，就可以跳过指定的值而不完全退出循环。

declare
 pi constant NUMBER(9,7) := 3.1415927;
 radius INTEGER(5);
 area NUMBER(14,2);
begin
 radius := 1;
 loop
  if radius < 5 then
   continue;
  end if;

  area := pi*power(radius, 2);
  insert into AREAS values (radius, area);
  radius := radius + 1;
  exit when area > 100;
 end loop;
end;
/ 

也可以直接使用continue when radius < 5

 

CASE语句

可以使用case语句控制PL/SQL块中的分支逻辑。

例如，可以使用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(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;
 close rad_cursor;
end;
/

如果省略else关键字，则PL/SQL隐式增加以下else子句：

else raise CASE_NOT_FOUND;

case子句经常用于把一列值转换成相应的说明。

 

异常处理部分

在异常处理部分，用when子句判断出发哪个异常，即执行哪段代码。

如果在PL/SQL块的可执行部分触发一个异常，则命令流立即退出可以执行命令部分

并在异常处理部分中搜索与遇到的错误相匹配的异常。

 

PL/SQL提供了一组系统定义的异常，并允许用户自定义的异常。

异常部分总是以exception关键字开始，并放在终止PL/SQL块的可执行命令部分的end命令之前。

declare
 <declarations section>
begin
 <executable command>
exception
 <exception handing>
end;

PL/SQL 块中异常处理部分是可选的。

 

程序 - 异常处理：

当除以零的情况下，会有错误出现，需要exception处理。

declare
 pi constant NUMBER(9,7) := 3.1415927;
 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 values (radius, area);
  radius := radius+1;
  exit when area > 100;
 end loop;
exception
 when ZERO_DIVIDE
  then insert into AREAS values(0,0);
end;
/

可以使用when others子句处理在异常处理部分中未定义过的所有异常。

 

一旦遇到异常，就不能返回到可执行命令部分中正常的命令处理流了。

如果需要保持在可执行命令部分中，就应当在程序遇到可能的异常之前用if条件去测试它们。