看源码:USE slt;
DROP PROCEDURE IF EXISTS sp_count_bonus2;
CREATE PROCEDURE sp_count_bonus2
(IN v_issue INT)-- 上期的期号
BEGIN
DECLARE v_account_id VARCHAR(20);-- 账号
DECLARE v_fig INT;--
DECLARE v_errint INT;-- 错误编码
DECLARE v_errmge VARCHAR(200);-- 错误描述
-- DECLARE v_issue INT;-- 期号-- 捕获异常
DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION SET v_errint=-1;
BEGIN
START TRANSACTION
-- 创建游标
DECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue=v_issue AND bet_state='A';
-- 捕获异常
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;
SET v_fig = 0;
-- 打开游标
OPEN cur;
-- 变量赋值
FETCH cur INTO v_account_id;
WHILE v_fig = 0 DO
BEGIN
CALL sp_count_bonus(v_account_id,v_issue);
FETCH cur INTO v_account_id;
END;
END WHILE;
CLOSE cur;
CALL sp_clear_credit;
END;IF v_errint=-1
BEGIN
ROLLBACK;
SELECT v_errint;
END;
ELSE
BEGIN
SET v_errint=0;
SET v_errmge='奖金计算成功';
COMMIT;
SELECT v_errint;
END;
END IF;
END;运行之后提示:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue' at line 15
麻烦高手帮忙看下,这个哪里出错了???
DROP PROCEDURE IF EXISTS sp_count_bonus2;
CREATE PROCEDURE sp_count_bonus2
(IN v_issue INT)-- 上期的期号
BEGIN
DECLARE v_account_id VARCHAR(20);-- 账号
DECLARE v_fig INT;--
DECLARE v_errint INT;-- 错误编码
DECLARE v_errmge VARCHAR(200);-- 错误描述
-- DECLARE v_issue INT;-- 期号-- 捕获异常
DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION SET v_errint=-1;
BEGIN
START TRANSACTION
-- 创建游标
DECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue=v_issue AND bet_state='A';
-- 捕获异常
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;
SET v_fig = 0;
-- 打开游标
OPEN cur;
-- 变量赋值
FETCH cur INTO v_account_id;
WHILE v_fig = 0 DO
BEGIN
CALL sp_count_bonus(v_account_id,v_issue);
FETCH cur INTO v_account_id;
END;
END WHILE;
CLOSE cur;
CALL sp_clear_credit;
END;IF v_errint=-1
BEGIN
ROLLBACK;
SELECT v_errint;
END;
ELSE
BEGIN
SET v_errint=0;
SET v_errmge='奖金计算成功';
COMMIT;
SELECT v_errint;
END;
END IF;
END;运行之后提示:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue' at line 15
麻烦高手帮忙看下,这个哪里出错了???
解决方案 »
- mysqldump远程的问题
- 急求《MySQL必知必会》电子书籍
- mysql是否是免费
- 如何让autocommit=0在服务启动后即生效?
- 关于mysql软件远程管理问题
- 再问,我想对一个字段里的不同值进行计数,怎么写?
- mysql怎样在2000server下面安装!?
- 求助----新手刚开始学习数据库,谁有郝斌老师的数据库视频
- 求救。关于MYSQL数据库PHPMYADMIN下正常,却导出乱码的问题
- Mysql 字符集utf8mb4设置无效
- c语言调用mysqlAPI执行存储过程发生错误:select Commands out of sync
- mysql统计玩家登录次数,大侠们进来指点一下啊!!!
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_count_bonus2 $$
CREATE PROCEDURE sp_count_bonus2(IN v_issue INT)
BEGIN
DECLARE v_account_id VARCHAR(20);
DECLARE v_fig INT;
DECLARE v_errint INT;
DECLARE v_errmge VARCHAR(200);BEGINDECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue=v_issue AND bet_state='A';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;
START TRANSACTION;
SET v_fig = 0;
OPEN cur;-- 变量赋值FETCH cur INTO v_account_id;WHILE v_fig = 0 DO BEGIN CALL sp_count_bonus(v_account_id,v_issue); FETCH cur INTO v_account_id; END;END WHILE;CLOSE cur;CALL sp_clear_credit;END;IF v_errint=-1 THEN BEGIN ROLLBACK; SELECT v_errint; END;ELSE BEGIN SET v_errint=0; SET v_errmge='奖金计算成功'; COMMIT; SELECT v_errint; END;END IF;END;$$
DELIMITER ;
你看在游标中已经定义了捕获异常,然后在捕获嵌套存储过程的异常怎么办??或者说是捕获WHILE语句块中的异常怎么办呢?-- 创建游标
DECLARE cur CURSOR FOR SELECT DISTINCT account_id FROM slt_bet_info WHERE issue=v_issue AND bet_state='A';
-- 捕获异常
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;
SET v_fig = 0;
-- 打开游标
OPEN cur;
-- 变量赋值
FETCH cur INTO v_account_id;
WHILE v_fig = 0 DO
BEGIN
CALL sp_count_bonus(v_account_id,v_issue);
FETCH cur INTO v_account_id;
END;
END WHILE;
CLOSE cur;