一、测试环境
名称 | 值 |
---|---|
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)