oralce 异常处理 exception

原创

潘麟 2013-12-06 10:40:01 博主文章分类：数据库 ©著作权

文章标签 oracle exception 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者潘麟的原创作品，请联系作者获取转载授权，否则将追究法律责任

oracle 10g: PL/SQL User's Guide and Reference
---> 10 Handling PL/SQL Errors
---> Summary of Predefined PL/SQL Exceptions

系统预定义异常(有名字的错误代码):
TOO_MANY_ROWS : SELECT INTO返回多行
INVALID_CURSOR :非法指针操作(关闭已经关闭的游标)
ZERO_DIVIDE :除数等于零
DUP_VAL_ON_INDEX :违反唯一性约束
ACCESS_INTO_NULL: 未定义对象
CASE_NOT_FOUND: CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL: 集合元素未初始化
CURSER_ALREADY_OPEN: 游标已经打开
DUP_VAL_ON_INDEX: 唯一索引对应的列上有重复的值
INVALID_NUMBER: 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND: 使用 select into 未返回行，或应用索引表未初始化的元素时
SUBSCRIPT_BEYOND_COUNT:元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT: 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR: 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED: PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON: PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR: PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID: 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE: Oracle 在等待资源时超时

oracl 11g: Pl/SQL LANGUAGE REFERENCE
----> 11 PL/SQL error handling
----> Predefined Exceptions

练习 1:捕获预定义异常
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
end;
/

declare
  v1 emp.sal%type;
begin
  select sal into v1 from emp;
exception
  when TOO_MANY_ROWS then
   dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

declare
   v_ename employees.last_name%type;
begin
   select last_name into v_ename from employees;
exception
   when too_many_rows then
        dbms_output.put_line('too many rows,you should add a condition');
end;
/

练习 2:捕获预定义异常
declare
  v1 emp.sal%type;
begin
  select sal into v1 from emp where empno=7777;
exception
  when TOO_MANY_ROWS then
    dbms_output.put_line('more person !');
--  when NO_DATA_FOUND then
--    dbms_output.put_line('no rows selected!');
  when others then --other执行器
    dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

declare
   v_sal hr.employees.salary%type;
begin
   select salary into v_sal from hr.employees where department_id= &ID;
   dbms_output.put_line(v_sal);
exception
   WHEN TOO_MANY_ROWS then
        dbms_output.put_line('there are too many rows to return.');
   when NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('there is no data to return.');
end;

declare
   v_ename employees.last_name%type;
begin
   select last_name into v_ename from employees;
exception
   when too_many_rows then
        dbms_output.put_line('sqlcode :'||sqlcode ||chr(10)||'sqlerrm :'||sqlerrm);
end;
/
sqlcode :-1422
sqlerrm :ORA-01422: exact fetch returns more than requested number of rows

练习 3:捕获错误代码和错误描述,借助预定义函数sqlcode(ERROR代码),sqlerrm(ERROR文本)
begin
  update emp set deptno=60;
  dbms_output.put_line('ok');
exception
  when TOO_MANY_ROWS then
    dbms_output.put_line('more person !');
  when NO_DATA_FOUND then
    dbms_output.put_line('no person !');
  when others then --other执行器
    dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

练习 4:捕获非预定义异常(捕获oracle错误代码)
declare
  fk_error  exception;--声明异常
  pragma exception_init(fk_error,-2292);--使用编译指示器将异常名称和oracle的错误代码绑定
begin
  delete dept; --oracle自动传播错误（fk_error）
  dbms_output.put_line('ok');
exception
  when TOO_MANY_ROWS then
    dbms_output.put_line('more person ');
  when NO_DATA_FOUND then
    dbms_output.put_line('no person ');
  when fk_error then
    dbms_output.put_line('infringe forign key !');
end;
/

declare
   pk_fk_error exception;
   pragma exception_init(pk_fk_error,-2292);
begin
   delete hr.departments;
   dbms_output.put_line('It had been deleted.');
exception
   when pk_fk_error then
        dbms_output.put_line('It can not delete.');
end;
/
It can not delete.

用户自定义异常的sqlcode代码段：-20001 ~ -20999
练习 5:捕获用户自定义的异常:
declare
  my_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(my_error, -20001);--编译指示,将命名的异常与ORACLE ERROR关联
BEGIN
  raise_application_error(-20001,'工资不能被改动!');--将异常传送到环境
  UPDATE e SET SAL=1000;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('未检索到数据!');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('SELECT返回多行数据!');
  WHEN MY_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('E表工资不可以被修改!');
end;
/

declare
   my_excep EXCEPTION;
   PRAGMA EXCEPTION_init(my_excep,-20001);
   v_did hr.employees.department_id%type := &id;
begin
   if v_did != 10 then
        raise_application_error(-20001,'my own exception.');
   else
       update hr.employees set salary=salary+1 where department_id=v_did;
       dbms_output.put_line('ok');
   end if;
end;

练习 6:捕获用户自定义的异常
declare
  my_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(my_error, -20001);
  v_empno number(4):=&p_empno;
begin
  IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '14' OR TO_CHAR (SYSDATE, 'DY') IN ('星期六', '星期日') THEN
    RAISE my_error;
  else
    insert into e(empno) values (v_empno);
    dbms_output.put_line('insert 成功!');
  END IF;
exception
  when my_error then
    dbms_output.put_line('该时间段不能向E表插入数据!');
end;
/

declare
   v_raise_sal number := &raise_sal;
   my_error EXCEPTION;
   PRAGMA exception_init(my_error,-20001);
begin
   if v_raise_sal > 1000 then
      raise_application_error(-20001,'can not raise so much money.');
   else
      update employees set salary=salary+v_raise_sal;
   end if;
--exception
--   when my_error then
--       dbms_output.put_line('you can not raise.');
end;
/
错误报告:
ORA-20001: can not raise so much money.
ORA-06512: at line 7
****************************************************************************************************
练习 7:打印 ORA-##### 错误编号和描述:
SPOOL D:\ORACLE_ERROR.TXT
SET SERVEROUTPUT ON
DECLARE
  ERR_MSG VARCHAR2(4000);
  ERR_CODE NUMBER(10);
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  FOR ERR_NUM IN 20000..20999
  LOOP
    ERR_CODE:=sqlcode;
    ERR_MSG := SQLERRM(-ERR_NUM);
     IF ERR_MSG NOT LIKE '%Message '||ERR_NUM||' not found%' then
       dbms_output.put_line(ERR_MSG);
     END IF;
  END LOOP;
END;
/
SPOOL OFF;

--嵌套的PL/SQL代码段的异常处理
declare
  v_ename varchar2(10);
begin
  select ename into v_ename from emp where empno=7839;
  dbms_output.put_line(v_ename);
    declare
      v1 emp.sal%type;
    begin
      select sal into v1 from emp;
    exception
      when TOO_MANY_ROWS then
       dbms_output.put_line(sqlcode||';'||sqlerrm);
    end;
  dbms_output.put_line('ok');
end;
/

--内部pl/sql代码段，进行了异常处理，是所有外部程序可以正常执行；
declare
   v_name employees.last_name%type;
begin
   select last_name into v_name from hr.employees
   where employee_id=100;
   declare
       v_no departments.department_id%type;
   begin
       select department_id into v_no from departments;
   exception
      when  OTHERS then
          dbms_output.put_line('too may rows');
   end;
   dbms_output.put_line(v_name);
end;
/

too may rows
King

--内部pl/sql代码段异常
declare
   v_name hr.employees.last_name%type;
begin
   select last_name into v_name from hr.employees where employee_id=100;
   dbms_output.put_line(v_name);
   declare
       v_sal hr.employees.salary%type;
   begin
       select salary into v_sal from employees;
       dbms_output.put_line('inner pl/sql block');
--   exception
--       when too_many_rows then
--           dbms_output.put_line('inner exception capured.');
   end;
   dbms_output.put_line('outer block');
exception
   when too_many_rows then
       dbms_output.put_line('outer exception captured.');
end;