mysql 存储过程select into 给变量赋值跳出while循环统计查询赋值就不会报错,代码如下，急

码拜

9年 ago

DELIMITER //
CREATE PROCEDURE userStat(IN schannelid VARCHAR(30),IN usertype VARCHAR(2))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable (
appName VARCHAR(20) PRIMARY KEY,
todayActiveUsers VARCHAR(20),
yestodayActiveUsers VARCHAR(20),
todayStarts VARCHAR(20),
yestodayStarts VARCHAR(20),
todayNewUsers VARCHAR(20),
yestodayNewUsers VARCHAR(20),
totalUserSum VARCHAR(20)
)ENGINE = MEMORY;

BEGIN
DECLARE v_apkName,v_pkgName VARCHAR(40);
DECLARE appName,todayActu,yesdayActu,todaySta,yesdaySta,todayNeu,yesdayNeu,allusers VARCHAR(40);
DECLARE Done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR SELECT apk_name,package_name FROM t_apk GROUP BY package_name ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR SQLSTATE “02000” SET Done = 1;
OPEN cur_1;
FETCH NEXT FROM cur_1 INTO v_apkName,v_pkgName;/*获取第一条记录*/
WHILE Done<>1 DO
IF usertype=”0″ THEN
/*计数当天的活跃用户数 todayActu**/
SELECT COUNT(DISTINCT(device_id)) INTO todayActu FROM t_apk_recorder
WHERE server_time >= CONCAT(CURDATE(),” 00:00:00″) AND server_time <= CONCAT(CURDATE(),” 23:59:59″) AND apk_name =v_pkgName;

/*计数当天的新用户数 todayNeu**/
SELECT COUNT(a1.device_id) INTO todayNeu FROM (SELECT DISTINCT(device_id) FROM t_apk_recorder
WHERE server_time >= CONCAT(CURDATE(),” 00:00:00″) AND server_time <= CONCAT(CURDATE(),” 23:59:59″) AND apk_name =v_pkgName )a1
WHERE a1.device_id NOT IN (SELECT a2.device_id FROM t_apkonly_recorder a2);
/*计数当天的启动次数 todaySta**/
SELECT COUNT(device_id) INTO todaySta FROM t_apk_recorder
WHERE server_time >= CONCAT(CURDATE(),” 00:00:00″) AND server_time <= CONCAT(CURDATE(),” 23:59:59″) AND apk_name = v_pkgName;
SET yesdayActu=”0″;
/**计数昨天活跃用户总数 yesdayActu**/
SELECT active_num INTO yesdayActu FROM t_statistics_activeuser ##一但没有用统计查询赋值就跳出循环
WHERE s_date=DATE_FORMAT(CURDATE()-1,”%Y-%m-%d”) AND channel_id=”0″ AND select_set=v_pkgName;

/**计数昨天启动用户总数 yesdaySta**/
#SELECT start_count INTO yesdaySta FROM t_statistics_startcount
#WHERE s_date=DATE_FORMAT(CURDATE()-1,”%Y-%m-%d”) AND channel_id=”0″ AND select_set=v_pkgName;

/**计数昨天新用户总数 yesdayNeu**/
# SELECT new_num INTO yesdayNeu FROM t_statistics_newuser
# WHERE s_date=DATE_FORMAT(CURDATE()-1,”%Y-%m-%d”) AND channel_id=”0″ AND select_set=v_pkgName;

/**得到全部用户数 allusers AND channel_id=?**/
SELECT COUNT(*) INTO allusers FROM t_apkonly_recorder
WHERE apk_name=v_pkgName;

/**插入临时表**/

INSERT INTO tmpTable VALUES(v_apkName,todayActu,yesdayActu,todaySta,yesdaySta,todayNeu,yesdayNeu,allusers);

FETCH cur_1 INTO v_apkName,v_pkgName; /*取下一条记录*/

END WHILE;
CLOSE cur_1;
SELECT * FROM tmpTable;
END;
TRUNCATE TABLE tmpTable;
END //

解决方案

BEGIN
DECLARE v_apkName,v_pkgName VARCHAR(40);
DECLARE appName,todayActu,yesdayActu,todaySta,yesdaySta,todayNeu,yesdayNeu,allusers VARCHAR(40);
DECLARE Done INT DEFAULT 0;
DECLARE Done_o INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR SELECT apk_name,package_name FROM t_apk GROUP BY package_name ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR SQLSTATE “02000” SET Done = 1;
OPEN cur_1;
FETCH NEXT FROM cur_1 INTO v_apkName,v_pkgName;/*获取第一条记录*/
WHILE Done<>1 DO
set Done_o:=Done;
IF usertype=”0″ THEN
/*计数当天的活跃用户数 todayActu**/
SELECT COUNT(DISTINCT(device_id)) INTO todayActu FROM t_apk_recorder
WHERE server_time >= CONCAT(CURDATE(),” 00:00:00″) AND server_time <= CONCAT(CURDATE(),” 23:59:59″) AND apk_name =v_pkgName;

/**得到全部用户数 allusers AND channel_id=?**/
SELECT COUNT(*) INTO allusers FROM t_apkonly_recorder
WHERE apk_name=v_pkgName;

/**插入临时表**/

INSERT INTO tmpTable VALUES(v_apkName,todayActu,yesdayActu,todaySta,yesdaySta,todayNeu,yesdayNeu,allusers);

set Done:=Done_o;

FETCH cur_1 INTO v_apkName,v_pkgName; /*取下一条记录*/

END WHILE;
CLOSE cur_1;
SELECT * FROM tmpTable;
END;
TRUNCATE TABLE tmpTable;
END //