Mysql存储过程中循环内的Select语句 [英] Select statement inside a loop in a Mysql Stored Procedure
本文介绍了Mysql存储过程中循环内的Select语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们可以在Mysql存储过程的循环内使用Select语句吗?
Can we use Select statement inside a loop in a Mysql Stored Procedure?
为什么代码错误
create procedure AbsentReportproc (INOUT fromdate DATETIME, INOUT todate DATETIME)
as
begin
DECLARE startdate DATE;
DECLARE enddate DATE;
DECLARE nofdays INT;
DECLARE counter INT;
DECLARE countdate DATE;
startdate=fromdate;
enddate=todate;
countdate=fromdate;
nofdays=DATEDIFF(DAY,startdate,endate);
counter=1;
while counter<=noofdays
loop
select CARDNO from test_prefixmaster
where CARDNO not in ( select CARDNO from test_prefixtransactions where Date(S_DateTime)=countdate)
set countdate=countdate+1;
set counter=counter+1;
end loop;
end//
推荐答案
尝试一下:
DELIMITER $$
DROP PROCEDURE IF EXISTS `AbsentReportproc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AbsentReportproc`(IN _fromdate DATETIME, IN _todate DATETIME)
BEGIN
CREATE TEMPORARY TABLE daterange (dte DATE);
SET @counter := -1;
WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO
INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY));
END WHILE;
SELECT tp.cardno, tp.EMPCODE, tp.DEPARTMENT, GROUP_CONCAT(d.dte) Absentddate, COUNT(tp.cardno) Totalnoofabsentdates
FROM test_prefixmaster tp JOIN daterange d
LEFT JOIN test_prefixtransactions tpt ON tp.cardno = tpt.CARDNO AND DATE(S_DateTime) = d.dte
WHERE tpt.CARDNO IS NULL
GROUP BY tp.cardno;
DROP TABLE daterange;
END$$
DELIMITER ;
这篇关于Mysql存储过程中循环内的Select语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文