MySQL/MariaDB存储过程带返回参数的运行和错误捕获
一、存储过程定义
定义了如下内容的存储过程,用于简单示例错误捕获的写法:
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateScore`(
IN `uid` INT,
IN `setScore` FLOAT,
OUT `setOk` TINYINT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE hasError tinyINT DEFAULT 0;
DECLARE CONTINUE handler FOR SQLEXCEPTION,NOT FOUND,SQLWARNING SET hasError=1;
START transaction;
UPDATE users SET UserScores =setScore WHERE UserId=uid;
/* SELECT userid INTO setok FROM users WHERE UserId=uid;*/
IF hasError THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
SET setOk = hasError;
END
其中:
(1)setOk参数为OUT,会返回该值,运行时前面要用@符合
(2)下述语句用于定义执行SQL语句时的错误捕获:
DECLARE hasError tinyINT DEFAULT 0;
DECLARE CONTINUE handler FOR SQLEXCEPTION,NOT FOUND,SQLWARNING SET hasError=1;
注意:NOT FOUND代表以’02’开头的SQLSTATE,仅仅对select语句和游标有效!
因此,上面的存储过程中,update不存在的UserId时,并不会产生NOT FOUND捕获,如果需要判断是否存在UserId,需要用select语句或者update后调用ROW_COUNT()检查语句影响的记录数!
DECLARE的具体语法是:
DECLARE handler_type HANDLER
FOR condition_value [, condition_value] ...
statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mariadb_error_code
详细语法说明可参考:
https://mariadb.com/kb/zh-cn/declare-handler/
二、运行存储过程
在SQL查询窗口中运行该存储过程。
例如将UserId=3的分数改为98,采用如下语句:
CALL `updateScore`(3,98, @hasErrors);
SELECT @hasErrors;
运行的返回结果如下:
这里:不论是否真正发生替换(影响到的具体记录数是否>0),都会返回0,要检查发生替换的记录数,要用ROW_COUNT()返回值。