项目中有表管理功能,会按照一定规则生成表,现提供清除这些表的清表语句(mysql,oracle类似)
表:
drop procedure if exists DELETE_TABLE_SQL;
CREATE PROCEDURE DELETE_TABLE_SQL()
BEGIN
DECLARE Done1 INT DEFAULT 0;
DECLARE TNAME varchar(19);
#声明游标b_result
DECLARE b_result CURSOR FOR
select table_name
FROM information_schema.tables
Where table_schema = 'CENTER_BUS' and (table_name LIKE '%\_b\_%' or table_name LIKE '%\_m\_%');
#异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done1 = 1;
OPEN b_result;
# 逐个取出当前记录字段的值
FETCH NEXT FROM b_result INTO TNAME;
WHILE Done1 <> 1 DO
SET @sql = CONCAT('DROP TABLE IF EXISTS ',TNAME,';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH NEXT FROM b_result INTO TNAME;
END WHILE;
#关闭游标
CLOSE b_result;
END;
delimiter ;
CALL DELETE_TABLE_SQL();
视图:
drop procedure if exists DELETE_VIEW_SQL;
CREATE PROCEDURE DELETE_VIEW_SQL()
BEGIN
DECLARE Done1 INT DEFAULT 0;
DECLARE TNAME varchar(19);
#声明游标b_result
DECLARE b_result CURSOR FOR
SELECT
table_name
FROM
information_schema. TABLES
WHERE
table_schema = 'center_bus'
AND table_type = 'view'
AND table_name LIKE '%\_s\_%';
#异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done1 = 1;
OPEN b_result;
# 逐个取出当前记录字段的值
FETCH NEXT FROM b_result INTO TNAME;
WHILE Done1 <> 1 DO
SET @sql = CONCAT('DROP VIEW IF EXISTS ',TNAME,';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH NEXT FROM b_result INTO TNAME;
END WHILE;
#关闭游标
CLOSE b_result;
END;
delimiter ;
CALL DELETE_VIEW_SQL();