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

/*

对*dt库下的所有数据表删除docuemttype为空和documenttype为MD,PD,ET的数据：

delete from 表名 where length(documenttype)<2 or documenttype is null or documenttype in ('et','md','pd'); */

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)<2 or documenttype is null or documenttype in ('et','md','pd');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;