我来把代码发上来,求各位大神,给一些优化的意见PROCEDURE hero_database_db.building_uplvel_pd( IN t_roleID INTEGER(11), IN t_buildID INTEGER(11), OUT t_cdtime integer(11), out t_remiangold integer(11), OUT t_ret INTEGER(11) ) BEGIN declare n_num INTEGER(11) DEFAULT 0; declare t_goldcoin integer(11) default 0; declare t_buildlevel integer(11) default 0; declare t_buildtablename varchar(30) default 0; declare t_pretime datetime; declare t_uplevelconsume integer(11) default 0; declare t_cdtime0 integer(11) default 0; set t_cdtime=0; set t_remiangold=0;
select count(b.RoleID),b.BuildLevel,r.GoldCoin into n_num,t_buildlevel,t_goldcoin from m_role_build_tb as b left JOIN p_login_rolebasedata_tb as r on b.RoleID=r.ID where b.BuildID=t_buildID and b.RoleID=t_roleID and b.BuildLevel<r.Level group by b.RoleID limit 0,1; #测试角色建筑的存在和等级是否小于角色等级 if n_num>0 then #得到该建筑的基础表 select FunctionTableName into t_buildtablename from b_maincity_function_unlock_tb where ID=t_buildID limit 0,1; SET @t_buildlevel = t_buildlevel; SET @t_uplevelconsume = 0; SET @t_cdtime0 = 0; #动态构造查询建筑基础表的SQL语句 SET @sql_text = concat('select UpLevelConsume,CDTime into @t_uplevelconsume,@t_cdtime0 from ', t_buildtablename, ' where Level=@t_buildlevel'); prepare stmt from @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; #得到升级建筑的金币消耗和造成的CD SELECT @t_uplevelconsume,@t_cdtime0 into t_uplevelconsume,t_cdtime0; #测试角色拥有的金币数是否大于所要消耗的金币数 if t_goldcoin>t_uplevelconsume then SET n_num = 0; select count(RoleID) into n_num from m_build_queue_tb where RoleID=t_roleID and unix_timestamp() - unix_timestamp(PreTime1) > CDTime1 limit 0,1; #测试角色的建筑队列1是否存在CD if n_num>0 then update m_build_queue_tb set PreTime1=now(),CDTime1=t_cdtime0 where RoleID=t_roleID; #更新角色的建筑队列1的上次升级时间和CD set t_remiangold=t_goldcoin-t_uplevelconsume; update p_login_rolebasedata_tb set GoldCoin=t_remiangold where ID=t_roleID; #更新角色拥有的金币数 update m_role_build_tb set BuildLevel=t_buildlevel+1 WHERE BuildID=t_buildID and RoleID=t_roleID;#更新角色的该建筑的等级 set t_cdtime=t_cdtime0; set t_ret=0; #测试角色的建筑队列2是否存在CD和是否解锁 else SET n_num = 0; select count(RoleID) into n_num from m_build_queue_tb where RoleID=t_roleID and unix_timestamp() - unix_timestamp(PreTime2) > CDTime2 and IsUnlock=1 limit 0,1; if n_num>0 then update m_build_queue_tb set PreTime2=now(),CDTime2=t_cdtime0 where RoleID=t_roleID; set t_remiangold=t_goldcoin-t_uplevelconsume; update p_login_rolebasedata_tb set GoldCoin=t_remiangold where ID=t_roleID; update m_role_build_tb set BuildLevel=t_buildlevel+1 WHERE BuildID=t_buildID and RoleID=t_roleID; set t_cdtime=t_cdtime0; set t_ret=0; else set t_ret=5;#建筑队列存在CD end if; END IF; else set t_ret=4;#金币不足 end if; else set t_ret=3; #角色等级不足 end if; END
IN t_roleID INTEGER(11),
IN t_buildID INTEGER(11),
OUT t_cdtime integer(11),
out t_remiangold integer(11),
OUT t_ret INTEGER(11)
)
BEGIN
declare n_num INTEGER(11) DEFAULT 0;
declare t_goldcoin integer(11) default 0;
declare t_buildlevel integer(11) default 0;
declare t_buildtablename varchar(30) default 0;
declare t_pretime datetime;
declare t_uplevelconsume integer(11) default 0;
declare t_cdtime0 integer(11) default 0;
set t_cdtime=0;
set t_remiangold=0;
select count(b.RoleID),b.BuildLevel,r.GoldCoin into n_num,t_buildlevel,t_goldcoin from m_role_build_tb as b left JOIN p_login_rolebasedata_tb as r on b.RoleID=r.ID
where b.BuildID=t_buildID and b.RoleID=t_roleID and b.BuildLevel<r.Level group by b.RoleID limit 0,1;
#测试角色建筑的存在和等级是否小于角色等级
if n_num>0 then
#得到该建筑的基础表
select FunctionTableName into t_buildtablename from b_maincity_function_unlock_tb where ID=t_buildID limit 0,1;
SET @t_buildlevel = t_buildlevel;
SET @t_uplevelconsume = 0;
SET @t_cdtime0 = 0;
#动态构造查询建筑基础表的SQL语句
SET @sql_text = concat('select UpLevelConsume,CDTime into @t_uplevelconsume,@t_cdtime0 from ', t_buildtablename, ' where Level=@t_buildlevel');
prepare stmt from @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#得到升级建筑的金币消耗和造成的CD
SELECT @t_uplevelconsume,@t_cdtime0 into t_uplevelconsume,t_cdtime0;
#测试角色拥有的金币数是否大于所要消耗的金币数
if t_goldcoin>t_uplevelconsume then
SET n_num = 0;
select count(RoleID) into n_num from m_build_queue_tb where RoleID=t_roleID and unix_timestamp() - unix_timestamp(PreTime1) > CDTime1 limit 0,1;
#测试角色的建筑队列1是否存在CD
if n_num>0 then
update m_build_queue_tb set PreTime1=now(),CDTime1=t_cdtime0 where RoleID=t_roleID; #更新角色的建筑队列1的上次升级时间和CD
set t_remiangold=t_goldcoin-t_uplevelconsume;
update p_login_rolebasedata_tb set GoldCoin=t_remiangold where ID=t_roleID; #更新角色拥有的金币数
update m_role_build_tb set BuildLevel=t_buildlevel+1 WHERE BuildID=t_buildID and RoleID=t_roleID;#更新角色的该建筑的等级
set t_cdtime=t_cdtime0;
set t_ret=0;
#测试角色的建筑队列2是否存在CD和是否解锁
else
SET n_num = 0;
select count(RoleID) into n_num from m_build_queue_tb where RoleID=t_roleID and unix_timestamp() - unix_timestamp(PreTime2) > CDTime2 and IsUnlock=1 limit 0,1;
if n_num>0 then
update m_build_queue_tb set PreTime2=now(),CDTime2=t_cdtime0 where RoleID=t_roleID;
set t_remiangold=t_goldcoin-t_uplevelconsume;
update p_login_rolebasedata_tb set GoldCoin=t_remiangold where ID=t_roleID;
update m_role_build_tb set BuildLevel=t_buildlevel+1 WHERE BuildID=t_buildID and RoleID=t_roleID;
set t_cdtime=t_cdtime0;
set t_ret=0;
else
set t_ret=5;#建筑队列存在CD
end if;
END IF;
else
set t_ret=4;#金币不足
end if;
else
set t_ret=3; #角色等级不足
end if;
END