Mysql使用存储过程循环读取查询结果集

使用存储过程循环读取查询结果集
CREATE PROCEDURE GetstopTime(IN CurrentDate varchar(50))
BEGIN
DECLARE OEEPercentageNum decimal(10,2) DEFAULT 0.00;
DECLARE PercentageChar VARCHAR(50) DEFAULT ‘0.00’;
DECLARE TotalNum INT DEFAULT 215;
DECLARE Weeks INT DEFAULT 0;
DECLARE StopNum INT DEFAULT 0;
DECLARE OEEPercentage decimal(10,2) DEFAULT 0.00;
DECLARE CurrentTime VARCHAR(50) DEFAULT ‘2023-01-01’;
DECLARE RemarkNote VARCHAR(50) DEFAULT ‘星期一’;
DECLARE StopTime decimal(10,2) DEFAULT 0.00;
DECLARE SumTime decimal(10,2) DEFAULT 0.00;
DECLARE StopLULTotal INT DEFAULT 0;
DECLARE FirstTime VARCHAR(50) DEFAULT ‘2023-01-01’;
DECLARE EndTime VARCHAR(50) DEFAULT ‘2023-01-01’;
DECLARE done INT DEFAULT FALSE;
DECLARE row_name VARCHAR(100);
DECLARE result_cursor CURSOR FOR SELECT DISTINCT(EQ_ID) from lul_eq_status_log WHERE EQ_STATUS=2 and DATE_TIME>=CONCAT(CurrentDate,’ 00:00:00’) and DATE_TIME<=CONCAT(CurrentDate,’ 23:59:59’); – 表名
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
– 打开游标
OPEN result_cursor;
– 循环读取
read_loop: LOOP
FETCH result_cursor INTO row_name;

IF done THEN
    LEAVE read_loop;
END IF;
        -- 在这里对每行进行处理
        SELECT DATE_TIME from lul_eq_status_log WHERE EQ_STATUS=2 and EQ_ID=row_name and DATE_TIME>=CONCAT(CurrentDate,' 00:00:00') and DATE_TIME<=CONCAT(CurrentDate,' 23:59:59') order by ID ASC limit 0,1 INTO FirstTime;
        
        SELECT DATE_TIME from lul_eq_status_log WHERE EQ_STATUS!=2 and EQ_ID=row_name and DATE_TIME>=FirstTime and DATE_TIME<=CONCAT(CurrentDate,' 23:59:59') order by ID ASC limit 0,1 INTO EndTime;
        
        SELECT (UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(FirstTime)) / 3600 AS HourDifference INTO StopTime;

Set SumTime = SumTime + StopTime;

END LOOP;
– 关闭游标
CLOSE result_cursor;
select SumTime/ (TotalNum24) as Num INTO PercentageChar;
select ROUND(PercentageChar100,2) as tageNum INTO OEEPercentageNum;
SELECT ROUND((100-OEEPercentageNum), 2) as Percentage INTO OEEPercentage;
SELECT NOW() as Times INTO CurrentTime;
SELECT DATE_FORMAT(CURDATE(), ‘%w’) AS Dayweek INTO Weeks;
if Weeks=1 then
SET RemarkNote=‘星期一’;
ELSEIF Weeks=2 then
SET RemarkNote=‘星期二’;
ELSEIF Weeks=3 then
SET RemarkNote=‘星期三’;
ELSEIF Weeks=4 then
SET RemarkNote=‘星期四’;
ELSEIF Weeks=5 then
SET RemarkNote=‘星期五’;
ELSEIF Weeks=6 then
SET RemarkNote=‘星期六’;
ELSE
SET RemarkNote=‘星期日’;
end if;
INSERT INTO oeepercentage (CreateDate,OeePercenTage, ReMark) VALUES (CurrentTime, OEEPercentage,RemarkNote);

END
调用
CREATE PROCEDURE InsertOEEPercentage()
BEGIN
DECLARE CurrentDate VARCHAR(50) DEFAULT ‘2023-09-19’;
SELECT CURDATE() as date INTO CurrentDate;
call GetstopTime(CurrentDate);
END