南大通用数据库-Gbase-8a-学习-28-自定义存储过程之异常捕获

一、测试环境

二、语法

1、DECLARE … HANDLER Statement语法树

参考文章链接：
MySql8官方文档之13.6.7.2 DECLARE … HANDLER Statement

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（1）handler_type参数

（2）condition_value参数

2、GET DIAGNOSTICS Statement语法树

参考文章链接：
MySql8官方文档之13.6.7.3 GET DIAGNOSTICS Statement

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
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

condition_number, target:
    (see following discussion)
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
31
32
33
34
35
36
37

三、实现：输入SQL，返回影响行数、错误码、状态、信息

DROP PROCEDURE IF EXISTS ProExecuteSqlQuery;

DELIMITER |
CREATE PROCEDURE ProExecuteSqlQuery(InputSqlText varchar(4000))
BEGIN
    DECLARE VarSqlText varchar(4000);
    DECLARE EffectRowNum INT;
    DECLARE ErrNo varchar(50);
    DECLARE ErrState varchar(50);
    DECLARE ErrMessage varchar(1000);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	
    BEGIN
        GET DIAGNOSTICS CONDITION 1 
		ErrNo=gbase_errno,ErrState=returned_sqlstate,ErrMessage=message_text;
		IF ErrNo != 1243 THEN
		    SELECT ErrNo,ErrState,ErrMessage;
        END IF;
    END;
	
	SET @VarSqlText = InputSqlText;
	PREPARE STMT FROM @VarSqlText;
	EXECUTE STMT;
	
	GET DIAGNOSTICS EffectRowNum = ROW_COUNT;
	DEALLOCATE PREPARE STMT;
	
    SELECT EffectRowNum;
END |
DELIMITER ;
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

四、实验

1、DQL

受影响行数只返回DML语句。

gbase> CALL ProExecuteSqlQuery('select * from SUN');               
Empty set (Elapsed: 00:00:00.02)

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

Query OK, 0 rows affected (Elapsed: 00:00:00.02)
1
2
3
4
5
6
7
8
9
10
11

2、DML

（1）INSERT

gbase> CALL ProExecuteSqlQuery('insert into sun values(1),(2);');    
+--------------+
| EffectRowNum |
+--------------+
|            2 |
+--------------+
1 row in set (Elapsed: 00:00:00.03)

Query OK, 0 rows affected (Elapsed: 00:00:00.03)
1
2
3
4
5
6
7
8
9

（2）UPDATE

gbase> CALL ProExecuteSqlQuery('update sun set di =1 where di = 2');
+--------------+
| EffectRowNum |
+--------------+
|            1 |
+--------------+
1 row in set (Elapsed: 00:00:00.04)

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

1
2
3
4
5
6
7
8
9
10

（3）DELETE

gbase> CALL ProExecuteSqlQuery('DELETE FROM SUN');                    
+--------------+
| EffectRowNum |
+--------------+
|            2 |
+--------------+
1 row in set (Elapsed: 00:00:00.04)

Query OK, 0 rows affected (Elapsed: 00:00:00.04)
1
2
3
4
5
6
7
8
9

3、错误语句

gbase> CALL ProExecuteSqlQuery('DELETE FROM haha'); 
+-------+----------+--------------------------------+
| ErrNo | ErrState | ErrMessage                     |
+-------+----------+--------------------------------+
| 1146  | 42S02    | Table 'czg.haha' doesn't exist |
+-------+----------+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

+--------------+
| EffectRowNum |
+--------------+
|           -1 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

Query OK, 0 rows affected (Elapsed: 00:00:00.00)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16