首页 > 代码库 > 【PL/SQL】异常处理:

【PL/SQL】异常处理:

如果在PLSQL块中没有做异常处理,在执行PLSQL块时,出现异常,会传递到调用环境,导致程序运行出错!

SCOTT@ prod> declare
   2  
   3   v_ename emp.ename%type;
   4   v_sal   emp.sal%type;
   5  
   6  begin
   7    
   8    select ename,sal into v_ename,v_sal from emp where deptno=&n;
   9    
  10    dbms_output.put_line(v_ename||:||v_sal);
  11    
  12  
  13  end;

1.预定义异常:
TOO_MANY_ROWS  在隐式游标处理时,select 返回行数超过一行

SQL> declare
  2  
  3   v_ename emp.ename%type;
  4   v_sal   emp.sal%type;
  5  
  6  begin
  7  
  8    select ename,sal into v_ename,v_sal from emp where deptno=&n;
  9  
 10    dbms_output.put_line(v_ename||:||v_sal);
 11  
 12  exception
 13    when too_many_rows then
 14         dbms_output.put_line(You return rows more than one !);
 15    when others  then
 16         dbms_output.put_line(Other‘‘s error !);
 17  end;

2.NO_DATA_FOUND   在访问数据时,没有发现数据。

SQL> declare
  2  
  3   v_ename emp.ename%type;
  4   v_sal   emp.sal%type;
  5  
  6  begin
  7  
  8    select ename,sal into v_ename,v_sal from emp where empno=#
  9  
 10    dbms_output.put_line(v_ename||:||v_sal);
 11  
 12  exception
 13    when no_data_found then
 14         dbms_output.put_line(No data found ,Please input correct number !);
 15    when others  then
 16         dbms_output.put_line(Other‘‘s error !);
 17  end;

3.ZERO_DIVIDE   除数为零

SQL> declare
  2  
  3    v_num1 number :=10;
  4    v_num2 number ;
  5    v_num3 number;
  6  
  7  begin
  8     v_num2 := &nn;
  9  
 10     v_num3 := v_num1 / v_num2 ;
 11  
 12     dbms_output.put_line( Number is : ||v_num3);
 13  exception
 14      when zero_divide then
 15       dbms_output.put_line( Divisor is equal to zero,Please input correct Number !);
 16      when others  then
 17      dbms_output.put_line(Other‘‘s error !);
 18  end;

 

【PL/SQL】异常处理: