存储过程小记

最新推荐文章于 2017-11-10 11:50:32 发布

Leon-Zheng

最新推荐文章于 2017-11-10 11:50:32 发布

阅读量276

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/difffate/article/details/62052687

版权

Mysql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

DELIMITER $$
CREATE PROCEDURE `PROC_UPDATELOGINADDRESS`()
    BEGIN
     DECLARE UserIdVar BIGINT(20);
     DECLARE OldUserIdVar BIGINT(20);
     DECLARE IPToIntVar BIGINT(20);
     DECLARE LastLoginIpVar VARCHAR(32);
     DECLARE LastLoginDateVar INT(11);
     DECLARE NoMoreRecords INT DEFAULT 0; 
     DECLARE AddressVar VARCHAR(300);
        DECLARE CURSOR_LOGINDATA CURSOR FOR
         SELECT t.`UserId`,t.`LastLoginIp`,t.`LastLoginDate` FROM `usercenter`.`uc_userlastlogin_new` t
          WHERE t.`LastLoginDate`=(SELECT REPLACE(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'-',''));
     DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
        OPEN CURSOR_LOGINDATA; 
        FETCH CURSOR_LOGINDATA INTO UserIdVar, LastLoginIpVar, LastLoginDateVar; 
        REPEAT
            SET AddressVar = '';
            SET OldUserIdVar = UserIdVar;
            SELECT SUBSTRING_INDEX(LastLoginIpVar,'.',1)*1000000000+SUBSTRING(SUBSTRING_INDEX(LastLoginIpVar,'.',2),INSTR(LastLoginIpVar,'.')+1)*1000000
          +SUBSTRING_INDEX(SUBSTRING_INDEX(LastLoginIpVar,'.',-2),'.',1)*1000+SUBSTRING_INDEX(LastLoginIpVar,'.',-1)*1 INTO IPToIntVar;
            SELECT `Address` INTO AddressVar FROM `pt_ipbank_new` WHERE `IPStart` <= IPToIntVar AND `IPEnd` >= IPToIntVar ;
         -- 插入IP转换后的地址数据
         INSERT INTO `userlastlogin`(`UserId`,`LastLoginDate`,`LastLoginAddress`) VALUES (UserIdVar,LastLoginDateVar,AddressVar)
          ON DUPLICATE KEY UPDATE LastLoginDate=LastLoginDateVar,LastLoginAddress=AddressVar;
         FETCH CURSOR_LOGINDATA INTO UserIdVar, LastLoginIpVar, LastLoginDateVar; 
         IF UserIdVar = OldUserIdVar THEN
          SET NoMoreRecords = 1;
         END IF;
        UNTIL NoMoreRecords = 1
        END REPEAT;
        CLOSE  CURSOR_LOGINDATA;
    END$$
DELIMITER ;

之前写的一个存储过程，总是忘记存储过程的格式，在博客上记一下，顺便回顾个函数：）

SELECT SUBSTRING_INDEX('www.111cn.net', '.', 2);

www.111cn

SELECT SUBSTRING_INDEX('www.111cn.net', '.', -2);

111cn.net