数据库教程:[MYSQL]存储过程使用范例

存储过程封装了一系列的操作，这样既方便了程序中调用，也加快了数据库访问速度。同时一些简单的逻辑修改也不用直接修改程序，直接修改存储过程即可。

  create definer=`admin`@`%` procedure `addrankex`(in _gameid varchar(32), in _deviceid varchar(64), in _ip varchar(32), in _level int, in _score int, in _cleartime int)  begin      declare _errcode int default 0;      #发生异常时_errcode置1      declare continue handler for sqlexception set _errcode = 1;#非声明语句必须放在所有声明语句的后面，否者会报错。        start transaction;        #初始化参数      set @deviceid = _deviceid;      set @ip = _ip;      set @level = _level;      set @score = _score;      set @cleartime = _cleartime;      set @sameusercount = 0;      set @currenttime = now();      set @best = 0;      set @current = 0;        set @strsql = concat('select count(*) into @sameusercount from ', _gameid, '_rank where deviceid=@deviceid and level=@level');      prepare stmt from @strsql;      execute stmt;      deallocate prepare stmt;        if @sameusercount > 0 then              #更新          set @strsql = concat('update ', _gameid, '_rank set score=@score,cleartime=@cleartime,ip=@ip,time=@currenttime where deviceid=@deviceid and level=@level and score<@score');          prepare stmt from @strsql;          execute stmt;          deallocate prepare stmt;      else          #添加          set @strsql = concat('insert into ', _gameid, '_rank (deviceid, level, score, cleartime, ip, count, time) values (@deviceid, @level, @score, @cleartime, @ip, 1, @currenttime)');          prepare stmt from @strsql;          execute stmt;          deallocate prepare stmt;      end if;        set @strsql = concat('update ', _gameid, '_rank set count=count+1 where deviceid=@deviceid and level=@level');      prepare stmt from @strsql;      execute stmt;      deallocate prepare stmt;        #错误处理      if _errcode = 0 then          commit;#提交            set @strsql = concat('select (count(*) + 1) into @best from ', _gameid, '_rank where deviceid!=@deviceid and level=@level and score>(select score from ', _gameid, '_rank where deviceid=@deviceid and level=@level)');          prepare stmt from @strsql;          execute stmt;          deallocate prepare stmt;            set @strsql = concat('select (count(*) + 1) into @current from ', _gameid, '_rank where deviceid!=@deviceid and level=@level and score>@score');          prepare stmt from @strsql;          execute stmt;          deallocate prepare stmt;            select @current as current,@best as best;      else          rollback;#回滚          select _errcode as errcode;      end if;  end

需要了解更多数据库技术:存储过程使用范例，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!