表1:SOLDDETAIL 销售明细,大概500万条记录,主要统计销售汇总
表2:hr_mergeshop店组
内容:
id,storecode
1,S001
1,S002
2,N001
2,N002
表三,hr_month_tartget,记录月店铺总销售目标与总实际销售内容:ym_date,storecode,实际销售amtsold。下面的过程是实现销售汇总(根据hr_mergeshop的内如,比如S001与S002的销售合并,分别记录到hr_month_tartget的S001与S002 amtsold)假如S001与S002两店铺的实际销售合计为123456元那么在hr_month_tartget表中就是:
200807,s001,123456
200807,s002,123456N001,N002 同样下面是我的过程,不知道有什么可以优化,或者其他的思路,我用到了游标:
CREATE OR REPLACE PROCEDURE hr_mergeshop_amtsold(p_month char) IS
--最后修改日期:2008年11月11日 v_store_group hr_mergeshop.store_group%TYPE; --定义店组变量 CURSOR cv_store_group IS --定义店组游标
SELECT DISTINCT store_group FROM hr_mergeshop;BEGIN
OPEN cv_store_group; LOOP
FETCH cv_store_group
INTO v_store_group;
EXIT WHEN cv_store_group%NOTFOUND;
UPDATE hr_month_tartget
SET amtsold = (SELECT SUM(xf_amtsold)
FROM SOLDDETAIL t
WHERE EXISTS
(SELECT 1
from hr_mergeshop
where store_code = t.xf_storecode
and store_group = v_store_group)
AND xf_txdate >=
TO_DATE(p_month || '01', 'yyyymm' || 'DD')
AND 1 > xf_txdate -
LAST_DAY(TO_DATE(p_month || '01',
'yyyymm' || 'DD')))
WHERE EXISTS (SELECT 1
FROM hr_mergeshop
WHERE store_code = hr_month_tartget.storecode
AND store_group = v_store_group)
AND ym_date = p_month;
END LOOP; CLOSE cv_store_group;END;
表2:hr_mergeshop店组
内容:
id,storecode
1,S001
1,S002
2,N001
2,N002
表三,hr_month_tartget,记录月店铺总销售目标与总实际销售内容:ym_date,storecode,实际销售amtsold。下面的过程是实现销售汇总(根据hr_mergeshop的内如,比如S001与S002的销售合并,分别记录到hr_month_tartget的S001与S002 amtsold)假如S001与S002两店铺的实际销售合计为123456元那么在hr_month_tartget表中就是:
200807,s001,123456
200807,s002,123456N001,N002 同样下面是我的过程,不知道有什么可以优化,或者其他的思路,我用到了游标:
CREATE OR REPLACE PROCEDURE hr_mergeshop_amtsold(p_month char) IS
--最后修改日期:2008年11月11日 v_store_group hr_mergeshop.store_group%TYPE; --定义店组变量 CURSOR cv_store_group IS --定义店组游标
SELECT DISTINCT store_group FROM hr_mergeshop;BEGIN
OPEN cv_store_group; LOOP
FETCH cv_store_group
INTO v_store_group;
EXIT WHEN cv_store_group%NOTFOUND;
UPDATE hr_month_tartget
SET amtsold = (SELECT SUM(xf_amtsold)
FROM SOLDDETAIL t
WHERE EXISTS
(SELECT 1
from hr_mergeshop
where store_code = t.xf_storecode
and store_group = v_store_group)
AND xf_txdate >=
TO_DATE(p_month || '01', 'yyyymm' || 'DD')
AND 1 > xf_txdate -
LAST_DAY(TO_DATE(p_month || '01',
'yyyymm' || 'DD')))
WHERE EXISTS (SELECT 1
FROM hr_mergeshop
WHERE store_code = hr_month_tartget.storecode
AND store_group = v_store_group)
AND ym_date = p_month;
END LOOP; CLOSE cv_store_group;END;
我以前一个同事用游标的一个存储过程,分析一个月的数据,需要近四个小时我用SQL改写,分析三个月的,也只要1-2分钟
没有INSERT的
我同事写的那个,是要每次先把当月已经生成过的数据删除,然后写入大概1000条纪录,然后再根据游标分析前一个月的数据,
每分析一条明细就去UPDATE一次而我同样用SQL写出来,用MERGE实现,除非有些目标数据是要删除的,否则的话,比他写的快多了
1,S001
1,S002
2,N001
2,N002
的分组条件,如果是以ID分组的,那太简单了
UPDATE HR_MONTH_TARTGET
SET AMTSOLD = (SELECT SUM(XF_AMTSOLD)
FROM SOLDDETAIL T
WHERE EXISTS (SELECT 1
FROM HR_MERGESHOP
WHERE STORE_CODE = T.XF_STORECODE
AND STORE_GROUP = STORE_GROUP)
AND XF_TXDATE >=
TO_DATE(&P_MONTH || '01', 'YYYYMM' || 'DD')
AND 1 > XF_TXDATE -
LAST_DAY(TO_DATE(&P_MONTH || '01',
'YYYYMM' || 'DD')))
WHERE EXISTS (SELECT 1
FROM HR_MERGESHOP
WHERE STORE_CODE = HR_MONTH_TARTGET.STORECODE
AND STORE_GROUP = STORE_GROUP)
AND YM_DATE = &P_MONTH;
UPDATE HR_MONTH_TARTGET
SET AMTSOLD = (SELECT SUM(XF_AMTSOLD)
FROM SOLDDETAIL T
WHERE EXISTS (SELECT 1
FROM HR_MERGESHOP
WHERE STORE_CODE = T.XF_STORECODE)
AND XF_TXDATE >=
TO_DATE(&P_MONTH || '01', 'YYYYMM' || 'DD')
AND 1 > XF_TXDATE -
LAST_DAY(TO_DATE(&P_MONTH || '01',
'YYYYMM' || 'DD')))
WHERE EXISTS (SELECT 1
FROM HR_MERGESHOP
WHERE STORE_CODE = HR_MONTH_TARTGET.STORECODE)
AND YM_DATE = &P_MONTH;
AND xf_txdate >=
TO_DATE(p_month || '01', 'yyyymm' || 'DD')
AND 1 > xf_txdate -
LAST_DAY(TO_DATE(p_month || '01',
'yyyymm' || 'DD')))
--改为: AND to_char(xf_txdate,'yyyy-mm') >=p_month --p_month处输入日期格式为:yyyy-mm
AND to_char(xf_txdate-1,'yyyy-mm') <p_month
AND xf_txdate >=
TO_DATE(p_month || '01', 'yyyymm' || 'DD')
AND 1 > xf_txdate -
LAST_DAY(TO_DATE(p_month || '01',
'yyyymm' || 'DD')))
--直接改为:
AND to_char(xf_txdate,'yyyy-mm') =p_month
--好象用不到: AND to_char(xf_txdate,'yyyy-mm') >=p_month --p_month处输入:yyyy-mm
AND to_char(add_months(xf_txdate,-1),'yyyy-mm') <p_month