DELIMITER $$ DROP PROCEDURE IF EXISTS XXXXXX$$ CREATE PROCEDURE XXXXXX(IN _uuid VARCHAR(36),_yong_hu_id VARCHAR(36),IN _toubiao_jing_e DECIMAL(20,5)) BEGIN DECLARE dqjke DECIMAL(20,5) DEFAULT 0.00000; DECLARE biao_jing_e DECIMAL(20,5) DEFAULT 0.00000; DECLARE raise_zhang_hu_jing_e DECIMAL(20,5) DEFAULT 0.00000; START TRANSACTION; -- 阿德算法三方的阿斯顿发 SELECT dang_qian_jie_kuan_e FROM tb_biao WHERE UUID = _uuid INTO dqjke; SET dqjke = dqjke + _toubiao_jing_e; UPDATE tb_biao SET dang_qian_jie_kuan_e = dqjke;
-- 阿斯顿发斯蒂芬 INSERT INTO tb_toubiao_jilu (biao_uuid,yong_hu_id,jing_e)VALUES(_uuid,_yong_hu_id,_jing_e);
-- 阿斯顿发速度 SET raise_zhang_hu_jing_e = 0 - _toubiao_jing_e; CALL raiseZhangHuZongE(raise_zhang_hu_jing_e,_yong_hu_id);
-- 阿斯顿发斯蒂芬 SELECT jing_e FROM tb_biao WHERE UUID = _uuid INTO biao_jing_e; IF (dqjke >= biao_jing_e) THEN UPDATE tb_biao SET biao_status = 'success' WHERE UUID = _uuid; END IF; COMMIT; END$$ DELIMITER ;
start transaction; sql..... commit;
DELIMITER $$USE `aa`$$DROP PROCEDURE IF EXISTS `XXXXXX`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `XXXXXX`(IN _uuid VARCHAR(36),_yong_hu_id VARCHAR(36),IN _toubiao_jing_e DECIMAL(20,5)) BEGIN DECLARE dqjke DECIMAL(20,5) DEFAULT 0.00000; DECLARE biao_jing_e DECIMAL(20,5) DEFAULT 0.00000; DECLARE raise_zhang_hu_jing_e DECIMAL(20,5) DEFAULT 0.00000; DECLARE errinfo INT; DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1; START TRANSACTION; -- 阿德算法三方的阿斯顿发 SELECT dang_qian_jie_kuan_e FROM tb_biao WHERE UUID = _uuid INTO dqjke; SET dqjke = dqjke + _toubiao_jing_e; UPDATE tb_biao SET dang_qian_jie_kuan_e = dqjke;
-- 阿斯顿发斯蒂芬 INSERT INTO tb_toubiao_jilu (biao_uuid,yong_hu_id,jing_e)VALUES(_uuid,_yong_hu_id,_jing_e);
-- 阿斯顿发速度 SET raise_zhang_hu_jing_e = 0 - _toubiao_jing_e; CALL raiseZhangHuZongE(raise_zhang_hu_jing_e,_yong_hu_id);
-- 阿斯顿发斯蒂芬 SELECT jing_e FROM tb_biao WHERE UUID = _uuid INTO biao_jing_e; IF (dqjke >= biao_jing_e) THEN UPDATE tb_biao SET biao_status = 'success' WHERE UUID = _uuid; END IF; IF errinfo=1 THEN ROLLBACK; ELSE COMMIT; END IF;
END$$DELIMITER ;
可不可以解释一下这一句啊?: DECLARE errinfo INT; DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1;
看了些资料, 这句异常23000是遇到重复关键字。 我想把这个过程写得更严谨一些。遇到任何异常(错误)都回滚。请问这个23000应该改成什么值? DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1;
DROP PROCEDURE IF EXISTS XXXXXX$$
CREATE PROCEDURE XXXXXX(IN _uuid VARCHAR(36),_yong_hu_id VARCHAR(36),IN _toubiao_jing_e DECIMAL(20,5))
BEGIN DECLARE dqjke DECIMAL(20,5) DEFAULT 0.00000;
DECLARE biao_jing_e DECIMAL(20,5) DEFAULT 0.00000;
DECLARE raise_zhang_hu_jing_e DECIMAL(20,5) DEFAULT 0.00000;
START TRANSACTION;
-- 阿德算法三方的阿斯顿发
SELECT dang_qian_jie_kuan_e FROM tb_biao WHERE UUID = _uuid INTO dqjke;
SET dqjke = dqjke + _toubiao_jing_e;
UPDATE tb_biao SET dang_qian_jie_kuan_e = dqjke;
-- 阿斯顿发斯蒂芬
INSERT INTO tb_toubiao_jilu (biao_uuid,yong_hu_id,jing_e)VALUES(_uuid,_yong_hu_id,_jing_e);
-- 阿斯顿发速度
SET raise_zhang_hu_jing_e = 0 - _toubiao_jing_e;
CALL raiseZhangHuZongE(raise_zhang_hu_jing_e,_yong_hu_id);
-- 阿斯顿发斯蒂芬
SELECT jing_e FROM tb_biao WHERE UUID = _uuid INTO biao_jing_e;
IF (dqjke >= biao_jing_e) THEN
UPDATE tb_biao SET biao_status = 'success' WHERE UUID = _uuid;
END IF;
COMMIT;
END$$
DELIMITER ;
sql.....
commit;
BEGIN
DECLARE dqjke DECIMAL(20,5) DEFAULT 0.00000;
DECLARE biao_jing_e DECIMAL(20,5) DEFAULT 0.00000;
DECLARE raise_zhang_hu_jing_e DECIMAL(20,5) DEFAULT 0.00000;
DECLARE errinfo INT;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1;
START TRANSACTION;
-- 阿德算法三方的阿斯顿发
SELECT dang_qian_jie_kuan_e FROM tb_biao WHERE UUID = _uuid INTO dqjke;
SET dqjke = dqjke + _toubiao_jing_e;
UPDATE tb_biao SET dang_qian_jie_kuan_e = dqjke;
-- 阿斯顿发斯蒂芬
INSERT INTO tb_toubiao_jilu (biao_uuid,yong_hu_id,jing_e)VALUES(_uuid,_yong_hu_id,_jing_e);
-- 阿斯顿发速度
SET raise_zhang_hu_jing_e = 0 - _toubiao_jing_e;
CALL raiseZhangHuZongE(raise_zhang_hu_jing_e,_yong_hu_id);
-- 阿斯顿发斯蒂芬
SELECT jing_e FROM tb_biao WHERE UUID = _uuid INTO biao_jing_e;
IF (dqjke >= biao_jing_e) THEN
UPDATE tb_biao SET biao_status = 'success' WHERE UUID = _uuid;
END IF;
IF errinfo=1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$DELIMITER ;
DECLARE errinfo INT;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1;
这句异常23000是遇到重复关键字。
我想把这个过程写得更严谨一些。遇到任何异常(错误)都回滚。请问这个23000应该改成什么值?
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET errinfo=1;
DECLARE handler_type HANDLER
FOR condition_value [, condition_value] ...
statement
难道要列出所有的异常编号?