[MYSQL]存储过程使用范例

发表于2017-08-06
评论0 1.3k浏览
本篇文章给大家介绍的是存储过程使用范例。存储过程封装了一系列的数据库操作,这样既方便了程序中调用,也加快了数据库访问速度。同时一些简单的逻辑修改也不用直接修改程序,直接修改存储过程即可。

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

如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

标签: