Copy codeThe Code is as follows:
-- 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 ');