作者:深圳中诚环球发展有限公司 | 来源:互联网 | 2022-12-26 14:59
mySql存储过程游标必须定义在临时表前?编译过了,但是,调用时发生临时不存在错误,求指导。存储过程如下dropPROCEDUREifEXISTSp_InventoryProc;CRE
mySql 存储过程游标必须定义在临时表前?
编译过了,但是,调用时发生临时不存在错误,求指导。存储过程如下
drop PROCEDURE if EXISTS p_InventoryProc;
CREATE PROCEDURE `p_InventoryProc`(in compId VARCHAR(36), in ReportRange INTEGER, in startTime VARCHAR(36), in endTime VARCHAR(36),in productCategoryId VARCHAR(36),in productId VARCHAR(36) )
BEGIN
DECLARE v_querySqlUnion VARCHAR(5000); # union 所有结果集
DECLARE v_querySql VARCHAR(500); # union 所有结果集
DECLARE v_queryFrom VARCHAR(200);
DECLARE v_queryField VARCHAR(100);
DECLARE whereCondition VARCHAR(100);
DECLARE v_rpDate VARCHAR(50);#游标报表日期
DECLARE v_qryDate VARCHAR(50);#游标查询时间
DECLARE done INT DEFAULT FALSE;
#循环取同临时表的时间用于统计 条件 : BillDate <= 当前时间值
DECLARE c_cursor CURSOR FOR SELECT queryDate,ReportDate from tmp_state;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
#创建时间统计维度临时表
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_date(queryDate varchar(20) not null, ReportDate VARCHAR(30) not null);
#将时间维度插入临时表
CASE ReportRange
when 2 then #按每周 7 日查询
set @var=1;
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;
#店铺id
set whereCOndition=CONCAT('and st.CompanyId=', compId);
#商品分类
IF productCategoryId is NULL || productCategoryId = '' THEN
set whereCOndition=CONCAT('and pd.CycleCode like %', productCategoryId,'% ');
END IF;
#商品id
IF productId is NULL || productId = '' THEN
set whereCOndition=CONCAT('and st.ProductId=', productId);
END IF;
#开启游标查询数据
set @cFlag=0;
OPEN c_cursor;
REPEAT
FETCH c_cursor INTO v_qryDate,v_rpDate;
SET whereCOndition=CONCAT(' and st.BillDate <=',v_qryDate);
SET v_queryField=CONCAT('select ',v_rpDate,' as ReportDate, Sum(LastAmount) as TotalAmount, Sum(LastCount) 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) Group by ProductId) stock ');
SET v_querySql=CONCAT(v_queryField,v_queryFrom);
IF @cFlag>0 THEN
set v_querySqlUnion=CONCAT(' union all ',v_querySql);
ELSE
set v_querySqlUnion=CONCAT(v_querySql);
END IF;
set @cFlag= @cFlag+1;
UNTIL done
END REPEAT;
CLOSE c_cursor;
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;
3 个解决方案
这个没有办法,这是MYSQL的特性。
一种解决方法就是不要用临时表,而使用普通表。可以在普通表中添加一列标志,作为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