Using temporal tables in MySQL cursors

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.