📄 pack_ccdata_report03.pck
字号:
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT03(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,';
v_sqltxt := v_sqltxt||'VALUE12,VALUE13,VALUE14,VALUE15,VALUE16,VALUE17,';
v_sqltxt := v_sqltxt||'VALUE18,VALUE19,VALUE20,VALUE21,VALUE22,VALUE23,';
v_sqltxt := v_sqltxt||'VALUE24,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',ZONENO,BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCJYTJ';
v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND '||v_zone_cond;
v_sqltxt := v_sqltxt||''' GROUP BY ZONENO,BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor1;
v_stepno:=402;
--计算累计指标
OPEN cur_condition_cursor2;
LOOP
FETCH cur_condition_cursor2 INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor2%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT03(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,';
v_sqltxt := v_sqltxt||'VALUE12,VALUE13,VALUE14,VALUE15,VALUE16,VALUE17,';
v_sqltxt := v_sqltxt||'VALUE18,VALUE19,VALUE20,VALUE21,VALUE22,VALUE23,';
v_sqltxt := v_sqltxt||'VALUE24,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',ZONENO,BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCJYTJ';
v_sqltxt := v_sqltxt||' WHERE DATATIME BETWEEN '''||v_firstmon;
v_sqltxt := v_sqltxt||''' AND '''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND '||v_zone_cond;
v_sqltxt := v_sqltxt||''' GROUP BY ZONENO,BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor2;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
--其他均是取下属机构
ELSE
--从字典表DIC_ID_CONDITION中取出相应条件
--循环从DW卡片统计表中取数
v_stepno:=303;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
OPEN cur_condition_cursor1;
LOOP
FETCH cur_condition_cursor1 INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor1%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT03(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,';
v_sqltxt := v_sqltxt||'VALUE12,VALUE13,VALUE14,VALUE15,VALUE16,VALUE17,';
v_sqltxt := v_sqltxt||'VALUE18,VALUE19,VALUE20,VALUE21,VALUE22,VALUE23,';
v_sqltxt := v_sqltxt||'VALUE24,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',A.ZONENO,A.BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCJYTJ A,CCENT.PRM_CARD_ORGAN B';
v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND A.ZONENO = B.ZONENO AND A.BANK_FLAG = B.BANKFLAG';
v_sqltxt := v_sqltxt||' AND B.BELONG_ZONENO = '''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''' AND B.BELONG_BANKFLAG = '''||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||''' AND B.START_DATE <= '''||v_lastday;
v_sqltxt := v_sqltxt||''' AND B.END_DATE > '''||v_lastday;
v_sqltxt := v_sqltxt||''' GROUP BY A.ZONENO,A.BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor1;
--计算累计指标
OPEN cur_condition_cursor2;
LOOP
FETCH cur_condition_cursor2 INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor2%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT03(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,';
v_sqltxt := v_sqltxt||'VALUE12,VALUE13,VALUE14,VALUE15,VALUE16,VALUE17,';
v_sqltxt := v_sqltxt||'VALUE18,VALUE19,VALUE20,VALUE21,VALUE22,VALUE23,';
v_sqltxt := v_sqltxt||'VALUE24,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',A.ZONENO,A.BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCJYTJ A,CCENT.PRM_CARD_ORGAN B';
v_sqltxt := v_sqltxt||' WHERE DATATIME BETWEEN '''||v_firstmon;
v_sqltxt := v_sqltxt||''' AND '''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND A.ZONENO = B.ZONENO AND A.BANK_FLAG = B.BANKFLAG';
v_sqltxt := v_sqltxt||' AND B.BELONG_ZONENO = '''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''' AND B.BELONG_BANKFLAG = '''||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||''' AND B.START_DATE <= '''||v_lastday;
v_sqltxt := v_sqltxt||''' AND B.END_DATE > '''||v_lastday;
v_sqltxt := v_sqltxt||''' GROUP BY A.ZONENO,A.BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor2;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
END IF;
v_stepno:=4;
--将每组主键，各个指标合并成一条插入数据表中
INSERT INTO CCENT.DM_DAT_REPORT03(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,VALUE17,VALUE18,VALUE19,VALUE20,
VALUE21,VALUE22,VALUE23,VALUE24)
SELECT DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
NVL(SUM(VALUE01),0),NVL(SUM(VALUE02)/P_I_UNIT,0),NVL(SUM(VALUE03),0),
NVL(SUM(VALUE04)/P_I_UNIT,0),NVL(SUM(VALUE05),0),NVL(SUM(VALUE06)/P_I_UNIT,0),
NVL(SUM(VALUE07),0),NVL(SUM(VALUE08)/P_I_UNIT,0),NVL(SUM(VALUE09),0),
NVL(SUM(VALUE10)/P_I_UNIT,0),NVL(SUM(VALUE11),0),NVL(SUM(VALUE12)/P_I_UNIT,0),
NVL(SUM(VALUE13),0),NVL(SUM(VALUE14)/P_I_UNIT,0),NVL(SUM(VALUE15),0),
NVL(SUM(VALUE16)/P_I_UNIT,0),NVL(SUM(VALUE17),0),NVL(SUM(VALUE18)/P_I_UNIT,0),
NVL(SUM(VALUE19),0),NVL(SUM(VALUE20)/P_I_UNIT,0),NVL(SUM(VALUE21),0),
NVL(SUM(VALUE22)/P_I_UNIT,0),NVL(SUM(VALUE23),0),NVL(SUM(VALUE24)/P_I_UNIT,0)
FROM DM_TMP_REPORT03
WHERE DATADATE = P_I_DATE
AND REPORTID = P_I_REPORTID
AND ZONENO = P_I_ZONENO
AND BANKFLAG = P_I_BANKFLAG
AND CURRTYPE = P_I_CURR
AND EXHIFLAG = P_I_EXHIFLAG
AND UNIT = P_I_UNIT
GROUP BY DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT;
--根据不同的传入参数插入本月无交易的地区记录
--判断传入是卡中心，需要二级展现
v_stepno:=2001;
IF P_I_ZONENO = '0100' AND P_I_BANKFLAG = '5' AND P_I_EXHIFLAG = '1'
THEN
INSERT INTO CCENT.DM_DAT_REPORT03(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,VALUE17,VALUE18,VALUE19,VALUE20,
VALUE21,VALUE22,VALUE23,VALUE24)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -