Oracle兼容-存储过程-Exception Handler

Oracle兼容-存储过程-Exception Handler

在存储过程/存储函数中，当发生异常时，支持以下几种不同处理方式：

异常处理 Exception Handler
异常声明 Exception Declaration
抛出异常 Exception Raise

1. 异常处理 `Exception Handler`

在存储过程/存储函数中，发生异常的原因可能来自于设计错误、编码错误、硬件故障及其他，无法预测所有可能的异常。

但可以针对这些状况，编写异常处理程序，让程序在异常发生时仍可继续运行。

任何语句块都可以有一个异常处理部分，可以有一个或多个异常处理程序。

1.1 语法

1. 1. WHEN { exception [ OR exception2 ]... | OTHERS }
2. THEN handler_statement [ handler_statement2 ]...
3. END;

5. 2. EXCEPTION
6. WHEN exception THEN handler_statement                    -- Exception handler1
7. WHEN exception2 OR exception3 THEN handler_statement2    -- Exception handler2
8. WHEN OTHERS THEN handler_statement3                      -- Exception handler3
9. END;

1.2 定义和用法

exception：异常名，预定义的异常（见下方表格）或用户定义的异常的名称。
OTHERS：其他异常，在代码块异常处理部分没有明确指定的其他所有异常。
handler_statement：异常处理语句，当触发了异常，将会执行指定的处理语句。
已预定义好的异常名（表格1）：

Oracle中的异常名	GreatSQL是否支持	映射到GreatSQL中对应的异常名
NO_DATA_FOUND	Y	ER_SP_FETCH_NO_DATA
INVALID_CURSOR	Y	ER_SP_CURSOR_NOT_OPEN
DUP_VAL_ON_INDEX	Y	ER_DUP_ENTRY
DUP_VAL_ON_INDEX	Y	ER_DUP_ENTRY_WITH_KEY_NAME
TOO_MANY_ROWS	Y	ER_TOO_MANY_ROWS
INVALID_NUMBER	Y	ER_DATA_OUT_OF_RANGE
CURSOR_ALREADY_OPEN	Y	ER_SP_CURSOR_ALREADY_OPEN
VALUE_ERROR	Y	ER_WRONG_VALUE
STORAGE_ERROR	Y	ER_GET_ERRNO
ZERO_DIVIDE	Y	ER_DIVISION_BY_ZERO
ACCESS_INTO_NULL	N
CASE_NOT_FOUND	N
COLLECTION_IS_NULL	N
LOGIN_DENIED	N
NO_DATA_NEEDED	N
NOT_LOGGED_ON	N
PROGRAM_ERROR	N
ROWTYPE_MISMATCH	N
SELF_IS_NULL	N
SUBSCRIPT_BEYOND_COUNT	N
SUBSCRIPT_OUTSIDE_LIMIT	N
SYS_INVALID_ROWID	N
TIMEOUT_ON_RESOURCE	N

更多预定义的异常错误信息，详见参考：Server Error Message Reference (opens new window)

关于Oracle异常错误，详见参考：Exception Declaration (opens new window)

提示：错误号本身不能兼容Oracle，因为它和GreatSQL为各自体系错误信息，不存在复用。

2. 异常声明 `Exception Declaration`

2.1 语法

1. 1. exception_name EXCEPTION

3. 2. PRAGMA EXCEPTION_INIT ( EXCEPTION, ERROR_CODE ) ;

2.2 定义

自定义异常名称。只能在 EXCEPTION_INIT PRAGMA、RAISE、RAISE_APPLICATION_ERROR 调用或异常处理程序中使用异常。

1. exception_name EXCEPTION

EXCEPTION_INIT pragma 将此名称分配给内部定义的异常。

1. PRAGMA EXCEPTION_INIT ( EXCEPTION, ERROR_CODE ) ;

关于 EXCEPTION_INIT 的用法可参考下方示例4、示例6。

参考：

3. 抛出异常

3.1 `RAISE`

3.1.1 语法

1. RAISE [ exception ]

3.1.2 定义

可以调用 RAISE 主动触发抛出异常，在异常处理程序之外，必须指定异常名称。

在异常处理程序中，如果省略异常名称，则该 RAISE 语句会重新引发当前异常。

参数 exception 可以是以下两种
1. 预定义异常名称（见上方所示表格1）。
1. 自定义声明的异常名。

调用 RAISE 主动抛出异常的行为等同于 SIGNAL Statement，可用的溢出与禁止使用的错误号详见文档：SIGNAL Statement (opens new window)。

关于 RAISE exception 的用法可参考下方示例3、示例4。

3.2 RAISE_APPLICATION_ERROR

3.2.1 语法

1. RAISE_APPLICATION_ERROR (ERROR_CODE, MESSAGE)

3.2.2 定义

利用 RAISE_APPLICATION_ERROR 可以主动抛出自定义错误。

参数	定义
ERROR_CODE	SMALLINT UNSIGNED
MESSAGE	VARCHAR(128)

关于 RAISE_APPLICATION_ERROR 的用法可参考下方示例5。

4. 示例

创建测试表并初始化数据

1. greatsql> CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(20) NOT NULL);
2. greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2'), (3,'row3') ;

1. 示例1，使用预定义异常 TOO_MANY_ROWS、NO_DATA_FOUND

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp1(n INT, ret OUT VARCHAR(50)) AS
5. a INT;
6. BEGIN
7. SELECT a INTO a FROM t1 LIMIT n;

9. EXCEPTION
10. WHEN TOO_MANY_ROWS THEN
11. SET ret = '--- TOO_MANY_ROWS cought ---';
12. WHEN NO_DATA_FOUND THEN
13. SET ret = '--- NO_DATA_FOUND cought ---';
14. END; //

16. greatsql> SET @ret = ''; CALL exception_sp1(0, @ret); SELECT @ret //
17. Query OK, 0 rows affected (0.00 sec)

19. Query OK, 0 rows affected (0.00 sec)

21. +------------------------------+
22. | @ret                         |
23. +------------------------------+
24. | --- NO_DATA_FOUND cought --- |
25. +------------------------------+
26. 1 row in set (0.00 sec)

28. greatsql>
29. greatsql> SET @ret = ''; CALL exception_sp1(1, @ret); SELECT @ret //
30. Query OK, 0 rows affected (0.00 sec)

32. Query OK, 1 row affected (0.00 sec)

34. +------+
35. | @ret |
36. +------+
37. | NULL |
38. +------+
39. 1 row in set (0.00 sec)

41. greatsql>
42. greatsql> SET @ret = ''; CALL exception_sp1(2, @ret); SELECT @ret //
43. Query OK, 0 rows affected (0.00 sec)

45. Query OK, 0 rows affected (0.00 sec)

47. +------------------------------+
48. | @ret                         |
49. +------------------------------+
50. | --- TOO_MANY_ROWS cought --- |
51. +------------------------------+
52. 1 row in set (0.00 sec)

1. 示例2，处理 OTHERS 异常

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp2(ret OUT VARCHAR(50)) AS
5. BEGIN
6. SELECT 1/0 ;

8. EXCEPTION
9. WHEN ZERO_DIVIDE THEN
10. SET ret = '--- ZERO_DIVIDE cought ---';
11. WHEN OTHERS THEN
12. SET ret = '--- others cought ---';
13. END; //

15. greatsql> SET @ret = ''; CALL exception_sp2(@ret); SELECT @ret //
16. Query OK, 0 rows affected (0.00 sec)

18. Empty set (0.00 sec)

20. Query OK, 0 rows affected (0.00 sec)

22. +----------------------------+
23. | @ret                       |
24. +----------------------------+
25. | --- ZERO_DIVIDE cought --- |
26. +----------------------------+
27. 1 row in set (0.00 sec)

1. 示例3，调用 RAISE 抛出异常

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp3() AS
5. BEGIN
6. BEGIN
7. SELECT 'Hi GreatSQL';
8. -- 通过RAISE直接抛出异常
9. RAISE TOO_MANY_ROWS;

11. EXCEPTION
12. WHEN OTHERS THEN
13. SELECT 'GET RAISE EXCEPTION FROM OTHERS AND RAISE' AS ret;
14. -- 并且抛出 TOO_MANY_ROWS 异常
15. RAISE;
16. END;

18. -- 捕获主动定义的TOO_MANY_ROWS异常
19. EXCEPTION
20. WHEN TOO_MANY_ROWS THEN
21. SELECT 'GET TOO_MANY_ROWS FROM MANUALLY RAISE' AS ret;
22. END; //

24. greatsql> CALL exception_sp3() //
25. +-------------+
26. | Hi GreatSQL |
27. +-------------+
28. | Hi GreatSQL |
29. +-------------+
30. 1 row in set (0.00 sec)

32. +-------------------------------------------+
33. | ret                                       |
34. +-------------------------------------------+
35. | GET RAISE EXCEPTION FROM OTHERS AND RAISE |
36. +-------------------------------------------+
37. 1 row in set (0.00 sec)

39. +---------------------------------------+
40. | ret                                   |
41. +---------------------------------------+
42. | GET TOO_MANY_ROWS FROM MANUALLY RAISE |
43. +---------------------------------------+
44. 1 row in set (0.00 sec)

46. Query OK, 0 rows affected (0.00 sec)

1. 示例4，自定义异常

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp4() AS
5. a EXCEPTION;
6. BEGIN
7. DECLARE
8. c EXCEPTION;
9. BEGIN
10. RAISE c;
11. EXCEPTION
12. WHEN c THEN
13. SELECT 'GET c RAISE 1' AS ret;
14. RAISE;
15. END;

17. EXCEPTION
18. WHEN a THEN
19. SELECT 'GET a RAISE 2 IS FAILED' AS ret;
20. WHEN OTHERS THEN
21. SELECT 'GET a RAISE OTHERS IS FAILED' AS ret;
22. END; //

25. greatsql> CALL exception_sp4() //
26. +---------------+
27. | ret           |
28. +---------------+
29. | GET c RAISE 1 |
30. +---------------+
31. 1 row in set (0.00 sec)

33. +------------------------------+
34. | ret                          |
35. +------------------------------+
36. | GET a RAISE OTHERS IS FAILED |
37. +------------------------------+
38. 1 row in set (0.00 sec)

40. Query OK, 0 rows affected (0.00 sec)

1. 示例5，自定义异常与消息

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp5() AS
5. BEGIN
6. RAISE_APPLICATION_ERROR(33306, 'GreatSQL exception in sp');
7. END; //

9. greatsql> CALL exception_sp5() //
10. ERROR 33306 (HY000): GreatSQL exception in sp

1. 示例6，异常绑定错误号

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE exception_sp6(n INT) AS
5. a EXCEPTION;
6. b INT;

8. PRAGMA EXCEPTION_INIT(a, 1329);
9. BEGIN
10. SELECT a INTO b FROM t1 LIMIT n;
11. EXCEPTION
12. WHEN a THEN
13. SELECT '--NO_DATA_FOUND--' AS ret;

15. WHEN OTHERS THEN
16. SELECT 'GET OTHERS' AS ret;
17. RAISE;
18. END; //

20. greatsql> CALL exception_sp6(0) //
21. +-------------------+
22. | ret               |
23. +-------------------+
24. | --NO_DATA_FOUND-- |
25. +-------------------+
26. 1 row in set (0.00 sec)

28. Query OK, 0 rows affected (0.00 sec)

30. greatsql> CALL exception_sp6(1) //
31. Query OK, 1 row affected (0.00 sec)

33. greatsql>
34. greatsql> CALL exception_sp6(2) //
35. +------------+
36. | ret        |
37. +------------+
38. | GET OTHERS |
39. +------------+
40. 1 row in set (0.00 sec)

42. ERROR 1172 (42000): Result consisted of more than one row

问题反馈

问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号

异常处理 EXCEPTION HANDLER