查询全部数据库 修改同一个字段的值存储过程

/**
*sField  字段名
*sOldValue      旧值
*sNewValue    新值 
*/
DROP PROCEDURE IF EXISTS `P_SYS_UPDATEALL_BY_FIELD`;

CREATE DEFINER = `root`@`%` PROCEDURE `P_SYS_UPDATEALL_BY_FIELD`(IN `sField` varchar(100),IN `sOldValue` varchar(100),IN `sNewValue` varchar(100))

BEGIN

#Routine body goes here...

-- 循环标志

DECLARE done INT DEFAULT false;

-- 记录表名

DECLARE _tableName VARCHAR(100);

-- sql字符串

DECLARE sqlStr VARCHAR(2000);

-- 测试退出计数

DECLARE count INT DEFAULT 0; 

-- 查询所有表

DECLARE cur CURSOR FOR SELECT TABLE_NAME from information_schema.TABLES

WHERE TABLE_TYPE='BASE TABLE'

AND TABLE_SCHEMA =(SELECT DATABASE())

AND TABLE_NAME NOT in(select F_TABLE_NAME from db_init_table) ;

-- 循环结束标志设置

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE ;

-- 打开游标

OPEN cur;

-- 循环

read_loop: LOOP

-- 循环变量

FETCH cur INTO _tableName;

-- 循环结束

IF done 

THEN

-- 退出循环

LEAVE read_loop;

END IF;

-- 如果表存在这个字段

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME=_tableName AND COLUMN_NAME=sField) >0

THEN 

-- 拼接sql

SET sqlStr= CONCAT(' update ',_tableName,' set ',sField,' = \'',sNewValue,'\' where ',sField,' = \'',sOldValue,'\' ');

SET @s_sql=sqlStr;

-- 执行

PREPARE stmt FROM @s_sql;

EXECUTE stmt;

DEALLOCATE prepare stmt; 

END if;

END loop;

-- 关闭游标

  CLOSE cur;  

END;