oracle异常处理 例解

异常处理是用来处理正常执行过程中未预料的事件。如果PL/SQL程序块一旦产生异常而又没有指出如何处理时，程序会自动终止。
异常处理部分放在PL/SQL的后半部分，结构为：

EXCEPTION
          WHEN first_exception THEN <code to handle first exception>
          WHEN second_exception THEN <code to handle second exception>
          WHEN OTHERS THEN <code to handle second exception>     --OTHERS必须放在最后

异常分为预定义和用户定义两种。
用户定义的异常是通过显式使用RAISE语句来引发。如

DECLARE
  e_TooManyStudents EXCEPTION;  -- 类型为Exception，用于指示错误条件
  v_CurrentStudents NUMBER(3);  -- HIS-101学生注册当前号
  v_MaxStudents NUMBER(3);      -- HIS-101学生注册允许的最大号

BEGIN
　 /* 找出注册学生当前号和允许的最大号 */

  SELECT current_students, max_students

    INTO v_CurrentStudents, v_MaxStudents

    FROM classes

    WHERE department = 'HIS' AND course = 101;

  /* 检查学生的号 */

  IF v_CurrentStudents > v_MaxStudents THEN

/* 太多的学生注册，则触发例外处理 */

  RAISE e_TooManyStudents;

  END IF;

EXCEPTION

  WHEN e_TooManyStudents THEN

    /* 当太多的学生注册，就插入信息解释发生过错误 */

 

    INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||

      'students: max allowed is ' || v_MaxStudents);

END;

END;

用户定义的的异常处理
可以使用RAISE_APPLICATION_ERROR来创建自己的错误处理：
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
其中error_number是从-20000到-20999之间的参数；error_message是相应的提示信息，小于512字节。如：

CREATE OR REPLACE PROCEDURE Register (
p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) AS
v_CurrentStudents NUMBER;  -- 班上学生的当前号
v_MaxStudents NUMBER;      -- 班上学生的最大号

 

BEGIN
/* 找出学生的当前号和最大号 */
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE course = p_Course
AND department = p_Department;

/* 确认另外的学生是否有足够的教室*/
IF v_CurrentStudents + 1 > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' ||
p_Department || ' ' || p_Course);
END IF;

 

/* 加一个学生在本班 */
ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);

EXCEPTION
WHEN NO_DATA_FOUND THEN
　　　RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
          ' doesn''t exist!');
END Register;

create or replace function f_kilorar(inareano in varchar2) return boolean is
     Result boolean;
  
     type c_cursor is ref cursor;
     c_kilo_group           c_cursor;
     v_kilo_group           kilo_group%rowtype;
  
     lsnetunit_nbr varchar2(20);
     lsstart_nbr      varchar2(20);
     lsend_nbr        varchar2(20);
  
     lsnetunit_old varchar2(20);
     lsstart_old      varchar2(20);
     lsend_old        varchar2(20);
  
  
begin

     lsnetunit_old := 1;
     lsstart_old      := 1;
     lsend_old        := 1;
  
  
     open c_kilo_group
         for  
            select * from kilo_group
                                     where areano = inareano
                                     order by netunit_nbr,start_nbr;
           loop
               fetch c_kilo_group into v_kilo_group;
               exit when c_kilo_group%notfound;
     
                    begin
                      lsnetunit_nbr := v_kilo_group.netunit_nbr;
                      lsstart_nbr      := v_kilo_group.start_nbr;
                      lsend_nbr        := v_kilo_group.end_nbr;
                   
                      if lsnetunit_nbr = lsnetunit_old and lsstart_nbr - lsend_old = 1     then
                         --记录日志
                         insert into log_kilo_group
                            select                  kilo_group      ,
                                                 areano          ,
                                                 netunit_nbr     ,
                                                 small_netunit,
                                                 bz              ,
                                                 start_nbr       ,
                                                 end_nbr         ,
                                                 'divisefunctiondelete'        ,
                                                 sysdate         ,
                                                 ''
                    
                         from kilo_group t
                         where areano = inareano
                                           and netunit_nbr = lsnetunit_nbr and start_nbr = lsstart_nbr;
                                        
                         insert into log_kilo_group
                            select                  kilo_group      ,
                                                 areano          ,
                                                 netunit_nbr     ,
                                                 small_netunit,
                                                 bz              ,
                                                 start_nbr       ,
                                                 end_nbr         ,
                                                 'divisefunctionupdate'        ,
                                                 sysdate         ,
                                                 ''
                    
                         from kilo_group t
                         where areano = inareano
                                           and netunit_nbr = lsnetunit_old and start_nbr = lsstart_old;
                      
                         --删除可以合并的当前行          
                         delete kilo_group where areano = inareano
                                           and netunit_nbr = lsnetunit_nbr and start_nbr = lsstart_nbr;
                                        
                         --修改可以合并的上一行，合并删除行的号段                             
                         update kilo_group set end_nbr = lsend_nbr
                                           where areano = inareano
                                           and netunit_nbr = lsnetunit_old and start_nbr = lsstart_old;
                      
                      end if;
                   
                      lsnetunit_old := lsnetunit_nbr;
                      lsstart_old      := lsstart_nbr;
                      lsend_old        := lsend_nbr;
                   
                   
                   
                    commit;
                       Result := true;
                    exception WHEN others THEN
                        rollback;
                        RAISE_APPLICATION_ERROR(-20001,SQLERRM);
                        Result := false;
                    end;

              end loop;

     close c_kilo_group;
  
     return(Result);
end f_kilorar;

       Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。在一个内在的异常中，SQLCODE返回Oracle错误的序号，而SQLERRM返回的是相应的错误消息，错误消息首先显示的是错误代码。SQLCODE返回的是负数，除非Oracle的错误为“ORA-01403：NO DATA FOUND”（译：ORA-01403：未找到数据），当Oracle错误为“ORA-01403：NO DATA FOUND”时，其对应的SQLCODE为+100。