PLSQL_（3）程序控制结构

pl/sql语言是oracle对关系数据库语言sql的过程化扩充。
一、顺序结构

declare
 v_student students%rowType;
begin
 select * into v_student
   from students
  where student_id = 10213;
 dbms_output.put_line('姓名：'|| v_student.name);
 dbms_output.put_line('性别：'|| v_student.sex);
 dbms_output.put_line('专业：'|| v_student.specialty);
end;

 

二、分支结构
1.if语句
语法格式：

if condition_1 then
  statements_1;
  ...
 [elseif condition_n then
   statements_n;]
 [else
  else statements;]
 end if;
declare 
 v_id teachers.teacher_id%type;
 v_title teachers.title%type;
begin
 v_id := &teacher_id;
 select title into v_title
  from teachers
  where teacher_id = v_id;
 if v_title = '讲师' then
  update teachers
    set wage = 1.1 * wage
   where teacher_id = v_id;
 elsif v_title = '高工' or v_title = '副教授' then
  update teachers
    set wage = 1.05 * wage
   where teacher_id = v_id;
 else
  update teachers
    set wage = wage + 100
   where teacher_id = v_id;
 end if;
end;

 

 

 

2.case语句
使用if语句处理复杂的分支（多分支）操作，程序结构不够清晰，往往会给编制程序、调试程序
、阅读程序带来一定的困难。使用case语句可以使程序结构比较清晰。
（1）等值比较的case语句
语法格式：
 

case expression
  when result_1 then
   statements_1;
  when result_2 then
   statements_2;
  ...
  [else
   else_statements;]
 end case;

 

当使用case语句执行多重分支时，如果条件表达式完全相同，并且条件表达式为相等条件选择，
那么可以选择使用等值比较的case语句。

declare
  v_id teachers.teacher_id%type;
 v_title teachers.title%type;
begin
 v_id := &teacher_id;
 select title into v_title
   from teachers
  where teacher_id = v_id;
 case v_title
  when '教授' then
   update teachers
      set wage = 1.5 * wage
     where teacher_id = v_id;
  when '高工' then
   update teachers
      set wage = 1.1 * wage
     where teacher_id = v_id;
  else
   update teachers
      set wage = wage + 100
     where teacher_id = v_id;
 end case;
end;

 

（2）多种条件比较的case语句
语法格式：

case
  when expression1 then
   statements_1;
  when expression2 then
   statements_2;
  ...
  [else
   else_statements;]
 end case;

 

 

如果条件表达式不相同，或条件表达式不完全为相等条件选择，那么需要选择使用多种条件比较的case语句，相当于多个IF的语句。

declare
  v_id teachers.teacher_id%type;
 v_title teachers.title%type;
begin
 v_id := &teacher_id;
 select title into v_title
   from teachers
  where teacher_id = v_id;
 case 
  when v_title = '教授' then
   update teachers
      set wage = 1.5 * wage
     where teacher_id = v_id;
  when v_title =  '高工' or v_title =  '副教授' then
   update teachers
      set wage = 1.1 * wage
     where teacher_id = v_id;
  else
   update teachers
      set wage = wage + 100
     where teacher_id = v_id;
 end case;
end;

declare
  v_id teachers.teacher_id%type;
  v_bonus teachers.bonus%type;
  v_eage teachers.wage%type;
  v_income number(7,2);
begin
 v_id := &teacher_id;
 select bonus, wage into v_bonus, v_wage
  from teachers
  where teacher_id = v_id;
  v_income := v_bonus + v_wage;
  case
   when v_income <= 1000 then
    dbms_output.out_line('个人所得税：0');
   when v_income > 1000 and v_income < 3000 then
    dbms_output.out_line('个人所得税：'||v_income*0.03);
   when v_income >= 3000 then
    dbms_output.out_line('个人所得税：'||v_income*0.05);
  end case;
end;

 

 

 

三、循环结构
1.loop循环
语法格式：

loop
  statements(s);
  exit [when condition];
 end loop;
create table total(n int, result int);
declare
 v_i int := 1;
 v_sum int :=0;
begin
 loop
  v_sum := v_sum + v_i;
  insert into total values(v_i, v_sum);
  exit when v_i = 10;
 end loop;
end;

 

 

2.while循环
语法格式：

while condition loop
  statement(s);
 end loop;
declare 
 v_i int := 1;
 v_sum int := 0;
begin
 while v_i <= 10 loop
  v_sum := v_sum + v_i * v_i;
  insert into total values(v_i, v_sum);
  v_i := v_i + 1;
 end loop;

 

 

3.for循环
loop循环和while循环需要定义循环控制变量来控制循环次数；然而for循环不需要定义循环控制变量，
系统默认定义一个循环控制变量，以控制循环的次数。
语法格式：
 

for loop_index in [reverse] lowest_number ..highest_number loop
  statement(s);   --[reverse]表示循环控制变量从上限向下限递减1.
 end loop;
declare
 v_i int := 1;
 v_factorial int := 1;
begin
 for v_i in 1..10 loop
  v_factorial := v_factorial * v_i;
  insert into total values(v_i, v_factorial);
 end loop
end;

 

4.goto语句与Null语句
（1）goto语句
--goto语句用于改变Pl/sql程序的执行顺序。
语法格式：
 goto label_name; --pl/sql程序无条件地跳转到标号label_name处执行。
注意：标号后至少要有一条可执行语句；
   pl/sql块内部可以跳转，内层块可以跳到外层块，但外层块不能跳到内层块；
   不能从某一if语句外部跳到其内部；
   不能从某一循环体外跳到体内；

set serveroutput on
declare
 v_i int := 1;
 v_sum int := 0;
begin
 loop
  v_sum := v_sum + v_i;
  insert into total values(v_i, v_sum);
  if v_i = 10 then
   goto output;
  end if;
  v_i := v_i + 1;
 end loop;
 <<output>>
  dbms_output.put_line('v_sum = ' || v_sum);
end;

 

   不能从某一子程序外部跳到其内部。

（2）null语句
--null语句被称为空语句，它不执行任何操作便将程序控制交给下一条语句。
语法格式：
 null:
--使用null语句可以提高程序的可读性，一般在标号之后或在异常处理程序段中使用。

declare
 v_i int := 1;
 v_sum int := 0;
begin
 loop
  v_sum := v_sum + v_i;
  insert into total values(v_i, v_sum);
  if v_i = 10 then
   goto output;
  end if;
  v_i := v_i + 1;
 end loop;
 <<output>>
  null;
end;