欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-25 21:01:03
复制代码 代码如下:-- 建立测试表和数据create table webuser (username varchar(10));insert into webuser v...

复制代码 代码如下:

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