drop procedure if exists proc_add_user;
create procedure proc_add_user(username varchar(10), usercount int) # username前綴  usercount后綴編號(hào)
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")); # 創(chuàng)建并插入用戶數(shù)據(jù)
     select user_id into v_user_id from ecs_users where user_name=concat(username,name0,i); # 獲取插入數(shù)據(jù)的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)); # 創(chuàng)建并插入地址數(shù)據(jù)
     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; # 步進(jìn)值
   end while;
end;

call proc_add_user("user", 10); # 調(diào)用通過(guò)參數(shù)創(chuàng)建數(shù)據(jù)