代码块：
--CREATE OR REPLACE PROCEDURE PRO_REVENUE_STATISTICS --IS
DECLARE
--计数器
ins_counter PLS_INTEGER := 0;
--全部客户数量
l_all_cust_num REVENUE_STATISTICS_RES.All_Cust_Num%TYPE;
--全部收入
l_all_income REVENUE_STATISTICS_RES.All_Income%TYPE;
--当前账期月份字符串默认12个月
l_month_str varchar2(200) := '';
--当前账期月份数字
l_month_num number(20) ;
--当前全部收入查询条件
l_month_select varchar2(10000) := '' ;
BEGIN
--计算全部客户数
SELECT sum(CUST_TOTAL) count into l_all_cust_num from provi_cust_total;
--计算截止到当前账期的总收入
--计算当前最大月份201909
SELECT MAX(ACCOUNT_DAY) into l_month_str FROM ct_lan_node_inc;
l_month_num := TO_NUMBER(SUBSTR(l_month_str,5));
DBMS_OUTPUT.PUT_LINE('l_month_num:'||l_month_num); FOR moth_index IN 1 .. (l_month_num)
LOOP
l_month_select := l_month_select || 'SUM(TY_' || moth_index || ')+';
END LOOP;
--去掉最后一个加号
l_month_select := substr(l_month_select,0,length(l_month_select)-1);
--拼接查询
l_month_select := 'SELECT /*+ PARALLEL(12) */(' || l_month_select ||') FROM EDA_CUST_INC';
--DBMS_OUTPUT.PUT_LINE(l_month_select);
--执行查询字符串
EXECUTE IMMEDIATE l_month_select into l_all_income;
--DBMS_OUTPUT.PUT_LINE(l_all_income); --获取省份游标
FOR prov_rec IN (SELECT REGION_ID,CUST_TOTAL,REGION_NBR,REGION_NAME FROM PROVI_CUST_TOTAL p LEFT JOIN common_region c ON p.region_id = c.common_region_id)
LOOP
<<prov_loop>>
--获取市游标
FOR city_rec IN (SELECT COMMON_REGION_ID,REGION_NBR, REGION_NAME FROM COMMON_REGION WHERE PAR_REGION_ID = prov_rec.REGION_ID)
LOOP
--获取产品游标
FOR product_rec IN (select NAME,CODE FROM PRODUCT_CAT)
LOOP
--获取行业游标
FOR industry_rec IN ( select INDUSTRY_TYPE_ID,
PAR_INDUSTRY_TYPE_ID,
INDUSTRY_TYPE_GRADE,
INDUSTRY_TYPE_CODE,
INDUSTRY_TYPE_NAME
from industry_type)
LOOP
--获取身份证信息
FOR identity_index IN 1..2
LOOP
DECLARE
--定义单条查询结果游标
CURSOR revenues_cur IS
SELECT
COUNT(e.CUST_ID) AUDIT_CUST_NUM,COUNT(DISTINCT(e.PARTY_ID)) AUDIT_CUST_PARTY_NUM,
SUM(TY_1) TY_1 ,SUM(TY_2) TY_2,SUM(TY_3) TY_3,SUM(TY_4) TY_4,SUM(TY_5) TY_5,SUM(TY_6) TY_6,SUM(TY_7) TY_7,SUM(TY_8) TY_8,SUM(TY_9) TY_9,SUM(TY_10) TY_10,SUM(TY_11) TY_11,SUM(TY_12) TY_12,
SUM(LY_1) LY_1 ,SUM(LY_2) LY_2,SUM(LY_3) LY_3,SUM(LY_4) LY_4,SUM(LY_5) LY_5,SUM(LY_6) LY_6,SUM(LY_7) LY_7,SUM(LY_8) LY_8,SUM(LY_9) LY_9,SUM(LY_10) LY_10,SUM(LY_11) LY_11,SUM(LY_12) LY_12
FROM EDA_CUST_INC e
WHERE 1=1
AND e.STD_PRVNCE_CD = prov_rec.REGION_NBR
AND e.STD_LATN_CD = city_rec.REGION_NBR
AND e.PROD_TYPE = product_rec.CODE
AND EXISTS (SELECT 1
FROM CUST_CP_MERGE M
WHERE e.CUST_ID = M.CUST_ID
AND e.STD_LATN_CD = M.STD_LATN_CD)
AND e.PARTY_ID IS NOT NULL AND UPPER(e.PARTY_ID) != 'NULL'
AND EXISTS (SELECT 1
FROM PARTY P
WHERE e.PARTY_ID = p.PARTY_ID
AND p.IDENTITY_TYPE = identity_index)
and exists (select 1
from party_org po
where e.party_id = po.party_id
and po.INDUSTRY_TYPE_ID = industry_rec.INDUSTRY_TYPE_ID);
--定义单条查询结果变量
revenue_rec revenues_cur%ROWTYPE;
BEGIN
--打开游标
OPEN revenues_cur;
--给变量赋值
FETCH revenues_cur INTO revenue_rec;
--DBMS_OUTPUT.PUT_LINE(prov_rec.REGION_NAME||'-'||city_rec.REGION_NAME||'-'||product_rec.NAME||'AUDIT_CUST_NUM'||revenue_rec.AUDIT_CUST_NUM||'AUDIT_CUST_PARTY_NUM'||revenue_rec.AUDIT_CUST_PARTY_NUM);
--插入结果表
INSERT INTO REVENUE_STATISTICS_RES2
(
ID,
PROVINCE_REGION_ID,
CITY_REGION_ID,
REGION_NAME,
PROD_TYPE,
INDUSTRY_TYPE_ID,
INDUSTRY_TYPE_CODE,
INDUSTRY_TYPE_NAME,
PAR_INDUSTRY_TYPE_ID,
INDUSTRY_TYPE_GRADE,
IDENTITY_TYPE,
IN_DATE,
AUDIT_CUST_NUM,
AUDIT_CUST_PARTY_NUM,
ALL_CUST_NUM,
ALL_INCOME,
TY_1,
TY_2,
TY_3,
TY_4,
TY_5,
TY_6,
TY_7,
TY_8,
TY_9,
TY_10,
TY_11,
TY_12,
LY_1,
LY_2,
LY_3,
LY_4,
LY_5,
LY_6,
LY_7,
LY_8,
LY_9,
LY_10,
LY_11,
LY_12
)
VALUES(
SEQ_REVENUE_STATISTICS_RES.NEXTVAL,
prov_rec.REGION_ID,
city_rec.COMMON_REGION_ID,
city_rec.region_name,
product_rec.code,
industry_rec.INDUSTRY_TYPE_ID,
industry_rec.INDUSTRY_TYPE_CODE,
industry_rec.INDUSTRY_TYPE_NAME,
industry_rec.PAR_INDUSTRY_TYPE_ID,
industry_rec.INDUSTRY_TYPE_GRADE,
identity_index,
sysdate,
revenue_rec.AUDIT_CUST_NUM,
revenue_rec.AUDIT_CUST_PARTY_NUM,
l_all_cust_num,
l_all_income,
revenue_rec.TY_1,
revenue_rec.TY_2,
revenue_rec.TY_3,
revenue_rec.TY_4,
revenue_rec.TY_5,
revenue_rec.TY_6,
revenue_rec.TY_7,
revenue_rec.TY_8,
revenue_rec.TY_9,
revenue_rec.TY_10,
revenue_rec.TY_11,
revenue_rec.TY_12,
revenue_rec.LY_1,
revenue_rec.LY_2,
revenue_rec.LY_3,
revenue_rec.LY_4,
revenue_rec.LY_5,
revenue_rec.LY_6,
revenue_rec.LY_7,
revenue_rec.LY_8,
revenue_rec.LY_9,
revenue_rec.LY_10,
revenue_rec.LY_11,
revenue_rec.LY_12
);
--关闭单条游标
CLOSE revenues_cur;
ins_counter := ins_counter+1;
--DBMS_OUTPUT.PUT_LINE(ins_counter);
--判断数量，如果数量到5000条提交一下事务
IF MOD(ins_counter,5000)=0
THEN
COMMIT;
END IF;
--结束内部块
END;
EXIT prov_loop;--退出到最外循环
END LOOP identity_loop;
END loop industry_loop;
END loop product_loop;
END LOOP city_loop;
END LOOP prov_loop;
DBMS_OUTPUT.PUT_LINE(ins_counter);
--提交最后的事务
COMMIT;
END;