pack_ccdata_report01.pck

此代码为利用PL/SQL开发数据库存储过程的典型示例

CREATE OR REPLACE PACKAGE PACK_CCDATA_REPORT01 AS

        PROCEDURE PROC_CCDATA_REPORT01
        (P_I_DATE        IN        VARCHAR2,        --传入参数 日期8位，月报6位，年报4位
         P_I_REPORTID    IN        VARCHAR2,        --传入参数,报表号
         P_I_ZONENO      IN        VARCHAR2,        --传入参数，地区号
         P_I_BANKFLAG    IN        VARCHAR2,        --传入参数，行级别
         P_I_EXHIFLAG    IN        VARCHAR2,        --传入参数，展现层次
         P_O_SUCCEED     OUT       VARCHAR2    --传出参数，成功标志 0为成功，1为失败

     );


END PACK_CCDATA_REPORT01;
/
CREATE OR REPLACE PACKAGE BODY PACK_CCDATA_REPORT01 AS

PROCEDURE PROC_CCDATA_REPORT01
    (     P_I_DATE        IN        VARCHAR2,        --传入参数 日期8位，月报6位，年报4位
          P_I_REPORTID    IN        VARCHAR2,        --传入参数,报表号
          P_I_ZONENO      IN        VARCHAR2,        --传入参数，地区号
          P_I_BANKFLAG    IN        VARCHAR2,        --传入参数，行级别
          P_I_EXHIFLAG    IN        VARCHAR2,         --传入参数，展现层次
          P_O_SUCCEED     OUT       VARCHAR2           --传出参数，成功标志 0为成功，1为失败
    )
IS
     --日志变量
     v_thisprocid           CS2002.LOG_PROC_EXEC.PROC_ID%TYPE :='CCENT.20000100';
     v_thisprocname         CS2002.LOG_PROC_EXEC.PROC_NAME%TYPE :='PACK_CCDATA_REPORT1.PROC_CCDATA_REPORT01';
     v_stepno               CS2002.LOG_PROC_EXEC.STEP_NUM%TYPE;
     v_begintime            CS2002.LOG_PROC_EXEC.BEGIN_TIME%TYPE;
     v_end_time             CS2002.LOG_PROC_EXEC.END_TIME%TYPE;
     v_deal_flag            CS2002.LOG_PROC_EXEC.DEAL_FLAG%TYPE;
     v_process_level        CS2002.LOG_PROC_ERR.LOG_LEVEL%TYPE :='3';
     v_proc_err_code        CS2002.LOG_PROC_ERR.PROC_ERR_CODE%TYPE;
     v_proc_err_txt         CS2002.LOG_PROC_ERR.PROC_ERR_TXT%TYPE;


     v_succeed              VARCHAR2(1); --判断成功标志 ‘0’成功，‘1’失败


     v_zone_cond            VARCHAR2(100);--选择地区的条件

     v_zoneno               VARCHAR2(5);

     v_lastday              VARCHAR2(8);  --取当月的最后一天


     --用于动态调用的变量
     v_dynamic_cursor       NUMBER;
     v_result               NUMBER;
     v_sqltxt               VARCHAR2(4000);

     --游标变量
     v_condition            CCENT.DIC_ID_CONDITION.CONDITION%TYPE;--取数条件
     v_flag                 CCENT.DIC_ID_CONDITION.FLAG%TYPE;     --指标标志位
     v_target               CCENT.DIC_ID_CONDITION.TARGET%TYPE;   --指标

     --取数条件游标
     CURSOR cur_condition_cursor IS
     SELECT CONDITION,FLAG,TARGET
     FROM DIC_ID_CONDITION
     WHERE REPORTID = P_I_REPORTID;

BEGIN
     --记载数据处理日志


     --读取系统时间记载开始处理时间
     SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') INTO v_begintime FROM DUAL;


     v_lastday:=TO_CHAR(LAST_DAY(TO_DATE(P_I_DATE,'YYYYMM')),'YYYYMMDD');

     --删除临时表中的记录
     v_stepno:=1;

     DELETE 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;

     DELETE 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;

     --EXECUTE IMMEDIATE ('TRUNCATE TABLE DM_TMP_REPORT01');由于是全局临时表

     --判断传入是卡中心，需要二级展现
     v_stepno:=2;
     IF P_I_ZONENO = '0100' AND P_I_BANKFLAG = '5' AND P_I_EXHIFLAG = '1'
         THEN   v_zone_cond := 'BANK_FLAG = ''3''';

              --从字典表DIC_ID_CONDITION中取出相应条件
              --循环从DW卡片统计表中取数


        v_stepno:=301;
            v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;

            OPEN cur_condition_cursor;
              LOOP
              FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
              EXIT WHEN cur_condition_cursor%NOTFOUND;

              v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT01(DATADATE,REPORTID,EXHIZONENO,';
              v_sqltxt := v_sqltxt||'EXHIAREANAME,EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,';
              v_sqltxt := v_sqltxt||'VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,VALUE06,';
              v_sqltxt := v_sqltxt||'VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,VALUE12,';
              v_sqltxt := v_sqltxt||'VALUE13,VALUE14,VALUE15,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_EXHIFLAG;
              v_sqltxt := v_sqltxt||','||v_target;
              v_sqltxt := v_sqltxt||','||v_flag;
              v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCKPTJ';
              v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
              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_cursor;

        DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
        v_sqltxt := NULL;

     --判断传入是发卡机构
     ELSIF P_I_BANKFLAG = '3'
         THEN v_zoneno := P_I_ZONENO;
              v_zone_cond := 'BANK_FLAG = ''3'' AND ZONENO = '''||v_zoneno;

              --从字典表DIC_ID_CONDITION中取出相应条件
             --循环从DW卡片统计表中取数


        v_stepno:=302;
            v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;

            OPEN cur_condition_cursor;
              LOOP
              FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
              EXIT WHEN cur_condition_cursor%NOTFOUND;

              v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT01(DATADATE,REPORTID,EXHIZONENO,';
              v_sqltxt := v_sqltxt||'EXHIAREANAME,EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,';
              v_sqltxt := v_sqltxt||'VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,VALUE06,';
              v_sqltxt := v_sqltxt||'VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,VALUE12,';
              v_sqltxt := v_sqltxt||'VALUE13,VALUE14,VALUE15,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_EXHIFLAG;
              v_sqltxt := v_sqltxt||','||v_target;
              v_sqltxt := v_sqltxt||','||v_flag;
              v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCKPTJ';
              v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
              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_cursor;

        DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
        v_sqltxt := NULL;

     --其他均是取下属机构
     ELSE
         --v_zone_cond:='EXSISTS (SELECT 1 FROM PRM_CARD_ORGAN WHERE ZONENO =DW_DAT_BFHCKPTJ.ZONENO AND BANKFLAG

              --从字典表DIC_ID_CONDITION中取出相应条件
             --循环从DW卡片统计表中取数


        v_stepno:=303;
            v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;

            OPEN cur_condition_cursor;
              LOOP
              FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
              EXIT WHEN cur_condition_cursor%NOTFOUND;

              v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT01(DATADATE,REPORTID,EXHIZONENO,';
              v_sqltxt := v_sqltxt||'EXHIAREANAME,EXHIBANKFLAG,ZONENO,BANKFLAG,EXHIFLAG,';
              v_sqltxt := v_sqltxt||'VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,VALUE06,';
              v_sqltxt := v_sqltxt||'VALUE07,VALUE08,VALUE09,VALUE10,VALUE11,VALUE12,';
              v_sqltxt := v_sqltxt||'VALUE13,VALUE14,VALUE15,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_EXHIFLAG;
              v_sqltxt := v_sqltxt||','||v_target;
              v_sqltxt := v_sqltxt||','||v_flag;
              v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCKPTJ A,CCENT.PRM_CARD_ORGAN B';
              v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
              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';