南大通用GBase 8a存储过程异常处理模块语法介绍和使用样例

发表于2021年1月6日2021年4月25日作者 laozizhu

GBase 8a数据库支持存储过程的异常处理，通过handler捕获异常，通过get DIAGNOSTIC获得异常详细信息，并提供continue和exit两种流程方案。本文介绍GBase 8a异常处理有关语法和样例。

本文对应版本：8.6.2Build43

目录导航

语法

捕获异常

DECLARE handler_type HANDLER FOR condition_value[,...] statement
handler_type:
 CONTINUE
 | EXIT
condition_value:
 SQLSTATE [VALUE] sqlstate_value
 | condition_name
 | SQLWARNING
 | NOT FOUND
 | SQLEXCEPTION
 | gbase_error_code

handler_type

CONTINUE：在处理器语句执行结束后，当前的程序继续执行下一行语句。

EXIT：当前声明declare handler的 BEGIN…END 复合语句的执行被终止，支持内内部嵌套的块。

condition_value

SQLSTATE [VALUE] sqlstate_value
SQL执行状态，比如SQLSTATE '42S02'

condition_name
预先设置的条件名字，类似自定义的别名。详情看本文专门介绍的章节。

SQLWARNING
告警

NOT FOUND
这是和游标上下文相关的，用于控制游标到达数据集末尾时发生的操作。如果没有更多的行可用，则会出现无数据条件，SQLSTATE值为“02000”。要检测此条件，可以为其或未找到条件设置处理程序。

SQLEXCEPTION
报错

gbase_error_code
特定的错误码

获取异常信息

通过 GET DIAGNOSTICS 获得异常发生时的信息。

GET [CURRENT | STACKED] DIAGNOSTICS
{
 statement_information_item [, statement_information_item] ...
 | CONDITION condition_number condition_information_item
 [, condition_information_item] ...
}
statement_information_item:
 target = statement_information_item_name
condition_information_item:
 target = condition_information_item_name
statement_information_item_name:
 NUMBER
 | ROW_COUNT
condition_information_item_name: {
 CLASS_ORIGIN
 | SUBCLASS_ORIGIN
 | RETURNED_SQLSTATE
 | MESSAGE_TEXT
 | GBASE_ERRNO
 | CONSTRAINT_CATALOG
 | CONSTRAINT_SCHEMA
 | CONSTRAINT_NAME
 | CATALOG_NAME
 | SCHEMA_NAME
 | TABLE_NAME
 | COLUMN_NAME
 | CURSOR_NAME
}

statement_information_item

statment 执行情况信息捕获反馈，包括 NUMBER、ROW_COUNT

number：number 中包含 WARNING 和错误的数量。

row_count：只记录 GET DIAGNOSTICS 命令之前最后一条 DML 操作的影响行数，不能累加，如果想获取多条 DML 语句的影响行数，需要在每条 DML 语句后执行 GET DIAGNOSTICS命令。

condition_information_item

捕获异常情况信息，如下3个是最常见的。

gbase_errno：记录错误号。

returned_sqlstate：记录错误状态。

message_text：记录错误信息。

定义异常条件信息的别名

DECLARE condition_name CONDITION FOR condition_value
condition_value:
 SQLSTATE [VALUE] sqlstate_value
 | gbase_error_code

condition_name：自定义的条件信息名字

condition_value：与declare handler的相同。

说明

GBase 8a的存储过程异常处理，只能在存储过程内部，通过declare声明要处理的异常内容。当异常发生时，最接近的上一级declare匹配并生效。在异常处理内部，可以通过get DIAGNOSTICS 获得异常发生时的上下文环境信息。

样例

最基本的

如下捕获所有SQL错误，并设置了一个报错的session变量信息。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
  declare exit handler for sqlexception 
  begin 
    set @errsing=1; 
  end;
end//
delimiter ;

自定义异常条件信息的别名

主要是自定义的名字更容易识别。如下定义了一个未知表名的条件的别名。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
    declare d_unknown_table condition for SQLSTATE '42S02';
    declare exit handler for d_unknown_table 
    begin 
        set @err_sign=1; 
    end;
    select * from t_not_exists;
end//
delimiter ;
call p_test();
select @err_sign;

执行效果如下

gbase> call p_test();
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select @err_sign;
+-----------+
| @err_sign |
+-----------+
|         1 |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

获得报错信息

如下样例，获得了错误的SQLSTATE，报错信息以及报错的错误号。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
    declare d_unknown_table condition for SQLSTATE '42S02';
    declare exit handler for d_unknown_table 
    begin 
        GET DIAGNOSTICS CONDITION 1
            @p1 = RETURNED_SQLSTATE, 
            @p2 = MESSAGE_TEXT,
            @p3=gbase_errno;
        set @err_sign=1; 
        
    end;
    select * from t_not_exists;
end//
delimiter ;
call p_test();
select @err_sign;
select @p1,@p2,@p3;

从执行效果看，对比直接执行的报错信息，拿到了我们预期的内容。

gbase> select * from t_not_exists;
ERROR 1146 (42S02): Table 'testdb.t_not_exists' doesn't exist

gbase> call p_test();
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select @err_sign;
+-----------+
| @err_sign |
+-----------+
|         1 |
+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select @p1,@p2,@p3;
+-------+-------------------------------------------+------+
| @p1   | @p2                                       | @p3  |
+-------+-------------------------------------------+------+
| 42S02 | Table 'testdb.t_not_exists' doesn't exist | 1146 |
+-------+-------------------------------------------+------+
1 row in set (Elapsed: 00:00:00.00)

游标到结果集末尾的NOT FOUND

如下查询2行数据后，不再有新的数据，打印输出。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
    declare done int DEFAULT 0;
    DECLARE a INT DEFAULT 0;
    DECLARE cur_1 CURSOR FOR SELECT * FROM t1 limit 2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur_1;
    REPEAT
      FETCH cur_1 INTO a;
  
      IF NOT done THEN
        select a;
      else
        select 'no more record';
      END IF;
    UNTIL done END REPEAT;

    CLOSE cur_1;

end//
delimiter ;
call p_test();

执行结果如下

gbase> call p_test();
+-------+
| a     |
+-------+
| 17400 |
+-------+
1 row in set (Elapsed: 00:00:00.01)

+-------+
| a     |
+-------+
| 31569 |
+-------+
1 row in set (Elapsed: 00:00:00.01)

+----------------+
| no more record |
+----------------+
| no more record |
+----------------+
1 row in set (Elapsed: 00:00:00.01)

Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase>

嵌套BEGIN END内层declare的处理

如下使用了一个内嵌的BEGIN END块，并声明了自己的异常处理。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
    declare done int DEFAULT 0;
    DECLARE a INT DEFAULT 0;
    select 'out begin';
    
    begin
        DECLARE cur_1 CURSOR FOR SELECT * FROM t1 limit 2;
        DECLARE Continue HANDLER FOR NOT FOUND SET done = TRUE;
        select 'Inner begin';
        OPEN cur_1;
        REPEAT
          FETCH cur_1 INTO a;
      
          IF NOT done THEN
            select a;
          else
            select 'no more record';
          END IF;
          
         
        UNTIL done END REPEAT;
        select 'Inner end';
        CLOSE cur_1;
    end;
    select 'out end';
end//
delimiter ;
call p_test();

执行结果如下：其中continue会继续执行,输出了Inner end

gbase> call p_test();
+-----------+
| out begin |
+-----------+
| out begin |
+-----------+
1 row in set (Elapsed: 00:00:00.02)

+-------------+
| Inner begin |
+-------------+
| Inner begin |
+-------------+
1 row in set (Elapsed: 00:00:00.02)

+-------+
| a     |
+-------+
| 17400 |
+-------+
1 row in set (Elapsed: 00:00:00.03)

+-------+
| a     |
+-------+
| 31569 |
+-------+
1 row in set (Elapsed: 00:00:00.03)

+----------------+
| no more record |
+----------------+
| no more record |
+----------------+
1 row in set (Elapsed: 00:00:00.04)

+-----------+
| Inner end |
+-----------+
| Inner end |
+-----------+
1 row in set (Elapsed: 00:00:00.04)

+---------+
| out end |
+---------+
| out end |
+---------+
1 row in set (Elapsed: 00:00:00.04)

Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase>

内层嵌套exit handler的执行结果

将如上例子的continue改成exit。

drop procedure if exists p_test;
delimiter //
create procedure p_test()
begin
    declare done int DEFAULT 0;
    DECLARE a INT DEFAULT 0;
    select 'out begin';
    
    begin
        DECLARE cur_1 CURSOR FOR SELECT * FROM t1 limit 2;
        DECLARE Exit HANDLER FOR NOT FOUND SET done = TRUE;
        select 'Inner begin';
        OPEN cur_1;
        REPEAT
          FETCH cur_1 INTO a;
      
          IF NOT done THEN
            select a;
          else
            select 'no more record';
          END IF;
          
         
        UNTIL done END REPEAT;
        select 'Inner end';
        CLOSE cur_1;
    end;
    select 'out end';
end//
delimiter ;
call p_test();

执行结果，Exit退出了内嵌的BEGIN END,在外层继续执行。

gbase> call p_test();
+-----------+
| out begin |
+-----------+
| out begin |
+-----------+
1 row in set (Elapsed: 00:00:00.04)

+-------------+
| Inner begin |
+-------------+
| Inner begin |
+-------------+
1 row in set (Elapsed: 00:00:00.04)

+-------+
| a     |
+-------+
| 17400 |
+-------+
1 row in set (Elapsed: 00:00:00.05)

+-------+
| a     |
+-------+
| 31569 |
+-------+
1 row in set (Elapsed: 00:00:00.05)

+---------+
| out end |
+---------+
| out end |
+---------+
1 row in set (Elapsed: 00:00:00.05)

Query OK, 0 rows affected (Elapsed: 00:00:00.05)

Post Views: 2,182

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

语法