关于mysql存储过程~

删除 ：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