delimiter $$
drop procedure if exists proc_login $$
create procedure proc_login(in name varchar(255),in pwd varchar(255))
begin
declare p varchar(255);
declare tk bigint(20);
declare ti datetime;
declare n int;
declare i timestamp;
set i = NOW();
select timekey,password into tk,p from t_misuuser where username = name;
if p is null then
set n = 0;
elseif (p != pwd) then
set n = 1;
elseif (p = pwd) then
set n = 2;
update t_misuuser set loginTime=i where timekey = tk;
end if;
select n,tk;
end $$
delimiter ;
delimiter $$
drop procedure if exists proc_insertuser $$
create procedure proc_insertuser()
begin
declare sum bigint default 10000;
declare i bigint;
select timekey into i from t_misuuser order by timekey desc limit 1;
if i is null then
set i = 0;
end if;
set i = i + 1;
set sum = sum + i;
while i < sum do
insert into t_misuuser(username,password,phonenum,email,attestState,examstate,shopinfostate,type,
onlineState,headImg,realName,sex,birthDay,constellationsCode,liveProvinceCode,liveCityCode,
liveCountyCode,homeProvinceCode,homeCityCode,homeCountyCode,loginTime,regTime) values
(i,'1','1','1','1','1','1','1','1','1','1','1','2013-05-05 00:00:00','1','1','1','1','1','1',
'1','2013-05-05 00:00:00','2013-05-05 00:00:00');
set i = i + 1;
end while;
end $$
delimiter ;
set @name = '2';
set @pwd = '1';
call proc_login(@name,@pwd);
call proc_insertuser();
select count(*) from t_misuuser;
truncate table t_misuuser;
delete from t_misuuser where timekey > 0;
insert into t_misuuser(username,password,phonenum,email,attestState,examstate,shopinfostate,type,
onlineState,headImg,realName,sex,birthDay,constellationsCode,liveProvinceCode,liveCityCode,
liveCountyCode,homeProvinceCode,homeCityCode,homeCountyCode,loginTime,regTime) values
('2','2','1','1','1','1','1','1','1','1','1','1','2013-05-05 00:00:00','1','1','1','1','1','1',
'1','2013-05-05 00:00:00','2013-05-05 00:00:00');
delimiter $$
drop procedure if exists proc_insertwaretype $$
create procedure proc_insertwaretype()
begin
declare sum bigint default 200;
declare i bigint default 1;
while i < sum do
if i < 4 then
insert into t_waretype(name,prikey) values("一级节点",0);
elseif i < 7 then
insert into t_waretype(name,prikey) values("二级节点",1);
elseif i < 10 then
insert into t_waretype(name,prikey) values("二级节点",2);
elseif i < 13 then
insert into t_waretype(name,prikey) values("二级节点",3);
elseif i < 70 then
insert into t_waretype(name,prikey) values("三级节点",4);
elseif i < 90 then
insert into t_waretype(name,prikey) values("三级节点",5);
elseif i < 110 then
insert into t_waretype(name,prikey) values("三级节点",6);
elseif i < 130 then
insert into t_waretype(name,prikey) values("三级节点",7);
elseif i < 150 then
insert into t_waretype(name,prikey) values("三级节点",8);
elseif i < 170 then
insert into t_waretype(name,prikey) values("三级节点",9);
elseif i < 180 then
insert into t_waretype(name,prikey) values("三级节点",10);
elseif i < 190 then
insert into t_waretype(name,prikey) values("三级节点",11);
elseif i < 200 then
insert into t_waretype(name,prikey) values("三级节点",12);
end if;
set i = i + 1;
end while;
end $$
delimiter ;
call proc_insertwaretype();