查詢全部資料庫 修改同一個字段的值存儲過程

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;