pack_ccdata_report03.pck

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

CREATE OR REPLACE PACKAGE PACK_CCDATA_REPORT03 AS

        PROCEDURE PROC_CCDATA_REPORT03
        (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_CURR    	IN		VARCHAR2,		--传入参数，币种
	     P_I_UNIT	    IN		VARCHAR2,		--传入参数，金额单位
       P_I_EXHIFLAG	IN		VARCHAR2,		--传入参数，展现层次
       P_O_SUCCEED  OUT   VARCHAR2    --传出参数，成功标志 0为成功，1为失败
 	);


END PACK_CCDATA_REPORT03;
/
CREATE OR REPLACE PACKAGE BODY PACK_CCDATA_REPORT03 AS

PROCEDURE PROC_CCDATA_REPORT03
    (	 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_CURR    	IN		VARCHAR2,		--传入参数，币种
	     P_I_UNIT	    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.20000300';
     v_thisprocname   	  CS2002.LOG_PROC_EXEC.PROC_NAME%TYPE:=
                                      'PACK_CCDATA_REPORT03.PROC_CCDATA_REPORT03';
     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_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_firstmon           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_cursor1 IS
     SELECT CONDITION,FLAG,TARGET
     FROM DIC_ID_CONDITION
     WHERE REPORTID = P_I_REPORTID
     AND SUMTAG = '0';

     CURSOR cur_condition_cursor2 IS
     SELECT CONDITION,FLAG,TARGET
     FROM DIC_ID_CONDITION
     WHERE REPORTID = P_I_REPORTID
     AND SUMTAG = '1';

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_firstmon:=SUBSTR(P_I_DATE,1,4)||'01';

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

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

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

     --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_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||',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:=401;

	   	  --计算累加指标
	   	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;

     --判断传入是发卡机构
     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_cursor1;
		   LOOP
			 FETCH cur_condition_cursor1 INTO v_condition,v_flag,v_target;
			 EXIT WHEN cur_condition_cursor1%NOTFOUND;