?? pack_ccdata_report05.pck

?? 此代碼為利用PL/SQL開發(fā)數(shù)據(jù)庫存儲(chǔ)過程的典型示例
?? PCK
?? 第 1 頁 / 共 2 頁
字號(hào):
12 下一頁
CREATE OR REPLACE PACKAGE PACK_CCDATA_REPORT05 AS

        PROCEDURE PROC_CCDATA_REPORT05
        (P_I_DATE     	IN		VARCHAR2,		--傳入?yún)?shù) 日期8位，月報(bào)6位，年報(bào)4位
	     P_I_REPORTID	IN		VARCHAR2,		--傳入?yún)?shù),報(bào)表號(hào)
	     P_I_ZONENO  	IN		VARCHAR2,		--傳入?yún)?shù)，地區(qū)號(hào)
	     P_I_BANKFLAG	IN		VARCHAR2,		--傳入?yún)?shù)，行級別
	     P_I_CURR    	IN		VARCHAR2,		--傳入?yún)?shù)，幣種
	     P_I_UNIT	    IN		VARCHAR2,		--傳入?yún)?shù)，金額單位
         P_I_EXHIFLAG	IN		VARCHAR2		--傳入?yún)?shù)，展現(xiàn)層次
 	);


END PACK_CCDATA_REPORT05;
/
CREATE OR REPLACE PACKAGE BODY PACK_CCDATA_REPORT05 AS

PROCEDURE PROC_CCDATA_REPORT05
    (	 P_I_DATE     	IN		VARCHAR2,		--傳入?yún)?shù) 日期8位，月報(bào)6位，年報(bào)4位
	     P_I_REPORTID	IN		VARCHAR2,		--傳入?yún)?shù),報(bào)表號(hào)
	     P_I_ZONENO  	IN		VARCHAR2,		--傳入?yún)?shù)，地區(qū)號(hào)
	     P_I_BANKFLAG	IN		VARCHAR2,		--傳入?yún)?shù)，行級別
	     P_I_CURR    	IN		VARCHAR2,		--傳入?yún)?shù)，幣種
	     P_I_UNIT	    IN		VARCHAR2,		--傳入?yún)?shù)，金額單位
         P_I_EXHIFLAG	IN		VARCHAR2		--傳入?yún)?shù)，展現(xiàn)層次

    )
IS
     --日志變量
     v_thisprocid     	  CS2002.LOG_PROC_EXEC.PROC_ID%TYPE :='CCENT.20000500';
     v_thisprocname   	  CS2002.LOG_PROC_EXEC.PROC_NAME%TYPE :=
                                   'PACK_CCDATA_REPORT05.PROC_CCDATA_REPORT05';
     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); --判斷成功標(biāo)志 ‘0’成功，‘1’失敗


     v_zone_cond	      VARCHAR2(100);--選擇地區(qū)的條件

     v_zoneno		      VARCHAR2(5);

     v_lastday		      VARCHAR2(8);
     v_firstmon           VARCHAR2(8);

     --用于動(dòng)態(tài)調(diào)用的變量
     v_dynamic_cursor 	  NUMBER;
     v_result         	  NUMBER;
     v_sqltxt         	  VARCHAR2(4000);

     --游標(biāo)變量
     v_condition	      CCENT.DIC_ID_CONDITION.CONDITION%TYPE;--取數(shù)條件
     v_flag		          CCENT.DIC_ID_CONDITION.FLAG%TYPE;
     v_target		      CCENT.DIC_ID_CONDITION.TARGET%TYPE;

     --取數(shù)條件游標(biāo)
     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
     --記載數(shù)據(jù)處理日志


     --讀取系統(tǒng)時(shí)間記載開始處理時(shí)間
     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';

     --刪除臨時(shí)表中的記錄
     v_stepno:=1;

     DELETE FROM DM_DAT_REPORT05
     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_REPORT05
     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');由于是全局臨時(shí)表

     --判斷傳入是卡中心，需要二級展現(xiàn)
     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中取出相應(yīng)條件
             --循環(huán)從DW卡片統(tǒng)計(jì)表中取數(shù)


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

        --計(jì)算非累加指標(biāo)
        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_REPORT05(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,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;

	   	--計(jì)算累加指標(biāo)
	   	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_REPORT05(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,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;

     --判斷傳入是發(fā)卡機(jī)構(gòu)
     ELSIF P_I_BANKFLAG = '3'
     	THEN v_zoneno := P_I_ZONENO;
     	     v_zone_cond := 'BANK_FLAG = ''3'' AND ZONENO = '''||v_zoneno;

     	     --從字典表DIC_ID_CONDITION中取出相應(yīng)條件
             --循環(huán)從DW卡片統(tǒng)計(jì)表中取數(shù)


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

        --計(jì)算非累計(jì)指標(biāo)
        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_REPORT05(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,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;

	   	--計(jì)算累計(jì)指標(biāo)
	   	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_REPORT05(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,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;

     --其他均是取下屬機(jī)構(gòu)
     ELSE
     	--v_zone_cond:='EXSISTS (SELECT 1 FROM PRM_CARD_ORGAN WHERE ZONENO =DW_DAT_BFHCKPTJ.ZONENO AND BANKFLAG

     	     --從字典表DIC_ID_CONDITION中取出相應(yīng)條件
             --循環(huán)從DW卡片統(tǒng)計(jì)表中取數(shù)


        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_REPORT05(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,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;
12 下一頁
?? 文件大小 4 K
?? 上傳用戶 haibokaishi2006
?? 所屬分類 Oracle數(shù)據(jù)庫
??? 相關(guān)標(biāo)簽

#SQL #PL #代碼 #數(shù)據(jù)庫
?? 快捷鍵說明

復(fù)制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號(hào) Ctrl + =
減小字號(hào) Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? pack_ccdata_report05.pck

?? 快捷鍵說明
