删除 :drop procedure if exists 名称;
创建实例:
create procedure Add_Caller
(
myuid int,
fwuid int,
dt datetime,
out returnValue int
)
BEGIN
declare exresult int;
declare dtbaseiff int;
declare countCall int;
declare callnumId int;
SELECT DATEDIFF(dt, checkday) into dtbaseiff FROM hot_userinfo WHERE uid=myuid;
if dtbaseiff is null then
set dtbaseiff=1;
end if;
if dtbaseiff >0 then
UPDATE hot_userinfo SET daychecknum=1,checkday=date_format(dt,'%Y%m%d') WHERE uid=myuid;
end if;
if dtbaseiff<=0 then
UPDATE hot_userinfo SET daychecknum=daychecknum+1 WHERE uid=myuid;
end if;
update hot_userinfo set callernum=callernum+1 where uid=myuid;
set exresult=row_count();
if exresult is null then
set exresult=0;
end if;
if exresult >0 then
select count(*) into countCall from hot_caller where uid=myuid;
select callid into callnumId from hot_caller where uid=myuid and calluid=fwuid;
if callnumId is null then
set callnumId=0;
end if;
if countCall >= 20 and callnumId <=0 then
delete from hot_caller where uid=myuid order by calltime asc limit 1;
end if;
if callnumId <=0 then
insert into hot_caller(uid,calluid,calltime,marking) values(myuid,fwuid,dt,0);
end if;
if callnumId>0 then
update hot_caller set calltime=dt where callid=callnumId;
end if;
end if;
set returnValue = exresult;
END
执行存储过程:
set @id = 0;
call 名称(参数值,@id 或者 更多);
select @id as id_out;
create procedure Add_VoteStar
(
receuid int,
senduid int,
tpnum int,
dt datetime,
out returnValue int
)
BEGIN
declare exresult int;
SELECT COUNT(*) into exresult FROM hot_votestar WHERE rid=receuid AND sid=senduid AND DATE_FORMAT(votetime,'%Y-%m-%d')=DATE_FORMAT(dt,'%Y-%m-%d');
if exresult is null then
set exresult=0;
end if;
if exresult <=0 then
INSERT INTO hot_votestar(rid,sid,score,votetime) VALUES (receuid,senduid,tpnum,dt);
set exresult=row_count();
if exresult is null then
set exresult=0;
end if;
else if exresult >0 then
set exresult=2;
end if;
end if;
set returnValue = exresult;
END