MYSQL创建存储过程并使用

drop procedure if exists proc_add_user;
create procedure proc_add_user(username varchar(10), usercount int) # username前缀  usercount后缀编号
begin
  declare i int default 1; # 局部变量
  declare v_user_id int;
  declare v_address_id int;
  declare name0 char(5); # 用于字符串拼接
   while(i<=usercount) do
     if length(i)=2 then
        set name0:="000";
     elseif length(i)=3 then
        set name0:="00";
     elseif length(i)=4 then
        set name0:="0";
     elseif length(i)=5 then
        set name0:="";
     else
        set name0:="0000";
     end if;
     insert into ecs_users(email,user_name,password) values(concat(username,name0,i,"@ecshop.com"),concat(username,name0,i),md5("123456")); # 创建并插入用户数据
     select user_id into v_user_id from ecs_users where user_name=concat(username,name0,i); # 获取插入数据的user_id并赋值给v_user_id
     insert into ecs_user_address(user_id,consignee,email,country,province,city,district,address,zipcode,tel)  values(v_user_id,concat(username,name0,i),concat(username,name0,i,"@ecshop.com"),1,2,52,0,concat("六合大厦",i,"楼"),123,concat("13800000",i)); # 创建并插入地址数据
     select address_id into v_address_id from ecs_user_address where user_id=v_user_id; # 获取address_id并赋值给v_address_id
     update ecs_users set address_id=v_address_id where user_id=v_user_id; # 同步更新用户表中的地址id
     set i:=i+1; # 步进值
   end while;
end;

call proc_add_user("user", 10); # 调用通过参数创建数据