mysql遍历游标_MySQL数据库中，使用游标循环遍历_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;

本文原创发布php中文网，转载请注明出处，感谢您的尊重！