CREATE DEFINER=`admin`@`192.168.1.%` PROCEDURE `creatPersonDatas`()
BEGIN
declare i int;
declare personid int;
declare user_name varchar(25);
set i=1;
while i<10001 do
if (i between 1 and 9) then
begin
set user_name=concat('j000000',i);
if not EXISTS (SELECT 1 from person where username=user_name) then
INSERT INTO person(username,version,birthday,password_hint,account_expired,password,level,phone_number,nick_name,
gender,first_name,qq,last_name,msn,website,hobby,pwd_prompt_answer,account_locked,email,account_enabled,
credentials_expired,pwd_prompt,account_enablead,bobby) values
(user_name,'','','','1','88888888','1','','','0','','','','','','','','1','username@163.com','1','1','','1','');
end if;
select id into personid from person where username=user_name;
insert into person_role(person_id,role_id) values(personid,2);
end;
elseif (i between 10 and 99) then
begin
set user_name=concat('j00000',i);
if not EXISTS (SELECT 1 from person where username=user_name) then
INSERT INTO person(username,version,birthday,password_hint,account_expired,password,level,phone_number,nick_name,
gender,first_name,qq,last_name,msn,website,hobby,pwd_prompt_answer,account_locked,email,account_enabled,
credentials_expired,pwd_prompt,account_enablead,bobby) values
(user_name,'','','','1','88888888','1','','','0','','','','','','','','1','username@163.com','1','1','','1','');
end if;
select id into personid from person where username=user_name;
insert into person_role(person_id,role_id) values(personid,2);
end;
elseif (i between 100 and 999) then
begin
set user_name=concat('j0000',i);
if not EXISTS (SELECT 1 from person where username=user_name) then
INSERT INTO person(username,version,birthday,password_hint,account_expired,password,level,phone_number,nick_name,
gender,first_name,qq,last_name,msn,website,hobby,pwd_prompt_answer,account_locked,email,account_enabled,
credentials_expired,pwd_prompt,account_enablead,bobby) values
(user_name,'','','','1','88888888','1','','','0','','','','','','','','1','username@163.com','1','1','','1','');
end if;
select id into personid from person where username=user_name;
insert into person_role(person_id,role_id) values(personid,2);
end;
elseif (i between 1000 and 9999) then
begin
set user_name=concat('j000',i);
if not EXISTS (SELECT 1 from person where username=user_name) then
INSERT INTO person(username,version,birthday,password_hint,account_expired,password,level,phone_number,nick_name,
gender,first_name,qq,last_name,msn,website,hobby,pwd_prompt_answer,account_locked,email,account_enabled,
credentials_expired,pwd_prompt,account_enablead,bobby) values
(user_name,'','','','1','88888888','1','','','0','','','','','','','','1','username@163.com','1','1','','1','');
end if;
select id into personid from person where username=user_name;
insert into person_role(person_id,role_id) values(personid,2);
end;
else
begin
set user_name=concat('j00',i);
if not EXISTS (SELECT 1 from person where username=user_name) then
INSERT INTO person(username,version,birthday,password_hint,account_expired,password,level,phone_number,nick_name,
gender,first_name,qq,last_name,msn,website,hobby,pwd_prompt_answer,account_locked,email,account_enabled,
credentials_expired,pwd_prompt,account_enablead,bobby) values
(user_name,'','','','1','88888888','1','','','0','','','','','','','','1','username@163.com','1','1','','1','');
end if;
select id into personid from person where username=user_name;
insert into person_role(person_id,role_id) values(personid,2);
end;
end if;
set i=i+1;
end while;
END
