`

PL/SQL异常处理(原创)

 
阅读更多

Exception概述

Exception是一种PL/SQL标识符,当运行的PL/SQL块出现错误或警告,则会触发异常处理。为了提高程序的健壮性,可以在PL/SQL块中引入异常处理部分,进行捕捉异常,并根据异常出现的情况进行相应的处理。
ORACLE异常分为两种类型:系统异常、自定义异常。其中系统异常又分为:预定义异常和非预定义异常。
预定义异常
ORACLE定义了他们的错误编号和异常名字,常见的预定义异常处理如下

错误号

异常错误信息名称

说明

ORA-0001

Dup_val_on_index

违反了唯一性限制

ORA-0051

Timeout-on-resource

在等待资源时发生超时

ORA-0061

Transaction-backed-out

由于发生死锁事务被撤消

ORA-1001

Invalid-CURSOR

试图使用一个无效的游标

ORA-1012

Not-logged-on

没有连接到ORACLE

ORA-1017

Login-denied

无效的用户名/口令

ORA-1403

No_data_found

SELECT INTO没有找到数据

ORA-1422

Too_many_rows

SELECT INTO 返回多行

ORA-1476

Zero-divide

试图被零除

ORA-1722

Invalid-NUMBER

转换一个数字失败

ORA-6500

Storage-error

内存不够引发的内部错误

ORA-6501

Program-error

内部错误

ORA-6502

Value-error

转换或截断错误

ORA-6504

Rowtype-mismatch

宿主游标变量与 PL/SQL变量有不兼容行类型

ORA-6511

CURSOR-already-OPEN

试图打开一个已处于打开状态的游标

ORA-6530

Access-INTO-null

试图为null 对象的属性赋值

ORA-6531

Collection-is-null

试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray

ORA-6532

Subscript-outside-limit

对嵌套或varray索引得引用超出声明范围以外

ORA-6533

Subscript-beyond-count

对嵌套或varray 索引得引用大于集合中元素的个数.

非预定义异常
ORACLE为它定义了错误编号,但没有定义异常名字。我们使用的时候,先声名一个异常名,通过伪过程PRAGMA EXCEPTION_INIT,将异常名与错误号关联起来。
自定义异常
程序员从业务角度出发,制定的一些规则和限制。
异常处理
PL/SQL中,异常处理按个步骤进行:
定义异常

exception_name EXCEPTION;
抛出异常

RAISE exception_name
捕获及处理异常
EXCEPTION
    WHEN e_name1 [OR e_name2 ... ] THEN
        statements;
    WHEN e_name3 [OR e_name4 ... ] THEN
        statements;
        ......
    WHEN OTHERS THEN
        statements;
END;

预定义异常处理示例  
一个整除的异常
SQL>  declare
  2    v_n1 number := 50;
  3    v_n2 number := 0 ;
  4    v_n3 number;
  5  begin
  6    v_n3 := v_n1/v_n2;
  7    dbms_output.put_line('v_n3=' || v_n3);
  8  exception
  9    when zero_divide then
 10      dbms_output.put_line('v_n2 can' || '''' || 't be 0');
 11* end;


v_n2 can't be 0
PL/SQL procedure successfully completed.

VALUE_ERROR(错误号ORA-06502)

SQL> declare
  2    v_ename varchar2(3);
  3  begin
  4    select ename into v_ename from emp where empno = &eno;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when value_error then
  8      dbms_output.put_line('variable datatype length is small');
  9  end;
 10  /
Enter value for eno: 7788
old   4:   select ename into v_ename from emp where empno = &eno;
new   4:   select ename into v_ename from emp where empno = 7788;
variable datatype length is small
PL/SQL procedure successfully completed.
TOO_MANY_ROWS(对应Oracle错误号ORA-01422)
SQL>declare
  2    v_ename emp.ename%type;
  3  begin
  4    select ename into v_ename from emp where deptno = &deptno;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when too_many_rows then
  8      dbms_output.put_line('Too many rows are returned');
  9  end;
 10  /
Enter value for deptno: 30
old   4:   select ename into v_ename from emp where deptno = &deptno;
new   4:   select ename into v_ename from emp where deptno = 30;
Too many rows are returned
PL/SQL procedure successfully completed.
非预定义异常示例

非预定义异常使用的基本过程如下
a.定义一个异常名
b.将异常名与异常编号相关联
c.在异常处理部分捕捉并处理异常

SQL> delete from dept where deptno = 10;
delete from dept where deptno = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
SQL> declare
  2    e_deptid exception;       --定义异常
  3    pragma exception_init(e_deptid,-2292);       --将异常和错误号关联
  4  begin
  5    delete from dept where deptno = 10;
  6  exception
  7    when e_deptid then         --捕获异常
  8      dbms_output.put_line('There is record at sub table');
  9  end;
 10  /
There is record at sub table
PL/SQL procedure successfully completed.
自定义异常示例
自定义异常与Oracle错误没有任何关系,由开发人员为特定情况所定义的例外。下面的例子中,通过自定义异常,当雇员编号不存在时,PL/SQL代码能够给出适当的提示
对于自定义的异常处理需要显示的触发,其步骤如下
a.定义异常(在declare部分进行定义)
b.显示触发异常(在执行BEGIN部分触发异常,使用RAISE语句)
c.引用异常(在EXCEPTION部分捕捉并处理异常)
SQL> update emp set deptno=20 where empno=1111;
0 rows updated.
SQL> declare
  2    e_integrity exception;
  pragma exception_init(e_integrity,-2291);
  4    update emp set deptno = &dno where empno = &eno;
  e_no_employee exception;
begin
  update emp set deptno = &dno where empno = &eno;
  if sql%notfound then
    raise e_no_employee;
  end if;
 10  exception
 11    when e_integrity then
    dbms_output.put_line('The dept does not exists');
  when e_no_employee then
    dbms_output.put_line('The employess does not exists');
 15  end;
 16  /
Enter value for dno: 20
Enter value for eno: 1111
old   6:   update emp set deptno = &dno where empno = &eno;
new   6:   update emp set deptno = 20 where empno = 1111;
The employess does not exists
PL/SQL procedure successfully completed.
工资如果少于1500,则抛出异常
SQL> declare
  2    v_empno emp.empno%type;
  3    v_sal   emp.sal%type;
  4    e_sal exception;
  5  begin
  6    v_empno := &empno;
  7    v_sal   := &sal;
  8    insert into emp(empno,sal) values(v_empno,v_sal);
  9    if v_sal < 1500 then
 10      raise e_sal;
 11    end if;
 12  exception
 13    when e_sal then
 14      rollback;
 15      dbms_output.put_line('Salary must be more then 1500');
 16  end;
 17  /
Enter value for empno: 8888
old   6:   v_empno := &empno;
new   6:   v_empno := 8888;
Enter value for sal: 1234
old   7:   v_sal   := &sal;
new   7:   v_sal   := 1234;
Salary must be more then 1500
PL/SQL procedure successfully completed.
使用异常函数处理异常          

SQLCODE与SQLERRM

SQLCODE与SQLERRM为异常处理函数。函数SQLCODE用于取得Oracle错误号,函数SQLERRM用于取得与错误号对应的相关错误消息
SQL> declare
  2    v_ename emp.ename%type;
  3  begin
  4    select ename into v_ename from emp
  5    where sal = &sal;
  6    dbms_output.put_line('Employee Name:' || v_ename);
  7  exception
  8    when no_data_found then
  9      dbms_output.put_line('The employee does not exists');
 10    when others then
 11      dbms_output.put_line('Error No:' || SQLCODE);
 12      dbms_output.put_line(SQLERRM);
 13  end;
 14  /
Enter value for sal: 1250
old   5:   where sal = &sal;
new   5:   where sal = 1250;
Error No:-1422
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
RAISE_APPLICATION_ERROR
调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程,可以重新定义异常错误消息,它为应用程序提供了一种与ORACLE交互的方法。该函数用于在PL/SQL中定义错误消息,且只能在数据库端的子程序中使用(存储过程、函数、包、触发器),不能在匿名块和客户端的子程序中使用
使用方法
RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
该函数内的错误代码和内容,都是用用户自定义
error_number:用于定义错误号,且错误号从-20000 到-20999 之间,以避免与ORACLE 的任何错误代码发生冲突。
message:用于指定错误消息,且消息长度不能超过k,超出部分将被截取
可选参数true,false:默认值为false,会替换先前的所有错误。当设置为true,则该错误会被放在先前错误堆栈中。
SQL> create or replace procedure raise_comm
  2  (v_no emp.empno%type,v_comm out emp.comm%type)
  3  as
  4  begin
  5    select comm into v_comm from emp where empno = v_no;
  6    if v_comm is null then
  7      raise_application_error(-20001,'This employee has no comm');
  8    end if;
  9  exception
 10    when no_data_found then
    dbms_output.put_line('The employee does not exists');
 12  end;
 13  /
Procedure created.
SQL> var g_sal number;
SQL> exec raise_comm(7788,:g_sal);
BEGIN raise_comm(7788,:g_sal); END;
*
ERROR at line 1:
ORA-20001: This employee has no comm
ORA-06512: at "SCOTT.RAISE_COMM", line 7
ORA-06512: at line 1
SQL> exec raise_comm(7499,:g_sal);
PL/SQL procedure successfully completed.
SQL> print g_sal;
     G_SAL
----------
       300
PL/SQL编译警告
PL/SQL警告的分类
SEVERE: 用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题.
PERFORMANCE: 用于检查可能引起性能问题,如在INSERT操作是为NUMBER列提供了VARCHAR2类型数据.
INFORMATIONAL: 用于检查程序中的死代码.
ALL: 用于检查所有警告.
控制PL/SQL警告消息
通过设置初始化参数PLSQL_WARNINGS来启用在编译PL/SQL子程序时发出警告消息,缺省为DISABLE:ALL
设置警告消息时有如下不同的范围
系统级别
会话级别
ALTER PROCEDURE      ---只针对设置的过程有效
将特定的消息号设置为错误,也可以激活或禁止特定消息号
SQL> show parameter plsql%ings;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings                       string      DISABLE:ALL      
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'
SQL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> alter session set plsql_warnings='enable:(5001,5002)';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
PL/SQL编译告警示例
检测死代码
在下面的代码中,ELSE子句永远不会执行,应该避免出现类似的死代码.从Oracle 10g开始,在编写PL/SQL子程序之前开发人员可以激活警告检查.

SQL> alter session set plsql_warnings='enable:informational';
Session altered.

SQL> show parameter plsql_warnings
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
plsql_warnings                       string                            ENABLE:INFORMATIONAL, DISABLE:
                                                                       PERFORMANCE, DISABLE:SEVERE
SQL> create or replace procedure dead_code
  2  as
  3  x number := 10;
  4  begin
  5    if x > 10 then
  6      x := 1;
  7    else
  8      x := 2;      --死代码
  9    end if;
 10  end ;
 11  /
SP2-0804: Procedure created with compilation warnings
检测引起性能问题的代码
SQL>  alter session set plsql_warnings='enable:performance';
Session altered.
SQL>  create or replace procedure update_sal
  2   (no number,salary varchar2)
  3   as
  4   begin
  5     update emp set sal=salary where empno=no;
  6   end;
  7   /
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE UPDATE_SAL:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------
5/23     PLW-07202: bind type would result in conversion away from column  type

参考至:http://blog.csdn.net/robinson_0612/article/details/6080119
              http://www.cnblogs.com/soundcode/archive/2012/01/10/2318385.html

              http://blog.csdn.net/endless_horizen/article/details/6563034

              http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/initparams165.htm

本文原创,转载请注明出处 、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

8
1
分享到:
评论

相关推荐

    oracle10g_pl/sql

    Oracle 10g PL/SQL 是Oracle数据库系统中用于创建和管理存储过程、函数、触发器等数据库对象的编程语言。本教程旨在为初学者提供一个全面的学习平台,同时也为经验丰富的开发者提供参考资料。PL/SQL是Oracle特有的...

    pl/sql最新中文手册

    3. **异常处理**:在PL/SQL中,异常处理是通过EXCEPTION关键字实现的,用于捕获并处理运行时错误。手册会详细解释如何定义和使用预定义及自定义异常。 4. **数据库操作**:PL/SQL与Oracle数据库紧密集成,可以执行...

    一个对数据库的操作工具PL/SQLpl/sqL工具

    1. **编程能力**:PL/SQL允许编写包含控制结构(如循环、条件语句)和异常处理的程序块,这些块可以存储在数据库中并按需调用,提高了数据库应用的灵活性和复杂性。 2. **数据处理**:通过PL/SQL,用户可以直接对...

    Oracle PL/SQL实战(待续)

    在Oracle数据库中,PL/SQL提供了丰富的控制结构,如循环、条件判断,以及异常处理机制,使得开发者能够编写复杂的业务逻辑。例如,`IF...THEN...ELSIF...ELSE`语句用于实现条件分支,`WHILE`和`FOR`循环则可以处理...

    pl/sql developer11.0

    PL/SQL Developer是一款由Allround Automations公司开发的专业Oracle数据库开发工具,专为编写、调试、测试和管理PL/SQL代码而设计。标题中的“pl/sql developer11.0”指的是该软件的第11个主要版本。在本文中,我们...

    pl/sql64位

    64位的PL/SQL开发者工具对于那些处理大数据量或者需要更高性能的用户来说尤其重要,因为它能够更好地利用现代计算机的内存和处理器资源。 在32位环境中,PL/SQL开发者工具可能受到内存限制,因为32位系统最大只能...

    oracle 9i pl/sql程序设计笔记

    通过上述对Oracle 9i PL/SQL程序设计笔记的总结,我们可以看到PL/SQL作为一种强大的数据库编程语言,提供了丰富的功能来支持数据处理、事务控制及业务逻辑的实现。掌握PL/SQL的不同块类型及其使用方法对于开发高效、...

    oracle pl/sql从入门到精通 配套源代码

    PL/SQL是Oracle数据库的内置编程语言,由块构成,包括声明部分、执行部分和异常处理部分。块可以是匿名块(即直接在SQL*Plus或其他工具中编写的一次性执行的代码),也可以是存储过程、函数、触发器、包等可重用的...

    ORACLE PL/SQL从入门到精通

    ORACLE PL/SQL是从入门到精通的专业知识,涵盖了数据库开发与管理的多个方面,包括触发器、过程、函数、软件包、异常处理、游标、循环、分支、变量使用、数据库安装等关键知识点。 触发器是数据库中用来保证数据...

    PL/SQL 基本知识

    首先,PL/SQL是一种过程化语言,它允许用户定义变量、控制流程(如循环、条件语句)、处理异常,并且可以嵌入SQL查询,进行数据的增删改查操作。通过使用PL/SQL,数据库管理员和开发者可以更高效地管理和维护数据库...

    Oracle PL/SQL实例编程(PL/SQL经典书籍)

    5. **异常处理**:PL/SQL提供了异常处理机制,通过BEGIN...EXCEPTION块来捕获和处理运行时错误,如NO_DATA_FOUND、TOO_MANY_ROWS和OTHERS。 6. **包(PACKAGE)**:包是将相关的常量、变量、过程和函数组合在一起的...

    Oracle PL/SQL程序设计(第5版)(上下册)

    - **块结构**:PL/SQL程序通常被组织成块,每个块由声明部分、执行部分和异常处理部分组成。 - **子程序**:包括存储过程和函数。这些子程序可以被其他PL/SQL代码调用,实现代码复用和模块化。 - **包**:包是一种...

    PL/SQL下载

    PL/SQL全称为“Procedural Language/Structured Query Language”,它提供了丰富的控制结构和异常处理机制,使得开发者能够编写复杂的数据库操作程序。 **PL/SQL的特点** 1. **集成性**:PL/SQL与Oracle数据库紧密...

    PL/SQL VCS插件安装包+PL/SQL

    PL/SQL VCS插件是为开发者提供的一种增强工具,它与PL/SQL Developer整合,目的是为了更好地管理和控制Oracle数据库中的SQL脚本和存储过程的版本。这个安装包结合了Version Control System (VCS)的功能,如Visual ...

    pl/sql 免安装,绿色版pl/sql

    1. **PL/SQL编程支持**:PL/SQL Developer内置了强大的PL/SQL编辑器,支持语法高亮、自动完成、错误检查等功能,使开发人员能够高效编写PL/SQL代码。此外,还具备代码折叠、查找替换、书签设置等实用功能,提高了...

    PL/SQL入门到精通书的源代码

    6. **异常处理**:PL/SQL提供了一个强大的异常处理机制,允许你定义和捕获运行时错误,从而编写健壮的代码。 7. **游标 FOR 循环**:这是一种简化版的游标使用,可以方便地遍历查询结果集,无需显式打开、提取和...

    PL/SQL Developer 远程连接Oracle数据库

    PL/SQL Developer是一款专为Oracle数据库设计的集成开发环境,它极大地简化了PL/SQL语言的编写、调试和管理任务。远程连接Oracle数据库是PL/SQL Developer的一项关键功能,允许用户在本地计算机上操作和管理远程...

    PL/SQL编程基础知识

    ### PL/SQL编程基础知识 #### 一、PL/SQL简介 PL/SQL(Procedural Language for SQL)是一种过程化语言,它结合了SQL的数据操纵功能和过程化语言的控制结构,使用户能够灵活地控制数据操作流程。作为Oracle数据库...

    pl/sql快捷插件

    PL/SQL Developer是一款由Allround Automations公司开发的专门用于Oracle数据库管理的集成开发环境(IDE)。这款软件为Oracle数据库管理员、开发人员和测试人员提供了便捷的SQL和PL/SQL编写、调试和执行功能。而“pl...

    PL/SQL doc 文件

    PL/SQL doc 文件 PL/SQL 是 ORACLE 对标准数据库语言的扩展,它被整合到 ORACLE 服务器和其他工具中,近几年中更多的开发人员和 DBA 开始使用 PL/SQL。本文将讲述 PL/SQL 基础语法、结构和组件、以及如何设计并执行...

Global site tag (gtag.js) - Google Analytics