MySQL uses cursors to iterate through query result sets

Source: Internet
Author: User

/* We sometimes encounter the need to traverse and do some operations (such as insertions) of the records s_s from the result set queried by the a table, and the data required for these operations may be partially from the s_s collection */
/* Temporary stored procedures, no way, not directly in the query window to do these things. */
drop procedure ifexists proc_tmp; CREATE PROCEDURE proc_tmp ()BEGIN
/* This can be written as well: DECLARE done  INT DEFAULT FALSE ; */declare doneint default 0; /* Used to determine whether to end the cycle */declare hostId bigint; /* is used to store the record of the result set s_s (because I have only one column and bigint type for the s_s record here) */
/* Define CURSOR */DECLARE idcur cursor for SelectA.hostid fromDev_host asA, Sys_hostconfig asBwhereA.hostid! =B.hostid;
/* Define the logic to set the loop end to identify how the done value changed */declare Continue Handler for notFOUNDSetDone =1;/*done = true; also */open idcur; /* OPEN cursor */REPEATfetch idcur into hostId; /* This section can read the book and fetch multiple columns (assuming that the result set s_s record is not a single column) */ifThe not -done and/* values for non-0,mysql are considered true*/insert into Sys_hostconfig (HostId, Aeleccap, Beleccap, Celeccap, Remeleccap, Atmpcap, Btmpcap, Ctmpcap, Boxtmpcap, Createtime)
/* Note that this uses hostId and now () * *values(hostId, the, the, the, -, the, the, the, the, now ()); End if; until done end repeat; Close idcur; /* Close the cursor */END call proc_tmp (); drop procedure proc_tmp; /* Delete temporary stored procedures * /

MySQL uses cursors to iterate through query result sets

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.