Oracle数据库的基本操作（五）

一、流程控制语句

SQL Server

  begin…and
 
  if…else
  
  while

  case

Oracle

  if .. then .. end if;

  if .. then .. else … end if;
   
  if .. then .. elsif .. then .. else .. end if;
 
  loop .. exit .. end loop;
  
  loop .. exit when .. end loop;
  
  while .. loop .. end loop;

  case

例1 if … then … else … end if;语句

  declare
    n1 number := 90;
    n2 number := 60;
  begin
    if n1 >= n2 then
      dbms_output.put_line('n1 >= n2');
    else
      dbms_output.put_line('n1 < n2');
    end if;
  end;

• 若将执行语句换为null则表示，当满足条件时，什么都不执行。
  

例2 if … then … elsif … then … else … end if;语句。

  declare
    n1 number := 90;
    n2 number := 60;
  begin
    if n1 < n2 then
      dbms_output.put_line('n1 < n2');
    elsif n1 = n2 then
      dbms_output.put_line('n1 = n2');
    else
      dbms_output.put_line('n1 > n2');
    end if;
  end;

例3 使用loop … exit … end loop;语句计算1+2+3+…+100的值。

  declare
    i number := 0;
    sumResult number := 0;
  begin
    loop
      i := i + 1;
      sumResult := sumResult + i;
      if i >= 100 then
        exit;
      end if;
    end loop;
    dbms_output.put_line('1+2+3+..+100=' || to_char(sumResult));
  end;

• 注：不能将sumResult改为sum,因为sum为关键词，运行时会出错。

例4 使用loop … exit when … end loop;语句计算1+2+3+…+100的值。

   declare
     i number := 0;
     sumResult number := 0;
   begin
     loop
       i := i + 1;
       sumResult := sumResult + i;
       exit when i >= 100;
     end loop;
     dbms_output.put_line('1+2+3+..+100=' || to_char(sumResult));
   end;

例5 使用while … loop … end loop; 语句计算1+2+3+…+100的值。

  declare
    i number := 0;
    sumResult number := 0;
  begin
    while i <= 100 loop
      sumResult := sumResult + i;
      i := i + 1;
    end loop;
    dbms_output.put_line('1+2+3+..+100=' || to_char(sumResult));
  end;

例6 使用for … in … loop … end loop; 语句计算1+2+3+…+100的值。

  declare
    sumResult number := 0;
  begin
    for i in 1 .. 100 loop  --循环变量i不需要在declare部分声明
      sumResult := sumResult + i;
    end loop;
    dbms_output.put_line('1+2+3+..+100=' || to_char(sumResult));
  end;

例7 使用case语句更改学生表中的专业名称，将‘CS’改为‘计科’，将‘IS’改为‘信息’，将‘MA’改为‘数学’，将其他专业改为‘其他’

  select Sno,Sname,Sdept,
    case Sdept
      when 'CS' then '计科'
      when 'IS' then '信息'
      when 'MA' then '数学'
      else '其他'
    end
  from Student;

例8 使用case语句将表SC中的成绩分为A,B,C,D,E五个等级

  select Sno,Cno,Grade,
    case
      when Grade is null then '无成绩'
      when Grade >= 90 then 'A'
      when Grade >= 80 then 'B'
      when Grade >= 70 then 'C'
      when Grade >= 60 then 'D' 
      else 'E'
    end
  from SC;

例9 以例2为例进行调试

• 赋予用户开启存储过程调试的权限
  

• 第一次执行一步调试，光标定位到第二行，n1、n2的值均为null
  
  

• 第二次执行一步调试，光标定位到第三行，n1的值为90，n2的值为null
  
  

• 第三次执行一步调试，光标定位到第四行，n1的值为90，n2的值为60
  
  

二、动态语句

SQL Server

declare @str varchar(50)
set @str = 'where Sage < 20'
exec('select * from Student' + @str)

Oracle

declare
  isDelete boolean := true;
begin
  if isDelete then
    delete from SC1;  --可以执行DML语句但不能执行DDL语句如drop table SC1;
  end if;
end;

例10

declare
  vSno Student.Sno % type;
  vSql varchar2(100);
  vStudent Student % rowtype;
begin
  execute immediate 'create table temp(Sno char(7),Sname varchar2（20）,Sage number(2))';
  vSql :='insert into temp values(:1,:2,:3)';
  execute immediate vSql using '2017001','001',20;
  vSql :='select * from Student where Sno = :Sno';
  execute immediate vSql into vStudent using '2017001';
  dbms_output.put_line(vStudent.Sname);
end;

三、异常处理

SQL Server

begin try
  ...
  commit;
end try
begin catch
  ...
  rollback;
  raiserror(...)
end catch

Oracle

• 三种类型：预定义异常；自定义异常；非预定义异常

  begin
    ...执行部分...
    exception
      when 异常1 then
        异常处理...
      when 异常2 then
        异常处理...
      ...
      when others then
        异常处理...
  end;
  

（1）预定义异常

如：no_data_found, too_many_rows, invalid_number, …

• 例11

  declare
    vSname Student.Sname % type;
  begin
    select Sname into vSname from Student where Ssex = '男';
    dbms_output.put_line('若上面这条语句有错，本条语句不会执行');
    exception
      when too_many_rows then
        dbms_output.put_line('返回了多少个值');
        dbms_output.put_line('异常码：'|| sqlcode);
        dbms_output.put_line('异常消息：'|| sqlerrm);
        when no_data_found then
          dbms_output.put_line('返回了0个值');
  end;
  

（2）自定义异常

• SQL Server
  raiserror

• Oracle
  declare部分定义异常变量；
  raise语句抛出自定义异常；

• 例12

  declare
    eOverNumber exception;  --定义异常处理变量
    vCount number;
    vMaxCount number := 3;
  begin
    select count(*) into vCount from Student;
    if vMaxCount < vCount then
      raise eOverNumber;
    end if;
    exception
      when eOverNumber then
      dbms_output.put_line('Student表的记录数大于3');
  end;
  

• Oracle为用户预留例自定义异常码，范围：-20000到-20999的负整数；

• Raise_application_error（异常码，异常消息）

• 例13

  declare
    eOverNumber exception;  --定义异常处理变量
    vCount number;
    vMaxCount number := 3;
  begin
    select count(*) into vCount from Student;
    if vMaxCount < vCount then
      raise_application_error(-20001,'Student表的记录数大于3');
    end if;
  end;
  

（3）非预定义异常

• 声明部分定义异常变量；

• 使用exception_init语句将定义的异常情况与标准的Oracle错误联系起来；

• pragma exception_init（异常情况，错误代码）

• 例14

  declare
    fk_delete exception;
    pragma exception_init(fk_delete,-2292);
  begin
    delete from Student where Sno = '2017001';
    exception
      when fk_delete then
        dbms_output.put_line('捕获到了非预定义的异常FK_DELETE');
        dbms_output.put_line('无法删除该记录，违反了外键约束');
  end;