mysql动态游标学习(mysql存储过程游标)

-- 建立测试表和数据
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');