Mysql存储过程中使用临时表和游标

1。临时表 

DROP PROCEDURE
IF EXISTS `P_GetMonitorPeople`;
CREATE PROCEDURE P_GetMonitorPeople (IN fgid INT, IN mins INT,in lens INT)
BEGIN
IF fgid>0&&mins>0&&lens>0 THEN
BEGIN
-- 建表tb_temp1
DROP TABLE IF EXISTS tb_temp1;
CREATE TEMPORARY TABLE tb_temp1(
`Tmp_Id` int UNSIGNED NOT NULL AUTO_INCREMENT, 
`GatherID` int NOT NULL,
`PhoneMac` varchar(12) NOT NULL,
`number` int NOT NULL,
PRIMARY KEY (`Tmp_Id`)
) ENGINE = MYISAM DEFAULT charset = utf8; 
-- 填充tb_temp1
INSERT into tb_temp1(GatherID,PhoneMac,number)
select gatherid, phonemac,count(phonemac) as number from (
select DISTINCT s.GatherID,s.phonemac from tb_app_gather_mac_shot s
LEFT JOIN tb_web_config_gather g on g.gatherid=s.gatherid 
where s.logtime BETWEEN DATE_SUB(NOW(),INTERVAL mins MINUTE) and NOW() and g.floorid=(select floorid from tb_web_floors where groupid=fgid)
ORDER BY s.logtime) as tb GROUP BY phonemac order by number DESC LIMIT lens;

select DISTINCT g.gatherid, t.phonemac,g.locationx,g.locationy from tb_temp1 t
LEFT JOIN tb_web_config_gather g on g.gatherid=t.gatherid;
END;
END IF;
#销毁内存表
DROP TABLE IF EXISTS tb_temp1;
END

-- 显示
CALL P_GetMonitorPeople(3,1,50);

 

 

2。游标

DROP PROCEDURE
IF EXISTS P_GetMonitorData;
CREATE PROCEDURE P_GetMonitorData(fgid int,mins int,lens int,mac varchar(12))

BEGIN
DECLARE tmpid int ;
DECLARE gid varchar(10) ;
DECLARE done INT DEFAULT FALSE;
DECLARE pregatherid int DEFAULT 0;
DECLARE str VARCHAR(200);
-- 定义游标
DECLARE rs CURSOR for select Tmp_Id,GatherID from tb_temp2;
-- 错误定义，标记循环结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
-- 打开游标
OPEN rs;
-- 循环执行
REPEAT
FETCH rs into tmpid,gid ;
IF NOT done THEN
#执行操作
IF pregatherid=gid && pregatherid <> 0 THEN
DELETE from tb_temp2 where Tmp_Id=tmpid;
ELSE
SET pregatherid=gid;
-- SET str=CONCAT(gid,str);
END IF;
END IF;
#当_done=FALSE时退出被循
UNTIL done 
END REPEAT;
/*关闭游标*/
CLOSE rs;
select * from tb_temp2;
SET done=FALSE;#只有定义为false，新的循环才能继续。
END