create
table
webuser (username
varchar
(10));
insert
into
webuser
values
(
'a1'
),(
'a2'
),(
'a3'
),(
'b1'
),(
'b2'
),(
'b3'
);
commit
;
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 as "
;
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 ;
call dynamic_cursor(
'a'
);