MySQL Cursor Management

After understanding the cursors on Oracle, and then going through MySQL's description of the cursor, we found that this piece is relatively simple, with an example to illustrate how MySQL cursors can be used.

The use of cursors is divided into 4 steps: Opening cursors (open), cursor loops (loop,leave), fetching data (fetch), closing cursors (close).

Here are the examples from the official MySQL document (5.5):

CREATE PROCEDURE Curdemo ()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE a CHAR (16);

DECLARE B, C INT;

DECLARE cur1 CURSOR for SELECT id,data from Test.t1; #声明游标

DECLARE cur2 CURSOR for SELECT i from Test.t2;

DECLARE CONTINUE HANDLER for not FOUND SET done = TRUE; #游标中找不到值时跳出

OPEN Cur1;

OPEN CUR2;

Read_loop:loop

FETCH Cur1 into a, b;

FETCH cur2 into C;

IF do Then

LEAVE Read_loop; #这里相当于exit when Cursor%notfound

END IF;

IF B < C then

INSERT into Test.t3 VALUES (A, b);

ELSE

INSERT into Test.t3 VALUES (a,c);

END IF;

END LOOP;

CLOSE Cur1; #关闭游标

CLOSE CUR2;

END;

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1870898

MySQL Cursor Management