数据库游标使用mysql（全表操作）

DELIMITER $


DROP PROCEDURE IF EXISTS reset_sync_status $


CREATE PROCEDURE reset_sync_status
(
IN p_db_name VARCHAR(30)
)
BEGIN
DECLARE sql_str VARCHAR(200);
DECLARE table_name VARCHAR(60);
DECLARE done INT DEFAULT FALSE;
DECLARE field_name VARCHAR(20) DEFAULT 'sync_status';
DECLARE def_value VARCHAR(10) DEFAULT '0';

DECLARE cur CURSOR FOR
SELECT a.table_name
FROM information_schema.TABLES a INNER JOIN information_schema.COLUMNS b ON a.table_name = b.table_name
WHERE a.table_schema = p_db_name AND b.column_name = field_name;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


OPEN cur;

read_loop:LOOP


FETCH cur INTO table_name;

SET @sql_str = CONCAT('UPDATE ',table_name,' set ',field_name,'=',def_value,';');
PREPARE stmt FROM @sql_str;
EXECUTE stmt;

IF done THEN
LEAVE read_loop;
END IF;

END LOOP;

CLOSE cur;


END $


CALL reset_sync_status('chm_baseinfo') $


DROP PROCEDURE reset_sync_status $ 


DELIMITER ;