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

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

 

DROP PROCEDURE IF EXISTS `SynchroWhenMergeBroker`;delimiter //CREATE PROCEDURE `SynchroWhenMergeBroker`(IN `oldbrokerid` int,IN `newbrokerid` int)LANGUAGE SQLDETERMINISTICCONTAINS SQLCOMMENT ''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