欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-14 19:46:27
...
/*
对*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;

相关标签: 数据库 数据表