记录我第一写存储过程

DELIMITER $$

USE `jshoper3x`$$

DROP PROCEDURE IF EXISTS `pro_s_jshop_active_data_count`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_s_jshop_active_data_count`()
BEGIN
	
	
	DECLARE 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:LOOP
			FETCH rs INTO membernumber,visiturl;
				IF finish =1 THEN
					LEAVE 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) THEN
					SET dayActive=(loginCount*0.7)+(publishCount*0.3);
				END IF;
				IF(jshopState=3) THEN
					SET dayActive=(loginCount*0.7)+(publishCount*0.2)+(updateCount*0.1);
				END IF;
				IF(jshopState=4) THEN
					SET 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;
	 

 

只是一段代码而已