Oracle12C--异常处理(二十九)

• 异常简介
  • PL/SQL程序分为编译型异常和运行时异常；
    • 对于编译型异常，用户没有办法进行处理，只能进行代码的修改；而运行时异常，用户可以使用EXCEPTION语句块来处理；
  • 编译型异常示例：程序的语法出现了错误所导致的异常

DECLARE v_result NUMBER := 1 ; BEGIN IF v_result = 1 -- 此处语法有错误，缺少THEN DBMS_OUTPUT.put_line('条件满足。') ; END IF ; END ; /

运行结果：错误报告: ORA-06550: 第 5 行, 第 3 列: PLS-00103: 出现符号 "DBMS_OUTPUT"在需要下列之一时： * & - + / at mod remainder rem then <an exponent (**)> and or || multiset 符号 "*" 被替换为 "DBMS_OUTPUT" 后继续。 ORA-06550: 第 5 行, 第 38 列: PLS-00103: 出现符号 ";"在需要下列之一时： . ( * % & - + / at mod remainder rem then <an exponent (**)> and or || 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

• 运行时异常：程序没有语法问题，但在运行时会因为程序运算或者返回结果而出现错误

DECLARE v_result NUMBER ; BEGIN v_result := 10/0 ; -- 被除数为0 END ; /

运行结果：错误报告: ORA-01476: 除数为 0 ORA-06512: 在 line 4 01476. 00000 - "divisor is equal to zero" *Cause: *Action:

• 使用EXCEPTION来处理异常
  • 在异常处理之前，首先要判断出现的是何种异常，处理格式如下：

WHEN 异常类型 | 用户定义异常 | 异常代码 | OTHERS THEN 异常处理；

在一个EXCEPTION语句块中可以同时编写多个WHEN，用于判断不能的异常类型

• 异常可以是系统预定义的，也可以用户自定义，或是异常编码；
  • 如果不知道异常的类型，可用others来捕捉任何异常；
• 示例1：处理被除数为零异常

DECLARE v_result NUMBER ; BEGIN v_result := 10/0 ; -- 被除数为0 DBMS_OUTPUT.put_line('异常之后的代码将不再执行！') ; EXCEPTION WHEN zero_divide THEN DBMS_OUTPUT.put_line('被除数不能为零。') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; END ; /

运行结果： 被除数不能为零。 SQLCODE = -1476 分析： 此例对上面的异常代码进行了修改，当出现异常时，会跳转到EXCEPTION部分，与每一个WHEN所设置的异常类型进行比较，比较成功后进行异常处理；

• 示例2：处理赋值异常

DECLARE v_varA VARCHAR2(1) ; v_varB VARCHAR2(4) := 'java' ; BEGIN v_varA := v_varB ; -- 错误的赋值 DBMS_OUTPUT.put_line('异常之后的代码将不再执行！') ; EXCEPTION WHEN value_error THEN DBMS_OUTPUT.put_line('数据赋值错误。') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; END ; / 运行结果： 数据赋值错误。 SQLCODE = -6502 分析： 此sql定义了两个varchar2变量，但第一个变量的范围小于第2个变量，所以将第二个变量的内容赋值给第一个变量时出现异常。对异常进行捕获后，控制台显示"数据赋值错误"；

通过这两个示例可以发现，程序出现了异常后，异常之后的代码将不再执行。而是跳转到了EXCEPTION中与与匹配WHEN异常捕获类型，如果类型匹配则使用指定的程序进行异常处理； 本操作如左图；

• 示例3：PL/SQL也可以针对SQL执行过程产生的异常进行处理；
  • 处理SQL异常 —— 找不到数据

DECLARE v_eno emp.empno%TYPE ; v_ename emp.ename%TYPE ; BEGIN v_eno := &empno ; -- 由键盘输入雇员编号 SELECT ename INTO v_ename FROM emp WHERE empno=v_eno ; DBMS_OUTPUT.put_line('编号为：' || v_eno || '雇员的名字为：' || v_ename) ; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有这个雇员！') ; END ; /

运行输入：6666 运行结果： 没有这个雇员！ 所捕获的异常是"no_data_found(数据没有发现异常)"

• 示例4：处理SQL异常 —— 返回多条结果

DECLARE v_dno emp.deptno%TYPE ; v_ename emp.ename%TYPE ; BEGIN v_dno := &deptno ; -- 由键盘输入部门编号 SELECT ename INTO v_ename FROM emp WHERE deptno=v_dno ; EXCEPTION WHEN too_many_rows THEN DBMS_OUTPUT.put_line('返回的数据过多！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; END ; /

运行输入：10 运行结果： 返回的数据过多！ SQLCODE = -1422 分析：由于雇员姓名是一个单独的变量，只能保存一个数值，返回的数据却是多条，所以会出现返回多行记录的异常 （too_many_rows）

• 示例5：使用others来捕获所有异常

DECLARE v_result NUMBER ; v_title VARCHAR2(50) := 'www .bing.cn' ; BEGIN v_result := v_title ; -- 此处出现异常 EXCEPTION WHEN others THEN DBMS_OUTPUT.put_line('返回的数据过多！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; /

运行结果： 返回的数据过多！ SQLCODE = -6502 SQLERRM = ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误 分析： 直接使用other进行捕获，捕获异常之后直接使用SQLCODE和SQLERRM分别打印异常代码和异常信息

• 用户自定义异常
  • Oracle自定义异常有两种方式：
    • 方式1：在声明块中声明EXCEPTION对象，此方式有两种选择
      • 选择1：声明异常对象并用名称来引用它，此方式使用普通的other异常捕获用户定义的异常；
      • 选择2：声明异常对象并将它与有效的Oracle错误代码映射，需要编写单独的WHEN语句块捕获；
    • 方式2：在执行块中构建动态异常。通过RAISE_APPLICATION_ERROR函数可以构建动态异常。在触发动态异常时，可使用-20000~-20999范围的数字。如果使用动态异常，可以在运行时指派错误信息；
  • 示例1：使用用户定义异常

DECLARE v_data NUMBER ; v_myexp EXCEPTION ; BEGIN v_data := &inputData ; IF v_data > 10 AND v_data < 100 THEN RAISE v_myexp ; -- 抛出异常 END IF ; EXCEPTION WHEN others THEN DBMS_OUTPUT.put_line('输入数据有错误！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; /

输入参数：60 运行结果： 输入数据有错误！ SQLCODE = 1 SQLERRM = User-Defined Exception 分析： 本sql采用声明异常对象的方式自定义用户异常，然后由用户输入一个数据。但判断条件满足后，使用RAISE手工进行用户异常的抛出。由于采用的声明式异常，直接使用others即可接收判断，并在默认情况下，所有用户定义的异常都只有SQLCODE，其内容为1 关于User-Defined Exception： 用户自定义异常的默认提示信息！ 为自定义异常添加名称，可以通过EXCEPTION_INIT编译完成

• 为自定义异常设置代码，语法如下：

PRAGMA EXCEPTION_INIT(异常名称，Oracle错误代码);

• 示例2：设置异常代码，更新示例1的代码

DECLARE v_data NUMBER ; v_myexp EXCEPTION ; PRAGMA EXCEPTION_INIT(v_myexp , -20789) ; BEGIN v_data := &inputData ; IF v_data > 10 AND v_data < 100 THEN RAISE v_myexp ; -- 抛出异常 END IF ; EXCEPTION WHEN v_myexp THEN DBMS_OUTPUT.put_line('输入数据有错误！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; /

输入参数：60 运行结果： 输入数据有错误！ SQLCODE = -20789 SQLERRM = ORA-20789: 分析： 因为没有定义错误信息，在输出异常时只有错误号 注意： 该SQL如果没有 黄色高亮语句，，依然可以通过myexp捕获异常，但是取得的SQLCODE依然为1

• 示例3：除了使用新的错误号，还可以利用此操作，将自定义异常绑定在一个已经存在的预定义异常的错误号上：

DECLARE v_myexp EXCEPTION ; v_input_rowid VARCHAR2(18) ; PRAGMA EXCEPTION_INIT(v_myexp , -01410) ; BEGIN v_input_rowid := '&inputRowid' ; -- 输入一个ROWID IF LENGTH(v_input_rowid) <> 18 THEN RAISE v_myexp ; END IF ; EXCEPTION WHEN v_myexp THEN DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; /

输入参数：60 运行结果： SQLCODE = -1410 SQLERRM = ORA-01410: 无效的 ROWID

• 将异常添加到异常列表（异常堆栈）中，语法如下（构建动态异常）：

RAISE_APPLICATION_ERROR(错误号，错误信息 [, 是否添加到错误堆栈])

语法参数说明： 错误号：只接受-20000~-20999范围的错误号，和声明的错误号一致； 错误信息：用于定义在使用SQLERRM输出时的错误提示信息； 是否添加到错误堆栈：如果设置为TRUE，则表示将错误添加到任意已有的错误堆栈，默认为FALSE，可选

• 示例1：构建动态异常

DECLARE v_data NUMBER ; v_myexp EXCEPTION ; -- 定义了一个异常变量 PRAGMA EXCEPTION_INIT(v_myexp , -20789) ; BEGIN v_data := &inputData ; -- 输入数据 IF v_data > 10 AND v_data < 100 THEN RAISE_APPLICATION_ERROR(-20789 , '输入数字不能在10 ~ 100之间！') ; END IF ; EXCEPTION WHEN v_myexp THEN -- 出现指定的异常 DBMS_OUTPUT.put_line('输入数据有错误！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; / 输入参数：60 运行结果： 输入数据有错误！ SQLCODE = -20789 SQLERRM = ORA-20789: 输入数字不能在10 ~ 100之间！

注意： 1.在构建动态异常时，语句RAISE_APPLICATION_ERROR(-20789 , '输入数字不能在10 ~ 100之间！') ;中的错误号，要与声明异常的错误号 PRAGMA EXCEPTION_INIT(v_myexp , -20789) ;保持一致，否则会出现语法错误； 2.如果此时捕获的不是异常变量，而使用other操作的话，那么即使不编写"PRAGMA EXCEPTION_INIT(v_myexp , -20789) ;"，语句也不会出现任何问题

示例：不声明异常变量，直接构建异常，同时使用others捕获 DECLARE v_data NUMBER ; v_myexp EXCEPTION ; -- 定义了一个异常变量 BEGIN v_data := &inputData ; -- 输入数据 IF v_data > 10 AND v_data < 100 THEN RAISE_APPLICATION_ERROR(-20789 , '输入数字不能在10 ~ 100之间！') ; END IF ; EXCEPTION WHEN others THEN -- 出现指定的异常 DBMS_OUTPUT.put_line('输入数据有错误！') ; DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ; DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; END ; / 输入参数：60 运行结果： 输入数据有错误！ SQLCODE = -20789 SQLERRM = ORA-20789: 输入数字不能在10 ~ 100之间！ 分析： 此sql可以正常执行，也就是说，如果捕获的是others，则可以忽略声明异常变量，绑定异常代码两个步骤，在异常处理要求不严格的情况下可以方便的被使用

• 综合范例
  • 需求：使用PL/SQL增加部门信息

DECLARE v_dno dept.deptno%TYPE ; -- 部门编号 v_dna dept.dname%TYPE ; -- 部门名称 v_dloc dept.loc%TYPE ; -- 部门位置 v_deptCount NUMBER ; -- 保存COUNT()函数结果 BEGIN v_dno := &inputDeptno ; -- 输入部门编号 v_dna := '&inputDname' ; -- 输入部门名称 v_dloc := '&inputLoc' ; -- 接收部门位置 -- 统计要增加的部门编号在dept表中的信息数量，如果返回0表示没有此部门 SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=v_dno ; IF v_deptCount > 0 THEN -- 部门存在 RAISE_APPLICATION_ERROR(-20888 , '此部门编号已存在，请重新输入！') ; ELSE -- 部门不存在 INSERT INTO dept(deptno,dname,loc) VALUES (v_dno,v_dna,v_dloc) ; DBMS_OUTPUT.put_line('新部门增加成功！') ; COMMIT ; END IF ; EXCEPTION WHEN others THEN DBMS_OUTPUT.put_line(SQLERRM) ; ROLLBACK ; END ; /

输入参数： 第一处：22 第二处：BING 第三处：广州 运行结果： 新部门增加成功！ 分析： 使用PL/SQL块进行dept数据的增加，在执行增加部门数据操作前， 首先判断要增加的部门编号是否存在（利用COUNT()函数统计个数来判断，为0表示不存在）， 如果存在则直接抛出一个异常，如果不存在，则执行增加操作，同时提交事务

• 附录：常见预定义异常