In mysql stored procedures, using a temporary table in the cursor can replace the array effect, mysql cursor

Source: Internet
Author: User

In mysql stored procedures, using a temporary table in the cursor can replace the array effect, mysql cursor

Mysql does not support arrays. However, sometimes the data of several tables needs to be combined. In the stored procedure, the results obtained through complicated operations are directly output to the caller, for example, the mysql temporary table can solve the problem by calculating the combinations of some fields in several tables that meet the conditions. TEMPORARY table: the TEMPORARY table is visible only during the current connection. When the connection is closed, the TEMPORARY table is automatically canceled. You must have the create temporary table permission to create a temporary table. You can specify engine = memory; to create a temporary memory table.

First, create the data table and data to use:

drop table if exists  person;create table `person` (  `id` int(11)primary key NOT NULL DEFAULT '0',  `age` int(11) DEFAULT NULL,  `name` varchar(225) not null) engine=innodb default charset=utf8;insert into person values(1,1,'zhangshan'),(2,2,'lisi'),(3,3,'lst'),(4,4,'jon'),(5,5,'test');

Temporary tables support specifying primary keys and indexes. You can use the specified primary key or index to improve the performance of non-temporary table queries. Example of stored procedure statements, cursors, and temporary tables:

Drop procedure if exists sp_test_tt; -- determines whether the stored procedure function exists. if delimiter is deleted, create procedure sp_test_tt () begin create temporary table if not exists tmp -- if the table already exists, use the keyword if not exists to prevent errors (id varchar (255), name varchar (50), age varchar (500) engine = memory; begin declare ids int; -- accepts the query variable declare names varchar (225); -- accepts the query variable declare done int default false; -- jumps out of the tag declare ages int (11 ); -- accept the query variable declare cur cursor for select id from person; -- declare the cursor declare continue handler for not FOUND set done = true; -- the loop end setting jumps out of the Mark open cur; -- start cursor LOOP_LABLE: loop -- loop FETCH cur INTO ids; select name into names from person where id = ids; select age into ages from person where id = ids; insert into tmp (id, name, age) value (ids, names, ages); if done THEN -- determine whether to continue the loop if done is equal to true, LEAVE the loop LEAVE LOOP_LABLE; -- exit loop end if; end LOOP; -- end loop close cur; -- CLOSE cursor select * from tmp; -- Query temporary table end; truncate TABLE tmp; -- use truncate TABLE to improve performance end; delimiter ;;

Execute the stored procedure:

call sp_test_tt();

Related Article

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.