存储过程小记

来源：互联网发布：mac 音视频处理软件编辑：程序博客网时间：2024/06/07 11:08

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

0 0