BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR 1062 SET t_error=1; set autocommit=0; START TRANSACTION; call put_playerbehavior(i_playerid,i_gameid,i_channelid,i_acttime,@a); IF t_error=1 then rollback; call put_playerbehavior(i_playerid,i_gameid,i_channelid,i_acttime,@a); END IF; COMMIT; END begin DECLARE v_playerid int; DECLARE v_gameid int; DECLARE v_channelid int; DECLARE done int; DECLARE v_acttime datetime; DECLARE v_endday datetime; DECLARE cur_behavior CURSOR FOR select playerid,gameid,channelid,acttime from playerbehavior where acttime >= i_startday and acttime < date_add(i_endday,INTERVAL 1 day); declare continue handler FOR SQLSTATE '02000' SET done = 1; set o_out=0; set v_endday=date_add(i_endday,INTERVAL 1 day); open cur_behavior; repeat FETCH cur_behavior INTO v_playerid,v_gameid,v_channelid,v_acttime; CALL test_roll(v_playerid,v_gameid,v_channelid,v_acttime,@a); until done end repeat; close cur_behavior; set o_out=1; end 第二个存储过程调用第一个存储过程，功能是把playerbehavior表中符合条件的数据用test_roll存储过程进行分析处理，讲结果放到一张表中，但是实际运行过程中，符合条件的最后一条数据会重复运行一次，不知道是什么情况~求大神解惑~分析的存储过程是没错的，就是以上2个存储过程~ 标签： MySQL 存储过程 我想问同样的问题 共0个人想要问同样的问题 补充话题说明» 分享到 收藏1举报踩 0 | 顶 0 按评价排序 | 显示最新答案 | 回页面顶部 共有2个答案 我要回答» 八宝旗 回答于 2012-12-03 16:09 举报 rp_1:repeat fetch cur** into **; if done=1 then leave rp_1; end if; call procedure(********); until done end repeat rp_1: --- 共有 1 条评论 --- 大神，膜拜一下 (7个月前 by 秋到尽头) 有帮助(0) | 没帮助(0) | 评论(1) | 引用此答案 八宝旗 回答于 2012-12-03 15:10 举报 FETCH 之后会触发handler进行SET done = 1; 但FETCH 与 until中间的代码会继续执行，从而引起最后一条数据被重复运行
下一步就是建立一个在做插入动作出错时存储错误的表。 4. Sample Problem: Log Of Errors CREATE PROCEDURE p22 (parameter1 INT) BEGIN DECLARE EXIT HANDLER FOR 1216 INSERT INTO error_log VALUES (CONCAT('Time: ',current_date, '. Foreign Key Reference Failure For Value = ',parameter1)); INSERT INTO t3 VALUES (parameter1); END;// 上面就是我们的程序。这里的第一个语句DECLARE EXIT HANDLER是用来处理异常的。意思是如果错误1215发生了，这个程序将会在错误记录表中插入一行。EXIT意思是当动作成功提交后退出这个复合语句。 5. Sample Problem: Log Of Errors CALL p22 (5) // 调用这个存储过程会失败，这很正常，因为5值并没有在主键表中出现。但是没有错误信息 返回因为出错处理已经包含在过程中了。t3表中没有增加任何东西，但是error_log表中记录 下了一些信息，这就告诉我们INSERT into table t3动作失败。 DECLARE HANDLER syntax 声明异常处理的语法 DECLARE { EXIT | CONTINUE } HANDLER FOR { error-number | { SQLSTATE error-string } | condition } SQL statement 上面就是错误处理的用法，也就是一段当程序出错后自动触发的代码。MySQL允许两种处理器，一种是EXIT处理，我们刚才所用的就是这种。另一种就是我们将要演示的，CONTINUE处理，它跟EXIT处理类似，不同在于它执行后，原主程序仍然继续运行，那么这个复合语句就没有出 口了。 1. DECLARE CONTINUE HANDLER example CONTINUE处理例子 CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); SET @x = 3; END;// 这是MySQL参考手册上的CONTINUE处理的例子，这个例子十分好，所以我把它拷贝到这里。 通过这个例子我们可以看出CONTINUE处理是如何工作的。 2. DECLARE CONTINUE HANDLER声明CONTINUE异常处理 CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; <-- SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); SET @x = 3; END;// 这次我将为SQLSTATE值定义一个处理程序。还记得前面我们使用的MySQL错误代码1216吗？ 事实上这里的23000SQLSTATE是更常用的，当外键约束出错或主键约束出错就被调用了。 3. DECLARE CONTINUE HANDLER CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; <-- INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); SET @x = 3; END;// 这个存储过程的第一个执行的语句是"SET @x = 1"。 4. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); <-- SET @x = 3; END;// 运行后值1被插入到主键表中。 5. DECLARE CONTINUE HANDLER CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; <-- INSERT INTO t4 VALUES (1); SET @x = 3; END;// 然后@x的值变为2。 6. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); <-- SET @x = 3; END;// 然后程序尝试再次往主键表中插入数值，但失败了，因为主键有唯一性限制。 7. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; <-- SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); SET @x = 3; END;// 由于插入失败，错误处理程序被触发，开始进行错误处理。下一个执行的语句是错误处理的语句，@x2被设为2。 8. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO t4 VALUES (1); SET @x = 2; INSERT INTO t4 VALUES (1); SET @x = 3; <-- END;// 到这里并没有结束，因为这是CONTINUE异常处理。所以执行返回到失败的插入语句之后，继续执行将@x设定为3动作。 9. DECLARE CONTINUE HANDLER example mysql> CALL p23()// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x, @x2// +------+------+ | @x | @x2 | +------+------+ | 3 | 1 | +------+------+ 1 row in set (0.00 sec)
运行过程后我们观察@x的值，很确定的可以知道是3，观察@x2的值，为1。从这里可以判断程序运行无误，完全按照我们的思路进行。大家可以花点时间去调整错误处理器，让检查放在语句段的首部，而不是放在可能出现错误的地方，虽然那样看起来程序很紊乱，跳来跳去的感觉。但是这样的代码很安全也很清楚。 1. DECLARE CONDITION CREATE PROCEDURE p24 () BEGIN DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000'; DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK; START TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (1); COMMIT; END; // 这是另外一个错误处理的例子，在前面的基础上修改的。事实上你可给SQLSTATE或者错误代码其他的名字，你就可以在处理中使用自己定义的名字了。下面看它是怎么实现的：我把表t2定义为InnoDB表，所以对这个表的插入操作都会ROLLBACK（回滚），ROLLBACK（回滚事务）也是恰好会发生的。因为对主键插入两个同样的值会导致SQLSTATE 23000错误发生，这里SQLSTATE 23000是约束错误。 2. DECLARE CONDITION声明条件 CREATE PROCEDURE p24 () BEGIN DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000'; DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK; START TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (1); COMMIT; END; // 这个约束错误会导致ROLLBACK（回滚事务）和SQLSTATE 23000错误发生。 3. DECLARE CONDITION mysql> CALL p24()// Query OK, 0 rows affected (0.28 sec) mysql> SELECT * FROM t2// Empty set (0.00 sec) 我们调用这个存储过程看结果是什么，从上面结果我们看到表t2没有插入任何记录。全部事务都回滚了。这正是我们想要的。 4. DECLARE CONDITION mysql> CREATE PROCEDURE p9 () -> BEGIN -> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; -> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; -> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END; -> END;// Query OK, 0 rows affected (0.00 sec) 这里是三个预声明的条件：NOT FOUND (找不到行), SQLEXCEPTION (错误),SQLWARNING (警告或注释)。因为它们是预声明的，因此不需要声明条件就可以使用。不过如果你去做这样的声明："DECLARE SQLEXCEPTION CONDITION ..."，你将会得到错误信息提示。
Cursors 游标 游标实现功能摘要： DECLARE cursor-name CURSOR FOR SELECT ...; OPEN cursor-name; FETCH cursor-name INTO variable [, variable]; CLOSE cursor-name; 现在我们开始着眼游标了。虽然我们的存储过程中的游标语法还并没有完整的实现，但是已经可以完成基本的事务如声明游标，打开游标，从游标里读取，关闭游标。 1. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 我们看一下包含游标的存储过程的新例子。 2. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; <-- DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 这个过程开始声明了三个变量。附带说一下，顺序是十分重要的。首先要进行变量声明，然后声明条件，随后声明游标，再后面才是声明错误处理器。如果你没有按顺序声明，系统会提示错误信息。 3. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 程序第二步声明了游标cur_1，如果你使用过嵌入式SQL的话，就知道这和嵌入式SQL差不多。 4. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND <-- SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 最后进行的是错误处理器的声明。这个CONTINUE处理没有引用SQL错误代码和SQLSTATE值。它使用的是NOT FOUND系统返回值，这和SQLSTATE 02000是一样的。 5. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; <-- REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 过程第一个可执行的语句是OPEN cur_1，它与SELECT s1 FROM t语句是关联的，过程将执行SELECT s1 FROM t，返回一个结果集。 6. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; <-- UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 这里第一个FETCH语句会获得一行从SELECT产生的结果集中检索出来的值，然而表t中有多行，因此这个语句会被执行多次，当然这是因为语句在循环块内。 7. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 8. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; <-- SET return_val = a; END;// 到了这一步UNTIL b=1条件就为真，循环结束。在这里我们可以自己编写代码关闭游标，也可以由系统执行，系统会在复合语句结束时自动关闭游标，但是最好不要太依赖系统的自动关闭行为（译注：这可能跟Java的Gc一样，不可信）。
. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; <-- END;// 这个例程中我们为输出参数指派了一个局部变量，这样在过程结束后的结果仍能使用。 10. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// mysql> CALL p25(@return_val)// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @return_val// +-------------+ | @return_val | +-------------+ | 5 | +-------------+ 1 row in set (0.00 sec) 上面是过程调用后的结果。可以看到return_val参数获得了数值5，因为这是表t的最后一行。 由此可以知道游标工作正常，出错处理也工作正常。 Cursor Characteristics 游标的特性 摘要： READ ONLY只读属性 NOT SCROLLABLE顺序读取 ASENSITIVE敏感 在5.0版的MySQL中，你只可以从游标中取值，不能对其进行更新。因为游标是（READONLY）只读的。你可以这样做： FETCH cursor1 INTO variable1; UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1; 游标也是不可以滚动的，只允许逐一读取下一行，不能在结果集中前进或后退。下面代码就是错误的： FETCH PRIOR cursor1 INTO variable1; FETCH ABSOLUTE 55 cursor1 INTO variable1; 同时也不允许在已打开游标进行操作的表上执行updates事务，因为游标是（ASENSITIVE）敏感的。因为如果你不阻止update事务，那就不知道结果会变成什么。如果你使用的是InnoDB而不是MyISAM存储引擎的话，结果也会不一样。 Security 安全措施 摘要 Privileges (1) CREATE ROUTINE Privileges (2) EXECUTE Privileges (3) GRANT SHOW ROUTINE? Privileges (4) INVOKERS AND DEFINERS 这里我们要讨论一些关于特权和安全相关的问题。但因为在MySQL安全措施的功能并没有完全，所以我们不会对其进行过多讨论。 1. Privileges CREATE ROUTINE GRANT CREATE ROUTINE ON database-name . * TO user(s) [WITH GRANT OPTION]; 现在用root就可以了 在这里要介绍的特权是CREATE ROUTINE，它不仅同其他特权一样可以创建存储过程和函数，还可以创建视图和表。Root用户拥有这种特权，同时还有ALTER ROUTINE特权。 2. Privileges EXECUTE GRANT EXECUTE ON p TO peter [WITH GRANT OPTION]; 上面的特权是决定你是否可以使用或执行存储过程的特权，过程创建者默认拥有这个特权。 3. Privileges SHOW ROUTINE? GRANT SHOW ROUTINE ON db6.* TO joey [WITH GRANT OPTION]; 因为我们已经有控制视图的特权了：GRANT SHOW VIEW。所以在这个基础上，为了保证兼容，日后可能会添加GRANT SHOW ROUTINE特权。这样做是不太符合标准的，在写本书的时候，MySQL还没实现这个功能。 4. Privileges Invokers and Definers 特权调用者和定义者 CREATE PROCEDURE p26 () SQL SECURITY INVOKER SELECT COUNT(*) FROM t // CREATE PROCEDURE p27 () SQL SECURITY DEFINER SELECT COUNT(*) FROM t // GRANT INSERT ON db5.* TO peter; // 现在我们测试一下SQL SECURITY子句吧。Security是我们前面提到的程序特性的一部分。你root用户，将插入权赋给了peter。然后使用peter登陆进行新的工作，我们看peter可以怎么使用存储过程，注意：peter没有对表t的select权力，只有root用户有。 5. Privileges Invokers and Definers /* Logged on with current_user = peter */使用帐户peter登陆 mysql> CALL p26(); ERROR 1142 (42000): select command denied to user 'peter'@'localhost' for table 't' mysql> CALL p27(); +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) 当peter尝试调用含有调用保密措施的过程p26时会失败。那是因为peter没有对表的select的权力。 但是当petre调用含有定义保密措施的过程时就能成功。原因是root有select权力，Peter有root的权力，因此过程可以执行。