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

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

阳光九叶草LXGZXJ

1351人浏览 · 2022-11-08 15:13:34

阳光九叶草LXGZXJ · 2022-11-08 15:13:34 发布

一、测试环境

名称	值
cpu	12th Gen Intel® Core™ i7-12700H
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	3G
逻辑核数	2
Gbase-8a数据库版本	9.5.3.27

二、语法

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）handler_type参数

参数名称	描述
CONTINUE	当前子程序的执行在执行处理程序语句之后继续。
EXIT	当前BEGIN…END复合语句的执行被终止。
UNDO	不支持

（2）condition_value参数

参数名称	描述	备注
mysql_error_code	指示 MySQL 错误代码的整数文本，例如 1051 以指定“未知表”	Gbase8a不一定叫这个名字
SQLSTATE [VALUE] sqlstate_value	指示 SQLSTATE 值的 5 个字符的字符串文本，例如“42S01”以指定“未知表”
condition_name	以前使用指定的条件名称 DECLARE … CONDITION。条件名称可以与 MySQL 错误代码或 SQLSTATE 值相关联
SQLWARNING	以“01”开头的 SQLSTATE 值类的简写。
NOT FOUND	以“02”开头的 SQLSTATE 值类的简写。这在游标的上下文中是相关的，用于控制当游标到达数据集末尾时发生的情况。如果没有更多行可用，则会出现 SQLSTATE 值为“02000”的“无数据”情况。若要检测此条件，可以为其设置处理程序或 NOT FOUND 条件。
SQLEXCEPTION	不以“00”、“01”或“02”开头的 SQLSTATE 值类的简写。

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)

三、实现：输入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、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)

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)

（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)

（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)

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)