Oracle PL/SQL中异常高级特性示例解析

所属分类: oracle / 数据库 阅读数: 427
收藏 0 赞 0 分享

PL/SQL(Procedural Language/SQL,过程语言/SQL)是结合了Oracel过程语言和结构化查询语言(SQL)的一种扩展语言。

优点:

(1)PL/SQL具有编程语言的特点,它能把一组SQL语句放到一个模块中,使其更具模块化种序的特点。

(2)PL/SQL可以采用过程性语言控制程序的结构。

(3)PL/SQL有自动处理的异常处理机制。

(4)PL/SQL程序块具有更好的可移植性,可移植到另一个Oracle数据库中。

(5)PL/SQL程序减少了网络的交互,有助于提高程序性能。

在OraclePL/SQL语句块中exception的异常处理部分是非常重要的组成部分,它决定了在PL/SQL语句块内部可执行部分在发生异常错误时,程序是友好地提示:程序遇到某些错误而无法执行,还是抛出一堆难以理解的Oracle内部错误码。

  本文只介绍3种PL/SQL异常的三种高级形态,用于解决Oracle内置异常过少,很多时候不能够满足实际的使用需求。

1,RAISE_APPLICATION_ERROR

 - 是Oracle提供的一种特殊的内置过程,允许程序员为特定的程序创建有意义的错误消息,适用于用户自定义定义异常。
 - 语法结构
  RAISE_APPLICATION_ERROR (error_number,error_message);或者
  RAISE_APPLICATION_ERROR (error_number,error_message,keep_errors)
  - error_number 是与特定错误消息关联的错误编号,Oracle预留了-20999 -- -20000专门提供给程序员自定义错误代码。
  - error_message 是错误消息文本,最多包含2048个字符。
  - keep_errors 是可选的Boolean参数,默认为FALSE,如果为TRUE,新抛出的错误会被添加到已抛出的错误列表中,这个错误列表称为错误栈,如果为FALSE,新错误会替换已抛出的错误栈。
 - 适用于未命名的用户定义异常,负责把错误编号和错误消息关联,用户定义了异常,却没有定义该错误的名称
 - 使用RAISE_APPLICATION_ERROR过程,程序员能够遵循与Oracle一致的方式返回错误消息。

 - 示例代码

declare v_id number := &p_id; v_name varchar2(20); v_sal number;begin if v_id > 0 then  select ename,sal into v_name,v_sal from emp where empno = v_id;
  dbms_output.put_line(chr(10)||v_name||' '||v_sal); else  raise_application_error (-20001,'Employee id can not be negative.'); end if;exception when NO_DATA_FOUND then  dbms_output.put_line(chr(10)||'There is no such employee id is '||v_id); end;/Enter value for p_id: 40old 2: v_id number := &p_id;new 2: v_id number := 40;There is no such employee id is 40PL/SQL procedure successfully completed./Enter value for p_id: -90old 2: v_id number := &p_id;new 2: v_id number := -90;declare*ERROR at line 1:ORA-20001: Employee id can not be negative.ORA-06512: at line 11

 - 示例解析:该PL/SQL代码会根据用户输入的员工Id,查询员工的姓名和工资。当我们输入存在的员工编号时,程序能够正常返回结果;如果输入不存在ID,则select into语句会抛出没有返回行,进而使程序进入异常处理部分(本部分为举例),程序同样执行成功;当输入一个负数时,if条件语句就会进入到raise_application_error部分,由于可执行部分运行发生错误,执行焦点会立即转移到异常处理部分,而异常处理部分没有关于该异常的处理,所以程序报错,并返回到用户界面。

 - 是哟个raise_application_error,程序员可以使程序实现像Oracle系统产生的错误消息。

 - 事实上,单纯使用raise_application_error,因为没有异常的名称,如果要对其进行异常处理,只能够使用others(下文有专门的介绍)。

2,EXCEPTION_INIT

 - 使用EXCEPTION_INIT编译指令,可以将用户自定义的Oracle错误编号和用户自定义的错误名称关联起来,相当于用户自定义错误和RAISE_APPLICATION_ERROR的结合体。

 - EXCEPTION_INIT 出现在语句块的声明部分: 

exception_name exception;  pragma exception_init(exception_name,error_code)

 - 考虑如下代码:

declare v_no number := &p_no;begin delete from dept where deptno = v_no; dbms_output.put_line(chr(10)||'The department id is '||v_no||' has been deleted');end;/Enter value for p_no: 20old 2: v_no number := &p_no;new 2: v_no number := 20;declare*ERROR at line 1:ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record foundORA-06512: at line 4

 - 由于违反外键约束,删除部门失败了。但是抛出的错误不是很好理解

 - 我们可以使用EXCEPTION_INIT来对这个错误进行处理,首先我们得知道违反外键约束的这个Oracle错误代码“ORA-02292”

 - 使用EXCEPTION_INIT

declare v_no number := &p_no; e_dept_exist exception; pragma exception_init(e_dept_exist,-02292);begin delete from dept where deptno = v_no; dbms_output.put_line(chr(10)||'The department id is '||v_no||' has been deleted');exception when e_dept_exist then  dbms_output.put_line(chr(10)||'There are some employees in this deptartment, if you want delete this deptartment ,please delete these employees in the department first.');end;/ Enter value for p_no: 20old 2: v_no number := &p_no;new 2: v_no number := 20;There are some employees in this deptartment, if you want delete this deptartment ,please delete these employees in the department first.PL/SQL procedure successfully completed.

 - 这下抛出的错误就容易理解多了。首先我们定义了一个名为e_dept_exist的异常,然后将这个异常与Oracle错误代码 -02292 进行关联。当程序执行报错时进入异常处理部分,在这里我们重新给这个错误定义了错误消息。

3,SQLCODE 和 SQLERRM

 - 在异常处理中,当异常的名称未知时(比如上面1中RAISE_APPLICATION_ERROR),都可以使用others来进行异常的捕获处理;

 - 由于others所捕获的异常是未知的(也可以是已知的,但是在程序中没有将其枚举出来),因此需要使用Oracle提供的两个内置函数SQLCODE、SQLERRM来针对others的异常进行处理:

 - SQLCODE 会返回Oracle的错误编号
 - SQLERRM,返回错误的消息

 - 示例1,处理Oracle系统返回的错误:

declare v_no number := &p_no; error_code number; error_msg varchar2(500);begin delete from dept where deptno = v_no; dbms_output.put_line(chr(10)||'The department id is '||v_no||' has been deleted');exception when others then  error_code := sqlcode;
  error_msg := sqlerrm;
  dbms_output.put_line(chr(10)||'Error code is: '||error_code);
  dbms_output.put_line(chr(10)||'Error message is: '||error_msg);end;Enter value for p_no: 10old 2: v_no number := &p_no;new 2: v_no number := 10;Error code is: -2292Error message is: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record foundPL/SQL procedure successfully completed.

 - 请注意exception异常处理部分,在该部分里面我们用到了声明部分定义的两个变量,error_code用来存储SQLCODE,error_msg用来存储SQLERRM。然后将两个变量值打印出来。

 - 示例2,处理用户自定义的异常:

declare v_id number := &p_id; v_name varchar2(20); v_sal number;begin if v_id > 0 then  select ename,sal into v_name,v_sal from emp where empno = v_id;
  dbms_output.put_line(chr(10)||v_name||' '||v_sal); else  raise_application_error (-20001,'Employee id can not be negative.'); end if;exception when NO_DATA_FOUND then  dbms_output.put_line(chr(10)||'There is no such employee id is '||v_id);
  when others then  declare   error_code number;
   error_msg varchar2(500);
  begin   error_code := sqlcode;
   error_msg := sqlerrm;
   dbms_output.put_line(chr(10)||'Error code is: '||error_code);
   dbms_output.put_line(chr(10)||'Error message is: '||error_msg);
  end;end;/Enter value for p_id: -90old 2: v_id number := &p_id;new 2: v_id number := -90;Error code is: -20001Error message is: ORA-20001: Employee id can not be negative.PL/SQL procedure successfully completed.

 - 在本代码中使用了raise_application_error,由于单纯的使用raise_application_error,只能使用others进行捕获。在异常处理部分,我们使用了一个PL/SQL语句块来处理这个错误,声明两个变量,并将SQLCODE和SQLERRM以字面值赋值的方法给这两个变量。

以上所述是小编给大家介绍的Oracle PL/SQL中异常高级特性示例解析,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

更多精彩内容其他人还在看

Oracle在Mybatis中SQL语句的配置方法

数据库中有下划线的字段在实体中应采用驼峰命名法,如P_NAME对应pName,实例如下:1.XML文件中SQL语句配置(Geteway.xml文件)<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE m... 查看详情
收藏 0 赞 0 分享

Oracle删除表及查看表空间的实例详解

Oracle常用的基本命令--1.用户下表中注释模糊查询:例如查询与优惠券关联的表SELECT * FROM user_tab_comments t WHERE t.comments LIKE '%优惠券%';![这里写图片描述](http://img.blog.csdn.net... 查看详情
收藏 0 赞 0 分享

Win7 64环境下Oracle10g 64位版本安装教程

刚接触Oracle不久,安装部署链接Oracle的时候,出现很多问题,单从这点上来说,SQL Server真是太友好了。1、如果之前安装过Oracle软件,建议完全卸载(究竟有没有必要_不知道_我是这么做的) 2、清理过旧版本的Oracle后,开始安装Oracle10g... 查看详情
收藏 0 赞 0 分享

PL/SQL Developer连接64位的Oracle图文教程

由于硬件技术的不断更新,Win7系统逐渐成为主流,而且计算机内存逐渐增大,为了充分的利用内存资源(因为32为系统最多只能用到3G左右的内存),提高系统性能,很多人开始使用Win7(64Bit)的系统。在64位系统上安装64位的Oracle数据库,但是没有对应的64位PL/SQL ... 查看详情
收藏 0 赞 0 分享

win7下oracle 10g安装图文教程

为大家准备的win7下oracle10g安装图解教程,非常详细:方法/步骤Oracle10g 安装预备步骤取得 Oracle 10g 安装程序,或从 Oracle 技术网(OTN)下载光盘映像。在评估阶段您可以免费下载和使用无技术限制的全功能 Oracle,但在正式的商业场合应用... 查看详情
收藏 0 赞 0 分享

oracle密码过期的彻底解决方案

越是赶时间,越耽误事,测试环境oracle密码过期。不多说,解决:设置成永不过期(SIT环境)。1.以sys用户登录管理数据库;查询语句如下,未格式化查询语句。。select * from dba_profiles where profile = 'DEFAULT' and ... 查看详情
收藏 0 赞 0 分享

oracle 12c安装教程(window)

oracle 12c安装教程记录如下1.数据库的下载www.orcale.com访问官网自行下载。2.必须要把两个文件都要下载好,放到同一个目录下进行解压,下解压第一个文件,解压第二个文件的时候要选择替换该文件,因为还有一些新的文件要加入,不然在安装的过程中会发生报错,直接回滚不... 查看详情
收藏 0 赞 0 分享

Oracle三种循环(For、While、Loop)实现九九乘法表

本文为大家分享了Oracle中三种循环(For、While、Loop)案例,供大家参考,具体内容如下1.ORACLE中的FOR循环用法(九九乘法表)declarei int:=0;j int:=0;beginfor i in 1..9 loop for j in 1..i lo... 查看详情
收藏 0 赞 0 分享

Oracle 11g控制文件全部丢失从零开始重建控制文件

介绍控制文件(control file)是一个相当小的文件(最多能增长到64M左右),其中包含Oracle需要的其他文件的一个目录。参数文件告知实例控制文件的位置,控制文件则告知示例数据库和在线重做日志文件的位置。控制文件还告知了Oracle其他一些事情,如已发生检查点的有关信息... 查看详情
收藏 0 赞 0 分享

Oracle 11g收集多列统计信息详解

前言通常,当我们将SQL语句提交给Oracle数据库时,Oracle会选择一种最优方式来执行,这是通过查询优化器Query Optimizer来实现的。CBO(Cost-Based Optimizer)是Oracle默认使用的查询优化器模式。在CBO中,SQL执行计划的生成,是以... 查看详情
收藏 0 赞 0 分享
查看更多