Cursors and multiple-cursor instances in MySQL stored procedures

Cases

The code is as follows

DELIMITER $$ DROP PROCEDURE IF EXISTS getuserinfo $$ CREATE PROCEDURE GetUserInfo (in Date_day datetime) -- --Example --MySQL stored procedure name: GetUserInfo --Parameter: date_day date format: 2008-03-08 -- BEGIN DECLARE _username varchar (12); --User name declare _chinese int; --Chinese declare _math int; --Mathematics declare done int; --Defining cursors DECLARE rs_cursor cursor for SELECT username,chinese,math from UserInfo where DateDiff (createdate, date_day) = 0; DECLARE CONTINUE HANDLER for not FOUND SET done=1; --Get yesterday's date If Date_day is null then Set date_day = Date_add (now (), Interval-1 Day); End If; Open rs_cursor; Cursor_loop:loop FETCH rs_cursor into _username, _chinese, _math; --Take data If Done=1 Then Leave Cursor_loop; End If; --Update table Update infosum set total=_chinese+_math where Username=_username; End Loop Cursor_loop; Close rs_cursor; end$$ DELIMITER;

These are examples of MySQL stored procedures using cursors, and if multiple cursors are used, I'll use the same example below.

MySQL's stored procedures make it easy to use cursors to implement functions, and the stored procedures are written in the following directions:

First create a table and insert some test data:

The code is as follows
DROP TABLE IF EXISTS netingcn_proc_test; CREATE TABLE ' Netingcn_proc_test ' ( ' ID ' INTEGER (one) not NULL auto_increment, ' Name ' VARCHAR (20), ' Password ' VARCHAR (20), PRIMARY KEY (' id ') ) Engine=innodb; Insert into netingcn_proc_test (name, password) values (' Procedure1 ', ' Pass1 '), (' Procedure2 ', ' pass2 '), (' Procedure3 ', ' pass3 '), (' Procedure4 ', ' pass4 ');

Here is an example of a simple stored procedure:

The code is as follows

drop procedure IF EXISTS Test_proc; Delimiter// CREATE PROCEDURE Test_proc () Begin --Declare a flag done to determine whether the cursor is completed DECLARE done INT DEFAULT 0; --Declares a variable that holds the data extracted from the cursor --Pay special attention to the fact that the name here cannot be the same as the one used in the cursor, otherwise the resulting data is null DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; --Declaring a cursor corresponding to the SQL statement DECLARE cur CURSOR for Select name, password from netingcn_proc_test; --Set the done to 1 at the end of the cursor loop DECLARE CONTINUE HANDLER for does FOUND SET done = 1; --Execute Query Open cur; --traverse the cursor for each row REPEAT --Put a line of information in the corresponding variable FETCH cur into tname, tpass; If not done then --Here you can use Tname, tpass the corresponding information. Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur; End // delimiter;

--Execute the stored procedure
Call Test_proc (); Note that the declaration of the variable, the declaration of the cursor, and the order of the handler declaration cannot be mistaken, you must declare the variable first, then declare the cursor, and then state the handler. There is only one cursor in the example of the stored procedure mentioned above, so if you want to use two or more cursors, it's very simple, so to speak, how to use two. Examples are as follows:

The code is as follows

drop procedure IF EXISTS test_proc_1; Delimiter// CREATE PROCEDURE Test_proc_1 () Begin DECLARE done INT DEFAULT 0; DECLARE Tid int (one) DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; DECLARE cur_1 CURSOR for Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_1; REPEAT FETCH cur_1 into Tname, Tpass; If not done then Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur_1; --note here, be sure to reset the done value to 0 Set done = 0; Open cur_2; REPEAT FETCH cur_2 into Tid, tname; If not done then Select Tid, Tname; End If; UNTIL do end REPEAT; Close cur_2; End // delimiter; Call Test_proc_1 ();

The above code is basically the same as in the first example, with one more cursor declaration and traversal cursor. Note here that the set done = 0 is used before traversing the second cursor, because when the first cursor traverses the play, its value is set to 1, and the second cursor is not traversed if it is set to 0 without the set handler. Of course, the good habit is to use the statement before each open cursor operation to ensure that the cursor is truly traversed. Of course, you can also work with multiple cursors in a nested way with the BEGIN statement block, for example:

The code is as follows

drop procedure IF EXISTS test_proc_2; Delimiter// CREATE PROCEDURE test_proc_2 () Begin DECLARE done INT DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; DECLARE cur_1 CURSOR for Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_1; REPEAT FETCH cur_1 into Tname, Tpass; If not done then Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur_1; Begin DECLARE done INT DEFAULT 0; DECLARE Tid int (one) DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_2; REPEAT FETCH cur_2 into Tid, tname; If not done then Select Tid, Tname; End If; UNTIL do end REPEAT; Close cur_2; End End // delimiter; Call test_proc_2 ();

About the jump and continue of the cursor operation of stored procedures in MySQL

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch