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;