[原创]一个存储过程实现（CSDN问题答案）

早上有人求助，我写出答案如下，觉得这个问题还算有代表性：
不过MYSQL的预处理好像得用动态变量，静态的不认。老出错。
------------------
表[A],2个字段，name,num,
数据为：
name num
tom 3
jerry 2
jessic 2
lily 1


希望通过一段SQL语句得到以下的数据集：
tom
tom
tom
jerry
jerry
jessic
jessic
lily

就是根据NUM的值，重复几次NAME。

--------------------------------------
贴出完成版
create table test(name varchar(64),num int(11));
insert into test values('tom',3),('jerry',2),('jessic',2),('lily',1);
---------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_test`$$

CREATE PROCEDURE `test`.`sp_test`()
BEGIN
declare cnt int(11);
declare i int(11);
declare j int(11);
  create temporary table num (name varchar(64),num int(11));
  select count(*) from test where 1 = 1 into cnt;
  set i = 0;
  loop1:loop
    set @query1 = concat('select name,num from test limit ',i,',1 into @name,@num1');
    prepare stmt1 from @query1;
    execute stmt1;
    deallocate prepare stmt1;
    set j = 1;
    loop2:loop
      set @query2 = 'insert into num values(?,?)';
      prepare stmt2 from @query2;
      execute stmt2 using @name,@num1;
      deallocate prepare stmt2;    
      set j = j + 1;
      if j > @num1 then
        leave loop2;
      end if;
    end loop loop2;
    set i = i + 1;
    if i > cnt -1 then
      leave loop1;
    end if;
  end loop loop1;
  select name  from num;   
    
END$$

DELIMITER ;
------------------
call sp_test();

query result(8 records)
name   
tom    
tom    
tom    
jerry  
jerry   
jessic   
jessic   
lily