mysql 存储过程和函数的实例运用

2018/8/31 源自  MySQL

前言

在日常工作中总会使用一些存储过程或者函数简化代码逻辑。以下就是用户在系统注册的时候,为每个用户分配一个递增的UID为唯一标识,来记录用户的所有行踪。

increaseuid 存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `increaseuid`$$

CREATE DEFINER=`goddesscard`@`%` PROCEDURE `increaseuid`(
     IN gamename CHAR(32)
)
BEGIN
     DECLARE m_exit INT DEFAULT 0;
     DECLARE m_passport_id,m_gamekey_uid BIGINT DEFAULT 0;
     SET m_gamekey_uid=getUid(gamename);
     REPEAT
          SET m_passport_id=-1;
          SET m_gamekey_uid=m_gamekey_uid+1;

          IF isNumIdExist(m_gameKey_uid) =0 THEN
               SET m_exit=1;
          END IF;
     UNTIL m_exit= 1 END REPEAT;

     UPDATE common_gamekey SET uid=m_gamekey_uid WHERE game=gamename;
     SELECT uid FROM common_gamekey WHERE game=gamename;

END$$

DELIMITER ;

call increaseuid('goddesscard');

getUid 方法

DELIMITER $$

DROP FUNCTION IF EXISTS `getUid`$$

CREATE DEFINER = `goddesscard`@`%` FUNCTION `getUid`(
    gamename char(32)
) 
RETURNS decimal(10,0)
BEGIN
    declare m_uid bigint ;
    select uid into m_uid from common_gamekey where game=gamename;
    return m_uid;
END$$

DELIMITER ;

select getUid('goddesscard');

isNumIdExist 函数

DELIMITER $$

DROP FUNCTION IF EXISTS `isNumIdExist`$$

CREATE DEFINER = `goddesscard`@`%` FUNCTION `isNumIdExist`(
     numId BIGINT
)
RETURNS decimal(10,0)
BEGIN
    declare m_id int default -1;

    select uuid into m_id from goddesscard_device where uuid = concat(numId);
    if(m_id != -1) then  
        return 1;
    end if;
    return 0;
END$$
DELIMITER ;

select isNumIdExist(1000);

一、zhaojp_rankVIP

DELIMITER $$
DROP PROCEDURE IF EXISTS zhaojp_rankVIP $$
CREATE PROCEDURE `zhaojp_rankVIP`(
IN game VARCHAR(20),
IN gold VARCHAR(10),
IN num INT,
IN beginTime VARCHAR(20),
IN endTime VARCHAR(20)
)
BEGIN
DECLARE middleSql VARCHAR(1000);
SET middleSql := CONCAT('select p.uid,p.nickname,ifnull(max(r.value),0) as s '
,'from aries_game_rank_',game,' r,aries_user_prop p,aries_charge_order c '
,'where p.uid = r.uid and c.game= \'',game,'\' and c.cp_callback = 1 and c.uid = p.uid  and r.time between \'',beginTime,'\' and \'',endTime,'\''
,'GROUP BY c.uid having ifnull(sum(c.gold),0) > ',gold,' order by s desc limit 0,',num);
SET @final_sql = middleSql;
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    END $$
DELIMITER ;

二、查询某游戏多少人

DELIMITER $$
DROP PROCEDURE IF EXISTS zhaojp_stat_countPassportByGame $$
CREATE PROCEDURE `zhaojp_stat_countPassportByGame`(
     IN game VARCHAR(32)
)
BEGIN 
     DECLARE var INT DEFAULT 0; 
     DECLARE result INT DEFAULT 0; 
     DECLARE countSql VARCHAR(2000);
     WHILE var<9 DO
     SET var=var+1; 
          SET countSql := CONCAT('select count(distinct a.uid) into @num from aries_user_passport_',var,' a where a.game = \'',game,'\'');
          SET @final_sql = countSql;
          PREPARE stmt FROM @final_sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
         
          SET result = result + @num;
     END WHILE;
     SELECT result;
     END$$
DELIMITER ;

三、increateuid

DELIMITER $$
DROP PROCEDURE IF EXISTS `increaseuid`$$
CREATE DEFINER=`doukouwu`@`%` PROCEDURE `increaseuid`(
     IN gamename CHAR(32)
)
BEGIN
     DECLARE m_exit INT DEFAULT 0;
     DECLARE m_passport_id,m_gamekey_uid BIGINT DEFAULT 0;
     SET m_gamekey_uid=getUid(gamename);
     REPEAT
          SET m_passport_id=-1;
          SET m_gamekey_uid=m_gamekey_uid+1;
         
          IF isNumIdExist(m_gameKey_uid) =0 THEN
               SET m_exit=1;
          END IF;
     UNTIL m_exit= 1 END REPEAT;
    
     UPDATE aries_common_gamekey SET uid=m_gamekey_uid WHERE game=gamename;
     SELECT uid FROM aries_common_gamekey WHERE game=gamename;
    
END$$
DELIMITER ;

四、countPassportByGame

DELIMITER $$
DROP PROCEDURE IF EXISTS `zhaojp_stat_countPassportByGame`$$
CREATE DEFINER=`mmorpgadm`@`%` PROCEDURE `zhaojp_stat_countPassportByGame`(
     IN game VARCHAR(32)
)
BEGIN 
     DECLARE var INT DEFAULT -1; 
     DECLARE result INT DEFAULT 0; 
     DECLARE countSql VARCHAR(2000);
     WHILE var < 9 DO
     SET var=var+1; 
          SET countSql := CONCAT('select count(distinct a.uid) into @num from aries_user_passport_',var,' a where a.game = \'',game,'\'');
         
          SET @final_sql = countSql;
          PREPARE stmt FROM @final_sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
         
          SET result = result + @num;
     END WHILE;
     SELECT result;
     END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `zhaojp_stat_countPassportIncrByGame`$$
CREATE DEFINER=`mmorpgadm`@`%` PROCEDURE `zhaojp_stat_countPassportIncrByGame`(
     IN game VARCHAR(32),
     IN beginDate VARCHAR(32),
     IN endDate VARCHAR(32)
)
BEGIN
     DECLARE var INT DEFAULT -1;
     DECLARE result INT DEFAULT 0;
     DECLARE countSql VARCHAR(2000);
     WHILE var < 9 DO
     SET var=var+1;
          SET countSql := CONCAT('select count(distinct a.uid) into @num from aries_user_passport_',var,' a where a.game = \'',game,'\' and a.registerDate between \'',beginDate,'\' and \'',endDate,'\'  ');
        
          SET @final_sql = countSql;
          PREPARE stmt FROM @final_sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
        
          SET result = result + @num;
     END WHILE;
     SELECT result;
     END$$
DELIMITER ;

五:周榜排行

DELIMITER $$
USE `mmorpgadm_passport`$$
DROP PROCEDURE IF EXISTS `zhaojp_rankSumByUid`$$
CREATE DEFINER=`mmorpgadm`@`%` PROCEDURE `zhaojp_rankSumByUid`(
     IN game VARCHAR(20),
     IN uid VARCHAR(20),
     IN passport VARCHAR(50),
     IN beginTime VARCHAR(20),
     IN endTime VARCHAR(20),
     IN num INT,
     IN score VARCHAR(10),
     IN TYPE VARCHAR(50)
)
BEGIN
     DECLARE lessSql VARCHAR(2000);
   
     DECLARE middleSql2 VARCHAR(1000);
     DECLARE moreSql VARCHAR(1000);
     DECLARE v_sql VARCHAR(1000);
     DECLARE final_sql VARCHAR(2000);
     SET middleSql2 := CONCAT('select p.uid,p.nickname,ifnull(sum(r.value),0) as s from aries_game_rank_',game
                    ,' r,aries_user_prop p '
                    ,' where r.time between \'',beginTime,'\' and \'',endTime,'\' and p.uid = r.uid '
                    ,' and r.type = ',TYPE,' '
                    ,' group by r.uid having s = ',score,' ');
     SET lessSql := CONCAT('select p.uid,p.nickname,ifnull(sum(r.value),0) as s from aries_game_rank_',game
          ,' r,aries_user_prop p where p.uid = r.uid and r.time between \'',beginTime,'\' and \'',endTime,' and r.type = ',TYPE,' '
          ,'\' GROUP BY r.uid having s < ',score,' order by s DESC limit 0,',num);
     SET moreSql := CONCAT('select p.uid,p.nickname,ifnull(sum(r.value),0) as s from aries_game_rank_',game
          ,' r,aries_user_prop p where p.uid = r.uid and r.time between \'',beginTime,'\' and \'',endTime,' and r.type = ',TYPE,' '
          ,'\' GROUP BY r.uid having s > ',score,' order by s DESC limit 0,',num);
     SET @final_sql = CONCAT('select * from (',moreSql,') as result1 union all select * from (',middleSql2,') as result2 union all ',lessSql);
   
     PREPARE stmt FROM @final_sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
   
    END$$
DELIMITER ;

MySQL 账户无法执行存储过程

GRANT SELECT ON mysql.proc TO 'mmorpgadm'@'%';