求大佬看看这个存储过程写的有什么问题吗？

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
#Routine body goes here...
DECLARE i INT;
set i=0;
while i>=9 do
#查询类型
select ELE_TYPE into @eleType from TI_ENERGY_ELE2 group by ELE_TYPE order by ELE_TYPE limit i,1;
#删除这个类型一周前的数据
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = @eleType AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 8 DAY);
set i =i+1;
end while;
END
请问这个存储过程为什么执行表示成功，但是没有删除对应数据

i=0
while i>=9 do ?

内蒙古寿福健康管理

sqlsugar是一款基于代码生成器的智能开发平台可以帮助解决 Java 项目 90% 的重复工作，让开发更多关注业务逻辑。

2025-02-07 10:46广告

立即查看

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
#Routine body goes here...
#删除这个类型一周前的数据
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'AB线电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'A相电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'A相电流' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'BC线电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'B相电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'B相电流' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'CA线电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'C相电压' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
DELETE FROM TI_ENERGY_ELE2 WHERE ELE_TYPE = 'C相电流' AND CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 15 DAY);
END
这个存储过程中的SQL 写在一起好，还是分开写好

你应该才游标，还有就是del的时时候有额外条件：CREATE_DATE<= DATE_SUB(CURDATE(), INTERVAL 8 DAY) 看看是否满足

扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！

6回复贴，共1页

<<返回sql吧

分享到:

日	一	二	三	四	五	六