错误捕获语句

错误捕获语句

缺省时，当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的增量值。

说明： 进入和退出一个包含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;