mysql存储过程之游标遍历数据表

今天写一个mysql存储过程，根据自己的需求要遍历一个数据表，因为对存储过程用的不多，语法不甚熟悉，加之存储过程没有调试环境，花了不少时间才慢慢弄好，故留个痕迹。

DROP PROCEDURE IF EXISTS `SynchroWhenMergeBroker`;
delimiter //
CREATE PROCEDURE `SynchroWhenMergeBroker`(IN `oldbrokerid` int,IN `newbrokerid` int)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	COMMENT ''
BEGIN
  DECLARE  Done  INT   DEFAULT   0 ;
  DECLARE tablename VARCHAR(255);
  DECLARE  rs  CURSOR   FOR   SELECT  table_name  FROM  information_schema.columns WHERE lower(table_schema) = 'mess' AND lower(column_name) = 'idd';
  DECLARE   CONTINUE  HANDLER  FOR  SQLSTATE  '02000'   SET  Done  =   1 ;
  OPEN  rs;
    FETCH   NEXT   FROM  rs  INTO  tablename;
    REPEAT
      IF   NOT  Done  THEN
              
				SET @stmt = concat('UPDATE ',tablename,' set idd = 110 where idd = ',oldbrokerid);
				
				PREPARE s1 FROM @stmt;
				
				EXECUTE s1;
				
      END IF;
        FETCH   NEXT   FROM  rs  INTO  tablename;
    UNTIL Done END  REPEAT;
	
		DEALLOCATE PREPARE S1;
	
  CLOSE  rs;
END
//
delimiter ;
CALL SynchroWhenMergeBroker(10,1);

原文地址：http://www.cppblog.com/kongque/archive/2010/01/07/105090.aspx