创建日志表sql:
-- Create table
create table PROCEDURE_LOG
(
ID VARCHAR2(100) not null,
NAME VARCHAR2(1000),
CODE NUMBER,
MSG VARCHAR2(3000),
IN_DATE DATE
)
tablespace CUSTINCOME
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the table
comment on table PROCEDURE_LOG
is '存储过程日志表（测试用）';
-- Add comments to the columns
comment on column PROCEDURE_LOG.ID
is '主键';
comment on column PROCEDURE_LOG.NAME
is '存储过程名称PROCEDURE_NAME';
comment on column PROCEDURE_LOG.CODE
is '单次执行编码ONCE_CODE';
comment on column PROCEDURE_LOG.MSG
is '存储的信息';
comment on column PROCEDURE_LOG.IN_DATE
is '插入时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table PROCEDURE_LOG
add constraint PRI_KEY_PROCEDURE_LOG primary key (ID)
using index
tablespace CUSTINCOME
pctfree 10
initrans 2
maxtrans 255;
CREATE OR REPLACE PROCEDURE PRO_REVENUE_STATISTICS IS
--DECLARE
--日志单次code
log_code number;
--存储过程名称
log_name varchar2(100) := 'PRO_REVENUE_STATISTICS';
--日志信息
log_msg varchar2(3000) := ''; --插入数据计数器
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) ;
--当前全部收入查询条件，动态sql
l_month_select varchar2(10000) := '' ;
BEGIN --给日志单次code赋值
select SEQ_REVENUE_STATISTICS_RES.NEXTVAL into log_code from dual; --计算全部客户数
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';
--执行动态sql查询字符串
EXECUTE IMMEDIATE l_month_select into 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 /*+ PARALLEL(12) */
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;
--关闭单条游标
CLOSE revenues_cur; --插入结果表
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
);
--COMMIT;
--计数器变量自增
ins_counter := ins_counter+1;
--判断数量，如果数量到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;
--DBMS_OUTPUT.PUT_LINE('--开始提交事务:'||ins_counter);
insert into PROCEDURE_LOG values(SEQ_REVENUE_STATISTICS_RES.NEXTVAL,log_name,log_code,'开始提交事务:'||ins_counter,sysdate);
--提交事务,插入数据和日志
COMMIT;
--DBMS_OUTPUT.PUT_LINE('--结束提交事务:'||ins_counter);
insert into PROCEDURE_LOG values(SEQ_REVENUE_STATISTICS_RES.NEXTVAL,log_name,log_code,'结束提交事务:'||ins_counter,sysdate);
--提交事务立即执行插入日志
COMMIT;
END LOOP prov_loop;
--DBMS_OUTPUT.PUT_LINE('全部结束：'||ins_counter);
insert into PROCEDURE_LOG values(SEQ_REVENUE_STATISTICS_RES.NEXTVAL,log_name,log_code,'全部结束:'||ins_counter,sysdate);
--提交事务立即执行插入日志
COMMIT; END PRO_REVENUE_STATISTICS;