Using a temporary table in a cursor in a MySQL stored procedure can override the array effect

MySQL does not support arrays. However, sometimes it is necessary to combine several tables of data, in the stored procedure, after the more complex operation obtained the results of direct output to the caller, such as the number of tables matching the conditions of some of the combined calculation of some fields, MySQL temporary table can solve the problem. Temp table: Only in the current connection, The temporary table is visible. When the connection is closed, the temporary table is automatically canceled. You must have create temporary table permission to create a temporary table. You canspecify the Create memory temp table by specifying engine = memory.

First set up the data tables and data to be used:

Drop Table if existsPerson ;Create Table' Person ' (' ID ')int( One)Primary Key  not NULL DEFAULT '0', ' age 'int( One)DEFAULT NULL, ' name 'varchar(225) not NULL) Engine=InnoDBdefaultCharSet=UTF8;Insert  intoPersonValues(1,1,'Zhangshan'),(2,2,'Lisi'),(3,3,'LST'),(4,4,'Jon'),(5,5,'Test');

Temporary tables support primary key, index designation. A connection to a non-temporal table query can use a specified primary key or index to improve performance. Examples of stored procedure statements and cursors and temporal tables:

Drop procedure if existsSp_test_tt;--determine if a stored procedure function exists if it is deleteddelimiter;;Create procedureSp_test_tt ()begin           Create Temporary Table if  not existsTmp--if the table already exists, use the keyword if not exists to prevent an error from occurring(IDvarchar(255), namevarchar( -), agevarchar( -) ) engine=memory; begin          DeclareIdsint;--Accept Query Variables        DeclareNamesvarchar(225);--Accept Query Variables        DeclareDoneint defaultFalse--Jump out of the logo        DeclareAgesint( One);--Accept Query Variables        DeclareCurcursor  for SelectId fromPerson--declaring Cursors        Declare ContinueHandler for  notFOUNDSetDone=True--Loop End set out identity        OpenCur--Start CursorLoop_lable:loop--Loops            FETCHCur intoIDs; SelectName intoNames fromPersonwhereId=IDs; SelectAge intoAges fromPersonwhereId=IDs; Insert  intotmp (Id,name,age) value (ids,names,ages); ifDone Then  --determines whether to continue the loop if done equals true to leave the loopLEAVE loop_lable;--Leave the Loop            END IF; EndLOOP;--End Loop        CLOSECur--Close Cursors     Select *  fromtmp--Querying temporary tables         End; truncate TABLEtmp--use truncate TABLE to improve performanceEnd; ;; delimiter;;

To execute a stored procedure:

Call Sp_test_tt ();

Using a temporary table in a cursor in a MySQL stored procedure can override the array effect