Use of MySQL Cursors

4. Use of MySQL Cursors
8. A simple cursor of MySQL
12. Step1:
14. New Table tb_test;
18. CREATE TABLE Tb_test (
20. ID int
22. );
26. INSERT into tb_test values (' 1 ');
28. INSERT into tb_test values (' 2 ');
30. INSERT into tb_test values (' 3 ');
34. Step2:
36. Declares the stored procedure sp_test;
40. Delimiter $$
42. CREATE PROCEDURE Sp_test ()
44. BEGIN
46. DECLARE tmp int default 0;
48. DECLARE sum int default 0;
50. DECLARE done int default -1;
54. / * Declare a cursor * /
56. DECLARE mycursor cursor for select id from tb_test;
60. / * When the cursor reaches the tail, MySQL automatically sets done=1 * /
62. Declare continue handler for not found set done=1;
66. / * OPEN cursor * /
68. Open mycursor;
72. / * Cycle start * /
74. Myloop:loop
78. / * Move cursor and assign value * /
80. Fetch mycursor into TMP;
84. if do = 1 Then
86. Leave Myloop;
88. End if;
92. / * do something * /
94. Set sum = SUM + tmp;
98. / * End of Loop * /
100. End Loop Myloop;
104. INSERT into tb_test values (sum);
108. / * Close the cursor * /
110. Close MyCursor;
112. END
114. $$
116. delimiter;
120. Step3:
122. Call Sp_test ();
124. SELECT * from Tb_test ();
126. +----+
128. | ID |
130. +----+
132. | 1 |
134. | 2 |
136. | 3 |
138. | 6 |
140. +----+

Use of MySQL Cursors