帮忙看看我这个存储过程为什么老报错！

[nightgoblin]

看源码：

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
麻烦高手帮忙看下，这个哪里出错了???

[nightgoblin]

引用 2 楼 WWWWA 的回复:

逻辑自行检查 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 ……

感谢解答，我知道错哪里了。

[nightgoblin]

引用 2 楼 WWWWA 的回复:

逻辑自行检查 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 ……

有个问题，存储过程里面是不是不能写2个异常捕获的语句啊？

[wwwwb]

存储过程里面是不是不能写2个异常捕获的语句啊？ 不能

[nightgoblin]

引用 6 楼 wwwwb 的回复:

存储过程里面是不是不能写2个异常捕获的语句啊？ 不能

那像我这种情况，我要捕获游标里面 嵌套存储过程的异常怎么办呢？？

[wwwwb]

在SP中　不行？

[nightgoblin]

引用 8 楼 wwwwb 的回复:

在SP中　不行？

你看在游标中已经定义了捕获异常，然后在捕获嵌套存储过程的异常怎么办？？或者说是捕获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;

[WWWWA]

在sp_count_bonus中捕获

[nightgoblin]

引用 10 楼 WWWWA 的回复:

在sp_count_bonus中捕获

可这个sp_count_bonus过程中也是用游标的，那换个简单的问法：如何在游标中的WHILE语句中捕获异常？因为前面已经定义了DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig=1;那么在WHILE语句中捕获异常怎么办呢？

[ACMAIN_CHM]

在WHILE中检查一下v_fig是否 =1

[ACMAIN_CHM]

START TRANSACTION 后的分号哪去了？

[WWWWA]

逻辑自行检查
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);

BEGIN

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;
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 ;

[rucypli]

逻辑是复杂 存储过程套游标套存储过程