MySQL数据库中，使用游标循环遍历_MySQL

/*
对*dt库下的所有数据表删除docuemttype为空和documenttype为md,pd,et的数据：
delete from 表名 where length(documenttype)*/
delimiter $$
use `数据库名称1`$$
drop procedure if exists `存储过程名称1`$$
create procedure `存储过程名称1`()
begin
declare str varchar(40);
declare done int default 0;
declare rs cursor for select table_name from information_schema.tables where table_schema = '数据库名称1';
/* 异常处理 */
declare continue handler for sqlstate '02000' set done = 1;
/* 打开游标 */
open rs;
/* 逐个取出当前记录userid字段的值*/
fetch next from rs into str;
/* 遍历数据表 */
repeat
delete from str where length(documenttype) fetch next from rs into str;
until done end repeat;
/* 关闭游标 */
close rs;
end$$
delimiter ;

use 数据库名称1;
call 存储过程名称1;

/*
对*sd库下的所有数据表删除docuemttype不为空的数据
delete from 表名 where length(documenttype)>1;
*/
DELIMITER $$
USE `数据库名称2`$$
DROP PROCEDURE IF EXISTS `存储过程名称2`$$
CREATE PROCEDURE `存储过程名称2`()
BEGIN
DECLARE str varchar(40);
DECLARE Done INT DEFAULT 0;
DECLARE rs CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '数据库名称2';
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录userId字段的值*/
FETCH NEXT FROM rs INTO str;
/* 遍历数据表 */
REPEAT
delete from str where length(documenttype)>1;
FETCH NEXT FROM rs INTO str;
UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
END$$
DELIMITER ;

use 数据库名称2;
call 存储过程名称2;

/*
对*et库下的所有数据表删除非学位论文数据：
delete from 表名 where length(doucmenttype)='' or documenttype is null or documenttype not in ('et','md','pd');
*/

DELIMITER $$
USE `数据库名称3`$$
DROP PROCEDURE IF EXISTS `存储过程名称3`$$
CREATE PROCEDURE `存储过程名称3`()
BEGIN
DECLARE str varchar(40);
DECLARE Done INT DEFAULT 0;
DECLARE rs CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '数据库名称3';
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录userId字段的值*/
FETCH NEXT FROM rs INTO str;
/* 遍历数据表 */
REPEAT
delete from str where length(doucmenttype)='' or documenttype is null or documenttype not in ('et','md','pd');
FETCH NEXT FROM rs INTO str;
UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
END$$
DELIMITER ;

use 数据库名称3;
call 存储过程名称3;