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

最新推荐文章于 2023-08-01 15:23:52 发布

转载最新推荐文章于 2023-08-01 15:23:52 发布 · 3.3k 阅读

文章标签：

#存储 #mysql #table #schema #2010

数据库专栏收录该内容

10 篇文章

订阅专栏

今天写一个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