📄 pack_ccdata_report01.pck
字号:
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
END IF;
v_stepno:=4;
--将每组主键，各个指标合并成一条插入数据表中
INSERT INTO CCENT.DM_DAT_REPORT01(DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15)
SELECT DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
NVL(SUM(VALUE01),0),NVL(SUM(VALUE02),0),NVL(SUM(VALUE03),0),
NVL(SUM(VALUE04),0),NVL(SUM(VALUE05),0),NVL(SUM(VALUE06),0),
NVL(SUM(VALUE07),0),NVL(SUM(VALUE08),0),NVL(SUM(VALUE09),0),
NVL(SUM(VALUE10),0),NVL(SUM(VALUE11),0),NVL(SUM(VALUE12),0),
NVL(SUM(VALUE13),0),NVL(SUM(VALUE14),0),NVL(SUM(VALUE15),0)
FROM DM_TMP_REPORT01
WHERE DATADATE=P_I_DATE
AND REPORTID = P_I_REPORTID
AND ZONENO = P_I_ZONENO
AND BANKFLAG = P_I_BANKFLAG
AND EXHIFLAG = P_I_EXHIFLAG
GROUP BY DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG;
--根据不同的传入参数插入本月无交易的地区记录
--判断传入是卡中心，需要二级展现
v_stepno:=2001;
IF P_I_ZONENO = '0100' AND P_I_BANKFLAG = '5' AND P_I_EXHIFLAG = '1'
THEN
INSERT INTO CCENT.DM_DAT_REPORT01(DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15)
SELECT P_I_DATE,P_I_REPORTID,B.ZONENO,'',
B.BANKFLAG,P_I_ZONENO,P_I_BANKFLAG,P_I_EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM PRM_CARD_ORGAN B
WHERE BANKFLAG = '3'
AND START_DATE <=v_lastday
AND END_DATE>v_lastday
MINUS
SELECT DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM DM_DAT_REPORT01 A
WHERE A.DATADATE=P_I_DATE
AND A.REPORTID = P_I_REPORTID
AND A.ZONENO = P_I_ZONENO
AND A.BANKFLAG = P_I_BANKFLAG
AND A.EXHIFLAG = P_I_EXHIFLAG;
v_stepno:=2002;
--判断传入是发卡机构
ELSIF P_I_BANKFLAG = '3'
THEN
INSERT INTO CCENT.DM_DAT_REPORT01(DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15)
SELECT P_I_DATE,P_I_REPORTID,B.ZONENO,'',
B.BANKFLAG,P_I_ZONENO,P_I_BANKFLAG,P_I_EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM PRM_CARD_ORGAN B
WHERE ZONENO = P_I_ZONENO
AND BANKFLAG = '3'
AND START_DATE <=v_lastday
AND END_DATE>v_lastday
MINUS
SELECT DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM DM_DAT_REPORT01 A
WHERE A.DATADATE=P_I_DATE
AND A.REPORTID = P_I_REPORTID
AND A.ZONENO = P_I_ZONENO
AND A.BANKFLAG = P_I_BANKFLAG
AND A.EXHIFLAG = P_I_EXHIFLAG;
v_stepno:=2003;
--其他均是取下属机构
ELSE
INSERT INTO CCENT.DM_DAT_REPORT01(DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15)
SELECT P_I_DATE,P_I_REPORTID,B.ZONENO,'',
B.BANKFLAG,P_I_ZONENO,P_I_BANKFLAG,P_I_EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM PRM_CARD_ORGAN B
WHERE BELONG_ZONENO = P_I_ZONENO
AND BELONG_BANKFLAG = P_I_BANKFLAG
AND START_DATE <=v_lastday
AND END_DATE>v_lastday
MINUS
SELECT DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM DM_DAT_REPORT01 A
WHERE A.DATADATE=P_I_DATE
AND A.REPORTID = P_I_REPORTID
AND A.ZONENO = P_I_ZONENO
AND A.BANKFLAG = P_I_BANKFLAG
AND A.EXHIFLAG = P_I_EXHIFLAG;
END IF;
v_stepno:=5;
--将所有的记录合计成一条
INSERT INTO CCENT.DM_DAT_REPORT01(DATADATE,REPORTID,EXHIZONENO,EXHIAREANAME,
EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15)
SELECT P_I_DATE,P_I_REPORTID,'9999',' 合计',
'9',P_I_ZONENO,P_I_BANKFLAG,P_I_EXHIFLAG,
NVL(SUM(VALUE01),0),NVL(SUM(VALUE02),0),NVL(SUM(VALUE03),0),
NVL(SUM(VALUE04),0),NVL(SUM(VALUE05),0),NVL(SUM(VALUE06),0),
NVL(SUM(VALUE07),0),NVL(SUM(VALUE08),0),NVL(SUM(VALUE09),0),
NVL(SUM(VALUE10),0),NVL(SUM(VALUE11),0),NVL(SUM(VALUE12),0),
NVL(SUM(VALUE13),0),NVL(SUM(VALUE14),0),NVL(SUM(VALUE15),0)
FROM DM_DAT_REPORT01
WHERE DATADATE=P_I_DATE
AND REPORTID = P_I_REPORTID
AND ZONENO = P_I_ZONENO
AND BANKFLAG = P_I_BANKFLAG
AND EXHIFLAG = P_I_EXHIFLAG;
v_stepno:=6;
--更新地区名
UPDATE CCENT.DM_DAT_REPORT01 A
SET EXHIAREANAME = (SELECT AREANAME FROM PRM_CARD_ORGAN
WHERE ZONENO = A.EXHIZONENO
AND BANKFLAG = A.EXHIBANKFLAG
AND START_DATE <= v_lastday
AND END_DATE >v_lastday)
WHERE EXHIAREANAME IS NULL;
v_stepno:=10;
--数据处理成功
v_succeed:='0';
COMMIT;
P_O_SUCCEED := '0';
--记载数据处理日志
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') INTO v_end_time FROM DUAL;
v_deal_flag:=v_succeed;
/* 处理成功后记存储过程调度日志 */
cs2002.PACK_LOG.PR_WRITEPROCLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_process_level,
'',
v_begintime,
v_deal_flag
);
EXCEPTION
WHEN OTHERS THEN
v_succeed:='1';
ROLLBACK;
P_O_SUCCEED := '1';
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') INTO v_end_time FROM DUAL;
v_deal_flag:=v_succeed;
v_proc_err_code:=SQLCODE;
v_proc_err_txt:=SUBSTR(SQLERRM,1,200);
/* 处理失败后记存储过程错误日志和存储过程调度日志 */
cs2002.PACK_LOG.PR_WRITEERRORLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_process_level,
v_sqltxt,
v_proc_err_code,
v_proc_err_txt
);
cs2002.PACK_LOG.PR_WRITEPROCLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_process_level,
'',
v_begintime,
v_deal_flag
);
RAISE;
END PROC_CCDATA_REPORT01;
END PACK_CCDATA_REPORT01;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -