Mysql cursor usage

Use mysql 1.5 or above;

Test Table level;

Create Table Test. Level (name varchar (20 ));

 

Insert some data;

 

Code

/* Initialize */
Drop procedure if exists usecursor //

/* Create a stored procedure */
Create procedure usecursor ()
Begin
/* Declare */

Declare tmpName varchar (20) default '';

Declare allName varchar (255) default '';

Declare cur1 cursor for select name FROM test. level;

/* Mysql does not know why to add an exception to the judgment?

* For details, refer to the official documentation for cursor 2.11.

* The cursor is caught after an exception.

* Set the loop to jump out of the loop with the variable tmpname being null.

*/

Declare continue handler for sqlstate '20170101' SET tmpname = null;

/* Open cursor */

OPEN cur1;

/* Move the cursor down */

FETCH cur1 INTO tmpName;

/* Loop body, which obviously adds the names queried by the cursor and separates them with; signs */

WHILE (tmpname is not null) DO

Set tmpName = CONCAT (tmpName ,";");

Set allName = CONCAT (allName, tmpName );

/* Move the cursor down */

FETCH cur1 INTO tmpName;

End while;

CLOSE cur1;

Select allName;

END ;//

Call useCursor ()//

 

 

Running result:

 

Code

Mysql> call usecursor ()//

+ -------------------------------------- +

| AllName |

+ -------------------------------------- +

| F1; C3; C6; C5; C2; C4; C1; F1; F3; F4; F2; F5; |

+ -------------------------------------- +

1 row in SET (0.00 Sec)

 

 

Code

Delimiter $

Drop procedure if exits cursor_example $
Create procedure cursor_example ()
Reads SQL data
Begin
Declare l_employee_id int;
Declare l_salary numeric (8, 2 );
Declare l_department_id int;
Declare done int default 0;
Declare cur1 cursor for select employee_id, salary, department_id from employees;
Declare continue handler for not found set done = 1;

Open cur1;
Emp_loop: loop
Fetch cur1 into l_employee_id, l_salary, l_department_id;
If done = 1 then
Leave emp_loop;
End if;
End loop emp_loop;
Close cur1;
End $
Delimiter;

 

Code

/* Creation process */
Delimiter //
Drop procedure if exists test //
Create procedure Test ()
Begin
DECLARE done int default 0;
DECLARE a VARCHAR (200) DEFAULT '';
DECLARE c VARCHAR (200) DEFAULT '';

DECLARE mycursor cursor for select fusername FROM uchome_friend;
Declare continue handler for not found set done = 1;

OPEN mycursor;

REPEAT
FETCH mycursor INTO;
If not done THEN
SET c = CONCAT (c, a);/* string addition */
End if;

UNTIL done end repeat;

CLOSE mycursor;

SELECT c;
END //
DELIMITER;