错误捕获语句

缺省时，当PL/SQL函数执行过程中发生错误时退出函数执行，并且周围的事务也会回滚。可以用一个带有EXCEPTION子句的BEGIN块捕获错误并且从中恢复。其语法是正常的BEGIN块语法的一个扩展：

  1. [<<label>>]
  2. [DECLARE
  3.     declarations]
  4. BEGIN
  5.     statements
  6. EXCEPTION
  7.     WHEN condition [OR condition ...] THEN
  8.         handler_statements
  9.     [WHEN condition [OR condition ...] THEN
  10.         handler_statements
  11.     ...]
  12. END;

如果没有发生错误，这种形式的块儿只是简单地执行所有语句，然后转到END之后的下一个语句。但是如果在执行的语句内部发生了一个错误，则这个语句将会回滚，然后转到EXCEPTION列表。寻找匹配错误的第一个条件。若找到匹配，则执行对应的handler_statements，然后转到END之后的下一个语句。如果没有找到匹配，则会向事务的外层报告错误，和没有EXCEPTION子句一样。错误码可以捕获同一类的其他错误码。

也就是说该错误可以被一个包围块用EXCEPTION捕获，如果没有包围块，则进行退出函数处理。

condition的名称可以是SQL标准错误码编号说明的任意值。特殊的条件名OTHERS匹配除了QUERY_CANCELED之外的所有错误类型。

如果在选中的handler_statements里发生了新错误，则不能被这个EXCEPTION子句捕获，而是向事务的外层报告错误。一个外层的EXCEPTION子句可以捕获它。

如果一个错误被EXCEPTION捕获，PL/SQL函数的局部变量保持错误发生时的原值，但是所有该块中想写入数据库中的状态都回滚。

示例：

  1. CREATE TABLE mytab(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) ;
  3. INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
  5. CREATE FUNCTION fun_exp() RETURNS INT
  6. AS $$
  7. DECLARE
  8.     x INT :=0;
  9.     y INT;
  10. BEGIN
  11.     UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
  12.     x := x + 1;
  13.     y := x / 0;
  14. EXCEPTION
  15.     WHEN division_by_zero THEN
  16.         RAISE NOTICE 'caught division_by_zero';
  17.         RETURN x;
  18. END;$$
  19. LANGUAGE plpgsql;
  21. call fun_exp();
  22. NOTICE:  caught division_by_zero
  23.  fun_exp 
  24. ---------
  25.        1
  26. (1 row)
  28. select * from mytab;
  29.  id | firstname | lastname 
  30. ----+-----------+----------
  31.     | Tom       | Jones
  32. (1 row)
  34. DROP FUNCTION fun_exp();
  35. DROP TABLE mytab;

当控制到达给y赋值的地方时，会有一个division_by_zero错误失败。这个错误将被EXCEPTION子句捕获。而在RETURN语句里返回的数值将是x的增量值。

错误捕获语句 - 图1 说明：
进入和退出一个包含EXCEPTION子句的块要比不包含的块开销大的多。因此，不必要的时候不要使用EXCEPTION。
在下列场景中，无法捕获处理异常，整个存储过程回滚：节点故障、网络故障引起的存储过程参与节点线程退出以及COPY FROM操作中源数据与目标表的表结构不一致造成的异常。

示例 ：UPDATE/INSERT异常

这个例子根据使用异常处理器执行恰当的UPDATE或INSERT 。

  1. CREATE TABLE db (a INT, b TEXT);
  3. CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
  4. $$
  5. BEGIN
  6.     LOOP
  8. --第一次尝试更新key
  9.         UPDATE db SET b = data WHERE a = key;
  10.         IF found THEN
  11.             RETURN;
  12.         END IF;
  13. --不存在，所以尝试插入key，如果其他人同时插入相同的key，我们可能得到唯一key失败。
  14.         BEGIN
  15.             INSERT INTO db(a,b) VALUES (key, data);
  16.             RETURN;
  17.         EXCEPTION WHEN unique_violation THEN
  18.         --什么也不做，并且循环尝试再次更新。
  19.         END;
  20.      END LOOP;
  21. END;
  22. $$
  23. LANGUAGE plpgsql;
  25. SELECT merge_db(1, 'david');
  26. SELECT merge_db(1, 'dennis');
  28. --删除FUNCTION和TABLE
  29. DROP FUNCTION merge_db;
  30. DROP TABLE db;

B模式下新增declare声明的错误捕获语法。

功能描述

与exception语法相同都是在执行语句内部发生错误时触发异常处理寻找匹配错误的第一个条件。若找到匹配，则执行对应的statement，否则抛出异常。与exception语法不同的是declare有两种行为，一种是exit，在声明exit异常处理语句并匹配到错误条件时会将已经正确执行的语句提交，在执行完声明的异常处理statement后退出；另一种行为是continue，与exit行为不同在执行语句发生错误触发异常处理语句并匹配到错误条件时会跳过错误语句，继续执行剩余语句。

语法格式

  1. BEGIN
  2.     DECLARE handler_action HANDLER
  3.     FOR condition_value [, condition_value] ...
  4.     statement
  5.     statements
  6. END;

参数说明

  • handler_action

    触发异常处理条件后的行为。

    • continue：触发声明的异常条件时执行statement，跳过执行异常SQL并继续执行。
    • exit：触发声明的异常条件时执行statement，提交已执行SQL并退出。
    • undo：暂不支持。

错误捕获语句 - 图2说明：

由于OG异常处理机制与M*数据库不同，只在SQL执行发生异常时才进行异常处理，产生warning等不进行异常处理。

  • condition_value
    • mysql_error_code：除0和1之外的整型数字。
    • SQLSTATE [VALUE] sqlstate_value：sqlstate错误码。
    • condition_name：条件名。
    • SQLWARNING：’01’开头的sqlstate错误码。
    • NOT FOUND：’02’开头的sqlstate错误码。
    • SQLEXCEPTION：除’01’,’02’开头的其他错误码。

示例

  1. create table declare_handler_t_continue (i INT PRIMARY KEY, j INT);
  2. create table declare_handler_t_exit (i INT PRIMARY KEY, j INT);
  4. CREATE OR REPLACE PROCEDURE proc_ex()  IS
  5. BEGIN
  6.     DECLARE EXIT HANDLER FOR unique_violation
  7.         RAISE NOTICE 'unique_violation HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;
  9.     INSERT INTO declare_handler_t_exit VALUES (1, 1);
  10.     INSERT INTO declare_handler_t_exit VALUES (2, 1);
  11.     INSERT INTO declare_handler_t_exit VALUES (1, 1); /* duplicate key */
  12.     INSERT INTO declare_handler_t_exit VALUES (3, 1);
  13. END;
  14. /
  15. call proc_ex();
  16. NOTICE:  unique_violation HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_exit_pkey"
  17.  proc_ex 
  18. ---------
  20. (1 row)
  22. SELECT * FROM declare_handler_t_exit ORDER BY i;
  23.  i | j 
  24. ---+---
  25.  1 | 1
  26.  2 | 1
  27. (2 rows)
  29. CREATE OR REPLACE PROCEDURE proc_continue_sqlexception()  IS
  30. BEGIN
  31.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  32.         RAISE NOTICE 'SQLEXCEPTION HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;
  34.     INSERT INTO declare_handler_t_continue VALUES (1, 1);
  35.     INSERT INTO declare_handler_t_continue VALUES (2, 1);
  36.     RAISE division_by_zero;
  37.     INSERT INTO declare_handler_t_continue VALUES (1, 1);
  38.     INSERT INTO declare_handler_t_continue VALUES (3, 1);
  39. END;
  40. /
  41. call proc_continue_sqlexception();
  42. NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 22012, SQLERRM = division_by_zero
  43. NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_continue_pkey"
  44.  proc_continue_sqlexception 
  45. ----------------------------
  47. (1 row)
  49. SELECT * FROM declare_handler_t_continue ORDER BY i;
  50.  i | j 
  51. ---+---
  52.  1 | 1
  53.  2 | 1
  54.  3 | 1
  55. (3 rows)