Mysql dynamic cursor learning (mysql stored procedure cursor) _ MySQL

Mysql dynamic cursor learning (mysql stored procedure cursor) bitsCN.com

-- Create test tables and data
Create table webuser (username varchar (10 ));
Insert into webuser values ('A1'), ('A2 '), ('A3'), ('B1'), ('B2'), ('b3 ');
Commit;

-- Create a stored procedure
Drop procedure if exists dynamic_cursor;
Delimiter //
Create procedure dynamic_cursor (IN p_name varchar (10 ))
BEGIN
DECLARE done int default 0;
DECLARE v_username varchar (10 );
DECLARE cur CURSOR for (SELECT username from webuser_view );
Declare continue handler for not found set done = 1;

Drop view if exists webuser_view;

SET @ sqlstr = "create view webuser_view ";
SET @ sqlstr = CONCAT (@ sqlstr, "SELECT username FROM webuser WHERE username like '", p_name, "% '");

PREPARE stmt FROM @ sqlstr;
EXECUTE stmt;
Deallocate prepare stmt;

OPEN cur;
F_loop: LOOP
FETCH cur INTO v_username;
IF done THEN
LEAVE f_loop;
End if;
SELECT v_username;
End loop f_loop;
CLOSE cur;
END;
//
Delimiter;

-- Test
Call dynamic_cursor ('A ');

BitsCN.com