记录我第一写存储过程

DELIMITER $$USE `jshoper3x`$$DROP PROCEDURE IF EXISTS `pro_s_jshop_active_data_count`$$CREATE DEFINER=`root`@`%` PROCEDURE `pro_s_jshop_active_data_count`()BEGINDECLARE jshopNumber VARCHAR(36) DEFAULT NULL;DECLARE maintainerJobnumber VARCHAR(20) DEFAULT NULL;DECLARE jshopState INT DEFAULT 0;DECLARE jshopName VARCHAR(100) DEFAULT NULL;DECLARE dayActive DOUBLE(10,2) DEFAULT 0.0;/*Login,Publish,Update计数器*/DECLARE loginCount INT DEFAULT 0;DECLARE publishCount INT DEFAULT 0;DECLARE updateCount INT DEFAULT 0;DECLARE contenttype VARCHAR(20) DEFAULT NULL;DECLARE finish INT DEFAULT 0;DECLARE membernumber VARCHAR(36) DEFAULT NULL;DECLARE visiturl VARCHAR(255) DEFAULT NULL;DECLARE actiontype VARCHAR(20) DEFAULT NULL;DECLARE rs CURSOR FOR SELECT DISTINCT l.user_number,l.url FROM s_log_data_info AS l WHERE l.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY);DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish=1;OPEN rs;myloop:LOOPFETCH rs INTO membernumber,visiturl;IF finish =1 THENLEAVE myloop;END IF;/*比对当前的日志表中的url是否在url映射表中并获取对应的操作类型*/SELECT sui.action_type FROM s_urlmapping_info AS sui WHERE uri IN(SELECT  SUBSTRING_INDEX(visiturl,'?',1)) INTO actiontype;IF(actiontype='login') THEN /*如果是login类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员login动作的记录数*/SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1)) AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO loginCount;END IF;IF(actiontype='publish') THEN/*如果是publish类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员publish动作的记录数*/SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1)) AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO publishCount;END IF;IF(actiontype='update') THEN/*如果是update类型就根据当前的membernumber,过去一天,和url的比对来统计日志中当前会员update动作的记录数*/SELECT COUNT(*) FROM s_log_data_info AS s where SUBSTRING_INDEX(s.url,'?',1) IN(SELECT SUBSTRING_INDEX(visiturl,'?',1)) AND s.gmt_create >= DATE_SUB(now(),INTERVAL 1 DAY) AND s.user_number=membernumber INTO updateCount;END IF;/*获取当前会员管理的店铺唯一编号*/SELECT ma.jshop_number FROM jshop_account AS ma WHERE ma.member_number=membernumber INTO jshopNumber;/*获取当前会员的维护人*/SELECT  DISTINCT cr.maintainer_jobnumber FROM customer_relationship AS cr WHERE cr.jshop_number=jshopNumber INTO maintainerJobnumber;/*获取当前用户的店铺的探点类型*/SELECT td.state FROM tandian AS td WHERE td.jshop_number=jshopNumber INTO jshopState;/*获取当前会员的店铺名称*/SELECT m.jshop_name  FROM jshop AS m WHERE m.number=jshopNumber INTO jshopName;IF(jshopState=2) THENSET dayActive=(loginCount*0.7)+(publishCount*0.3);END IF;IF(jshopState=3) THENSET dayActive=(loginCount*0.7)+(publishCount*0.2)+(updateCount*0.1);END IF;IF(jshopState=4) THENSET dayActive=(loginCount*0.7)+(publishCount*0.2)+(updateCount*0.1);END IF;INSERT INTO s_active_data_info(`jshop_number`,`maintainer_jobnumber`,`tandian_state`,`jshop_name`,`day_active`,`gmt_create`) VALUES(jshopNumber,maintainerJobnumber,jshopState,jshopName,dayActive,now());END LOOP myLoop; CLOSE rs;END$$DELIMITER ;call pro_s_jshop_active_data_count; 

?

只是一段代码而已