Using temporal tables and cursors in MySQL stored procedures

1. Temp table

1 DROP PROCEDURE2 IF EXISTS' p_getmonitorpeople ';3 CREATE PROCEDUREP_getmonitorpeople (inchFgidINT,inchminsINT,inchLensINT)4 BEGIN5 IFFgid>0&&mins>0&&Lens>0  Then6 BEGIN7 --Build Table Tb_temp18 DROP TABLE IF EXISTSTb_temp1;9 CREATE Temporary TABLETB_TEMP1 (Ten' tmp_id 'intUNSIGNED not NULLAuto_increment, One' Gatherid 'int  not NULL, A' Phonemac 'varchar( A) not NULL, -` Number`int  not NULL, - PRIMARY KEY(' tmp_id ') the) ENGINE=MYISAMDEFAULTCharSet=UTF8; - --Fill Tb_temp1 - INSERT  intoTB_TEMP1 (Gatherid,phonemac, Number) - SelectGatherid, Phonemac,Count(PHONEMAC) as  Number  from ( + Select DISTINCTS.gatherid,s.phonemac fromTb_app_gather_mac_shot S -  Left JOINTb_web_config_gather g onG.gatherid=S.gatherid + whereS.logtimebetweenDate_sub (now (), INTERVAL mins MINUTE) andNow () andG.floorid=(SelectFloorid fromTb_web_floorswhereGroupID=fgid) A ORDER  byS.logtime) asTbGROUP  byPhonemacOrder  by  Number DESCLIMIT Lens; at  - Select DISTINCTG.gatherid, T.phonemac,g.locationx,g.locationy fromTB_TEMP1 T -  Left JOINTb_web_config_gather g onG.gatherid=T.gatherid; - END; - END IF; - #销毁内存表 in DROP TABLE IF EXISTSTb_temp1; - END to  + --Show -Call P_getmonitorpeople (3,1, -);

2. Cursor

DROP PROCEDUREIF EXISTSP_getmonitordata;CREATE PROCEDUREP_getmonitordata (Fgidint, minsint, LensintMacvarchar( A))BEGINDECLARETmpidint ;DECLAREGidvarchar(Ten) ;DECLAREDoneINT DEFAULTFALSE;DECLAREPregatheridint DEFAULT 0;DECLARE Str VARCHAR( $);--Defining CursorsDECLARERsCURSOR  for SelectTmp_id,gatherid fromTB_TEMP2;--error definition, Mark Loop EndDECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE;--Open CursorOPENrs;--Loop ExecutionREPEATFETCHRs intoTmpid,gid;IF  notDone Then#执行操作IFPregatherid=Gid&&Pregatherid<> 0  ThenDELETE  fromTb_temp2wheretmp_id=tmpid;ELSESETPregatherid=GID;--SET Str=concat (GID,STR);END IF;END IF; #当_done=false when exiting by until doneENDREPEAT;/*Close Cursors*/CLOSErs;Select *  fromTB_TEMP2;SETDone=FALSE; #只有定义为false, the new loop can continue. END

Using temporary tables and cursors in MySQL stored procedures