1.建表和插入数据
DROP TABLE IF EXISTS `tt`;
CREATE TABLE `tt` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`datetime` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tt
-- ----------------------------
INSERT INTO `tt` VALUES ('1', 'z', 'p202009162000');
INSERT INTO `tt` VALUES ('2', 'y', 'p202009162100');
INSERT INTO `tt` VALUES ('3', 'f', 'p202009162200');
INSERT INTO `tt` VALUES ('4', 'g', 'p202009171000');
INSERT INTO `tt` VALUES ('5', 'h', 'p202009172200');
2.存储过程
delimiter $$
create procedure drop_all(t_name varchar(200))
BEGIN
DECLARE cnt_model INT;
set @result = CONCAT("select count(1) from ",t_name," where right(date_format(date_sub(now(), interval 1 hour), '%Y%m%d%H%i'),12)>=right(datetime,12)","INTO @t_count");
prepare stmt FROM @result;
EXECUTE stmt;
set cnt_model =@t_count;
if(cnt_model>=1)then
set @d_name = CONCAT("truncate table ",t_name);
prepare stmt1 FROM @d_name;
EXECUTE stmt1;
end if;
END $$
delimiter ;
3.调用存储过程
call drop_all('tt');//动态传入表名称
4.删除存储过程
drop PROCEDURE drop_all;
5.查询当前数据库中的所有存储过程
select `name` from MySQL.proc where db = 'databaseName' and `type` = 'PROCEDURE';