Using temporal tables in MySQL cursors

Sometimes we need to combine several tables of data, in the stored procedure, after a more complex operation to obtain the results of direct output to the caller, such as the number of matching tables of several fields of the combined calculation, MySQL temporary table can solve the problem.

The so-called temporary table: the temporary table is visible only in the current connection condition. 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.

Drop Table if existsPre_person;Create Table' Person ' (' ID ')int( One)Primary Key  not NULL DEFAULT '0', ' age 'int( One)DEFAULT NULL, ' name 'varchar( -) not NULL) Engine=InnoDBdefaultCharSet=UTF8;Insert  intoPersonValues(1,1,'Zhang San'),(2,2,'John Doe'),(3,3,'Harry'),(4,4,'Zhao Liu'),(5,5,'Xu Xian');

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. For example, here I use stored procedure statements and cursors and temporal tables to synthesize instances:

Drop procedure if existsSp_test;--determine if a stored procedure function exists if it is deleteddelimiter;;Create proceduresp_test ()begin           Create Temporary Table if  not existsTmp--if the table already exists, use the keyword if not exists to prevent an error from occurring(IDint( One), namevarchar(Ten), ageint(3) ) engine=memory; begin          DeclareIdsint;--Accept Query Variables        DeclareNamesvarchar(Ten);--Accept Query Variables        DeclareDoneint defaultFalse--Jump out of the logo        DeclareAgesint(3);--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;;

Then execute the stored procedure:

Call Sp_test ();

?? Part of the character encoding problem ... You can change the character set (I'm not going to change it here.) )

Using temporal tables in MySQL cursors