我的一个PLSQL【我】 循环嵌套、游标使用、变量定义、查询插入表、批量提交事务、字符串截取、动态sql拼接执行
代码块：
--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) := '2019012'; --当前账期月份数字 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;
