Oracle兼容-存储过程-Exception Handler

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

  1. 异常处理 Exception Handler

  2. 异常声明 Exception Declaration

  3. 抛出异常 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_FOUNDYER_SP_FETCH_NO_DATA
INVALID_CURSORYER_SP_CURSOR_NOT_OPEN
DUP_VAL_ON_INDEXYER_DUP_ENTRY
DUP_VAL_ON_INDEXYER_DUP_ENTRY_WITH_KEY_NAME
TOO_MANY_ROWSYER_TOO_MANY_ROWS
INVALID_NUMBERYER_DATA_OUT_OF_RANGE
CURSOR_ALREADY_OPENYER_SP_CURSOR_ALREADY_OPEN
VALUE_ERRORYER_WRONG_VALUE
STORAGE_ERRORYER_GET_ERRNO
ZERO_DIVIDEYER_DIVISION_BY_ZERO
ACCESS_INTO_NULLN
CASE_NOT_FOUNDN
COLLECTION_IS_NULLN
LOGIN_DENIEDN
NO_DATA_NEEDEDN
NOT_LOGGED_ONN
PROGRAM_ERRORN
ROWTYPE_MISMATCHN
SELF_IS_NULLN
SUBSCRIPT_BEYOND_COUNTN
SUBSCRIPT_OUTSIDE_LIMITN
SYS_INVALID_ROWIDN
TIMEOUT_ON_RESOURCEN

更多预定义的异常错误信息，详见参考：Server Error Message Reference异常处理 EXCEPTION HANDLER - 图1 (opens new window)

关于Oracle异常错误，详见参考：Exception Declaration异常处理 EXCEPTION HANDLER - 图2 (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 定义

  1. 自定义异常名称。只能在 EXCEPTION_INIT PRAGMA、RAISE、RAISE_APPLICATION_ERROR 调用或异常处理程序中使用异常。
  1. exception_name EXCEPTION
  1. 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异常处理 EXCEPTION HANDLER - 图5 (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_CODESMALLINT UNSIGNED
MESSAGEVARCHAR(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

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx