换了一种写法,不使用游标访问,直接取临时表数据,绕过游标声明问题:代码如下 BEGIN DECLARE v_querySqlUnion VARCHAR(50000); # union 所有结果集 DECLARE v_querySql VARCHAR(1000); # union 所有结果集 DECLARE v_queryFrom VARCHAR(500); DECLARE v_queryField VARCHAR(300); DECLARE whereCondition VARCHAR(300); DECLARE v_rpDate VARCHAR(50);#游标报表日期 DECLARE v_qryDate VARCHAR(50);#游标查询时间 DECLARE done INT DEFAULT FALSE; DECLARE v_minId INTEGER; DECLARE v_maxId INTEGER; #删除临时表 DROP TEMPORARY TABLE IF EXISTS tmp_date; #创建时间统计维度临时表 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_date( tmp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, queryDate varchar(20) not null, ReportDate VARCHAR(30) not null ); #将时间维度插入临时表 CASE ReportRange when 0 then #按自由时间间隔天数查询 WHILE endTime>startTime DO set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d')); INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate ); set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY)); END WHILE; when 1 then #按 单天 查询 set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d')); INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate ); when 2 then #按每周 7 日查询 set @var=1; set endTime=(SELECT DATE_FORMAT (endTime ,'%Y-%m-%d')); #调整时间为每天结束 23:59:59 set endTime=CONCAT(endTime,' 23:59:59'); WHILE @var<7 DO set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d')); INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime,@rpDate ); #时间间隔控制 set @var=@var+1; set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY)); END WHILE; when 3 then #按每月 4~5 自然周查询
WHILE endTime>startTime DO #拼接起止时间作为 报表时间字段 2014.12.08~2014.12.14 形式 set @sDate=(SELECT DATE_FORMAT( (SELECT DATE_ADD(endTime,INTERVAL -WEEKDAY(endTime) day)),'%Y.%m.%d') ); set @eDate=(SELECT DATE_FORMAT(endTime,'%Y.%m.%d') ); set @rpDate=CONCAT(@sDate,'~',@eDate);
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate ); #时间间隔控制 set endTime=(SELECT DATE_ADD(endTime,INTERVAL -WEEKDAY(endTime)+6 day));#获得此周结束时间间 set endTime=(SELECT DATE_SUB(endTime,INTERVAL 1 WEEK));#从结束时间递减 一周(7天)直至 小于 开始时 SELECT CONCAT(endTime,' 23:59:59'); END WHILE; when 4 then #按每年 12 个自然月查询 WHILE endTime>startTime DO set @rpDate=(SELECT DATE_FORMAT(endTime,'%Y.%m')); INSERT INTO tmp_date(queryDate,ReportDate) VALUES(endTime,@rpDate); #时间间隔控制, 取得每个月最后一天日期的结束时间 23:59:59 set endTime=(SELECT DATE_SUB(endTime,INTERVAL 1 MONTH)); set endTime=(SELECT LAST_DAY(endTime));#确保是当月最后一天, INTERVAL 扣除天数 30,31 的月份不准 set endTime=CONCAT(endTime,' 23:59:59'); END WHILE; ELSE #按自由天数查询 WHILE endTime>startTime DO set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d')); INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate ); set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY)); END WHILE; END CASE;
#循环取报表时间 SELECT MIN(tmp_id) INTO v_minId FROM tmp_date; SELECT MAX(tmp_id) INTO v_maxId FROM tmp_date;
WHILE v_minId <= v_maxId DO set v_querySql='';
SELECT queryDate,ReportDate INTO v_qryDate,v_rpDate from tmp_date where tmp_id=v_minId; #店铺id set whereCondition=CONCAT(" and st.CompanyId='", compId,"' "); #商品分类 IF productCategoryId is NOT NULL && productCategoryId <> '' THEN set whereCondition=CONCAT(whereCondition," and pd.CycleCode like %", productCategoryId,"% "); END IF; #商品id IF productId is NOT NULL && productId <> '' THEN set whereCondition=CONCAT(whereCondition," and st.ProductId='", productId,"'"); END IF; #时间维度 SET whereCondition=CONCAT(whereCondition," and st.BillDate <='",v_qryDate,"'"); #统计字段 SET v_queryField=CONCAT("select '",v_rpDate,"' as ReportDate, coalesce(Sum(LastAmount),0) as TotalAmount, coalesce(Sum(LastCount),0) as TotalCount "); #查询表与内嵌 SET v_queryFrom=CONCAT(" from (select * from (select st.LastAmount,st.LastCount,st.ProductId,st.BillDate from kos_stocks_bo st LEFT JOIN kos_product pd on st.ProductId=pd.PD_ID LEFT JOIN kos_product_category pc on pd.ProductCategoryId = pc.PC_ID where 1=1 ",whereCondition," ORDER BY BillDate desc) stock1 Group by ProductId) stock "); #单条查询语句 SET v_querySql=CONCAT(v_queryField,v_queryFrom); #合并查询sql,得到一个结果集 IF v_minId>1 THEN set v_querySqlUnion=CONCAT(v_querySqlUnion," union all ",v_querySql); ELSE set v_querySqlUnion=CONCAT(v_querySql); END IF; set v_minId= v_minId+1; END WHILE;
set @v_sql=v_querySqlUnion; prepare stmt from @v_sql; #预处理需要执行的动态SQL,其中stmt是一个变量 EXECUTE stmt; #执行SQL语句 deallocate prepare stmt;
#删除临时表 DROP TEMPORARY TABLE IF EXISTS tmp_date; END 此外, thinkphp 3.2 框架,调用存储过程有点问题,不能得到结果。解决方法如下: http://blog.csdn.net/cyd1919/article/details/8859898 或者不改框架,在调用存储程时加个空格如: $pInventory=" call p_InventoryProc([parmas])",如下: http://www.thinkphp.cn/bug/2600.html
一种解决方法就是不要用临时表,而使用普通表。可以在普通表中添加一列标志,作为SESSION的标记不,当处理完记录后,则删除表中所有这个SESSIONID的记录。
BEGIN
DECLARE v_querySqlUnion VARCHAR(50000); # union 所有结果集
DECLARE v_querySql VARCHAR(1000); # union 所有结果集
DECLARE v_queryFrom VARCHAR(500);
DECLARE v_queryField VARCHAR(300);
DECLARE whereCondition VARCHAR(300);
DECLARE v_rpDate VARCHAR(50);#游标报表日期
DECLARE v_qryDate VARCHAR(50);#游标查询时间
DECLARE done INT DEFAULT FALSE; DECLARE v_minId INTEGER;
DECLARE v_maxId INTEGER; #删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_date; #创建时间统计维度临时表
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_date(
tmp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
queryDate varchar(20) not null,
ReportDate VARCHAR(30) not null
); #将时间维度插入临时表
CASE ReportRange
when 0 then #按自由时间间隔天数查询
WHILE endTime>startTime DO
set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d'));
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate );
set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY));
END WHILE; when 1 then #按 单天 查询
set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d'));
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate ); when 2 then #按每周 7 日查询
set @var=1;
set endTime=(SELECT DATE_FORMAT (endTime ,'%Y-%m-%d')); #调整时间为每天结束 23:59:59
set endTime=CONCAT(endTime,' 23:59:59');
WHILE @var<7 DO
set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d'));
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime,@rpDate );
#时间间隔控制
set @var=@var+1;
set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY));
END WHILE;
when 3 then #按每月 4~5 自然周查询
WHILE endTime>startTime DO
#拼接起止时间作为 报表时间字段 2014.12.08~2014.12.14 形式
set @sDate=(SELECT DATE_FORMAT( (SELECT DATE_ADD(endTime,INTERVAL -WEEKDAY(endTime) day)),'%Y.%m.%d') );
set @eDate=(SELECT DATE_FORMAT(endTime,'%Y.%m.%d') );
set @rpDate=CONCAT(@sDate,'~',@eDate);
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate );
#时间间隔控制
set endTime=(SELECT DATE_ADD(endTime,INTERVAL -WEEKDAY(endTime)+6 day));#获得此周结束时间间
set endTime=(SELECT DATE_SUB(endTime,INTERVAL 1 WEEK));#从结束时间递减 一周(7天)直至 小于 开始时
SELECT CONCAT(endTime,' 23:59:59');
END WHILE;
when 4 then #按每年 12 个自然月查询
WHILE endTime>startTime DO
set @rpDate=(SELECT DATE_FORMAT(endTime,'%Y.%m'));
INSERT INTO tmp_date(queryDate,ReportDate) VALUES(endTime,@rpDate);
#时间间隔控制, 取得每个月最后一天日期的结束时间 23:59:59
set endTime=(SELECT DATE_SUB(endTime,INTERVAL 1 MONTH));
set endTime=(SELECT LAST_DAY(endTime));#确保是当月最后一天, INTERVAL 扣除天数 30,31 的月份不准
set endTime=CONCAT(endTime,' 23:59:59');
END WHILE;
ELSE #按自由天数查询
WHILE endTime>startTime DO
set @rpDate=(SELECT DATE_FORMAT (endTime ,'%Y.%m.%d'));
INSERT INTO tmp_date(queryDate,ReportDate) VALUES( endTime, @rpDate );
set endTime=(SELECT DATE_SUB(endTime, INTERVAL 1 DAY));
END WHILE;
END CASE;
#循环取报表时间
SELECT MIN(tmp_id) INTO v_minId FROM tmp_date;
SELECT MAX(tmp_id) INTO v_maxId FROM tmp_date;
WHILE v_minId <= v_maxId DO
set v_querySql='';
SELECT queryDate,ReportDate INTO v_qryDate,v_rpDate from tmp_date where tmp_id=v_minId; #店铺id
set whereCondition=CONCAT(" and st.CompanyId='", compId,"' ");
#商品分类
IF productCategoryId is NOT NULL && productCategoryId <> '' THEN
set whereCondition=CONCAT(whereCondition," and pd.CycleCode like %", productCategoryId,"% ");
END IF;
#商品id
IF productId is NOT NULL && productId <> '' THEN
set whereCondition=CONCAT(whereCondition," and st.ProductId='", productId,"'");
END IF;
#时间维度
SET whereCondition=CONCAT(whereCondition," and st.BillDate <='",v_qryDate,"'");
#统计字段
SET v_queryField=CONCAT("select '",v_rpDate,"' as ReportDate, coalesce(Sum(LastAmount),0) as TotalAmount, coalesce(Sum(LastCount),0) as TotalCount ");
#查询表与内嵌
SET v_queryFrom=CONCAT(" from (select * from (select st.LastAmount,st.LastCount,st.ProductId,st.BillDate from kos_stocks_bo st LEFT JOIN kos_product pd on st.ProductId=pd.PD_ID LEFT JOIN kos_product_category pc on pd.ProductCategoryId = pc.PC_ID where 1=1 ",whereCondition," ORDER BY BillDate desc) stock1 Group by ProductId) stock ");
#单条查询语句
SET v_querySql=CONCAT(v_queryField,v_queryFrom);
#合并查询sql,得到一个结果集
IF v_minId>1 THEN
set v_querySqlUnion=CONCAT(v_querySqlUnion," union all ",v_querySql);
ELSE
set v_querySqlUnion=CONCAT(v_querySql);
END IF;
set v_minId= v_minId+1;
END WHILE;
set @v_sql=v_querySqlUnion;
prepare stmt from @v_sql; #预处理需要执行的动态SQL,其中stmt是一个变量
EXECUTE stmt; #执行SQL语句
deallocate prepare stmt;
#删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_date;
END
此外, thinkphp 3.2 框架,调用存储过程有点问题,不能得到结果。解决方法如下:
http://blog.csdn.net/cyd1919/article/details/8859898 或者不改框架,在调用存储程时加个空格如: $pInventory=" call p_InventoryProc([parmas])",如下:
http://www.thinkphp.cn/bug/2600.html