oracle统计money存储过程

 

-- oracle统计money存储过程

 

 

CREATE OR REPLACE PROCEDURE P_TJ_MONEY (YYYYMM IN VARCHAR2,COUNTS IN NUMBER,OUT_FLAG OUT VARCHAR2) IS

  I NUMBER;

  V_BEGIN NUMBER;  

  V_COUNTS NUMBER;

  V_DAYS NUMBER;  

  V_YYYYMM VARCHAR2(60); 

  V_YYYYMMDD VARCHAR2(60);

  V_FLAGS VARCHAR(30);

  V_SQL VARCHAR2(3000);

BEGIN

  --- 初始化年月

  V_YYYYMM := YYYYMM;

  IF COUNTS <= 0 THEN

     V_COUNTS := 1;

    ELSE

     V_COUNTS := COUNTS;

  END IF;

  WHILE V_COUNTS>=1 LOOP           

  --------------------------------------------------------------------------------------------------------------   

      --- 计算当月多少天

      V_SQL := 'SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('''||V_YYYYMM||''',''YYYYMM'')),''DD'')) TS FROM DUAL';

      EXECUTE IMMEDIATE V_SQL INTO V_DAYS;

      /* 

       * 当天外呼超过 10分钟的坐席量

       * 插入统计表 TJ_MONEY

       */

      V_BEGIN := 1;

      /*退出循环*/

      <<LABEL_OUT>>  

      FOR I IN V_BEGIN .. V_DAYS LOOP        

        IF I <= 9 THEN

             V_YYYYMMDD := V_YYYYMM||'0'||I;

           ELSE

             V_YYYYMMDD := V_YYYYMM||I;

        END IF;

        --- 计算当天是否存在坐席

        V_SQL := 'SELECT COUNT(1) AGENTNUM FROM (

                         SELECT SUM(DURATION) CALLTIME

                                FROM CTI_CALLSTAT_'||V_YYYYMM||'

                                WHERE SUBSTR(ENDTIME,0,8) = '''||V_YYYYMMDD||'''

                                GROUP BY ORIGCALLING

                  ) WHERE CALLTIME>=''600'' ';

        EXECUTE IMMEDIATE V_SQL INTO V_FLAGS;

        COMMIT;

        --- 统计循环

        V_BEGIN := V_BEGIN + 1 ;

        --- 跳出 FOR 循环

        IF V_FLAGS<=0 THEN                    

          GOTO LABEL_OUT;

        END IF;

        --- 删除已经统计数据

        V_SQL := 'DELETE FROM TJ_MONEY WHERE CTIME='''||V_YYYYMMDD||''' AND FLAG=''0'' ';

        EXECUTE IMMEDIATE V_SQL;

        COMMIT;

        V_SQL := 'INSERT INTO TJ_MONEY SELECT COUNT(1) AGENTNUM,'''||V_YYYYMMDD

                 ||''' CTIME,''0'' FLAG FROM (

                         SELECT SUM(DURATION) CALLTIME

                                FROM CTI_CALLSTAT_'||V_YYYYMM||'

                                WHERE SUBSTR(ENDTIME,0,8) = '''||V_YYYYMMDD||'''

                                GROUP BY ORIGCALLING

                  ) WHERE CALLTIME>=''600'' ';

        EXECUTE IMMEDIATE V_SQL;

        COMMIT; 

      END LOOP;    

  --------------------------------------------------------------------------------------------------------------    

      --- 删除已经统计数据

      V_SQL := 'DELETE FROM TJ_MONEY WHERE CTIME='''||V_YYYYMM||''' AND FLAG=''1'' ';

      EXECUTE IMMEDIATE V_SQL;

      COMMIT;

      --- 统计当月的平均量

      V_SQL :=' INSERT INTO TJ_MONEY

                SELECT ROUND(((SELECT ROUND(SUM(AGENTNUM)/A,0) SL_A FROM ( SELECT * FROM TJ_MONEY WHERE FLAG = ''0'' AND CTIME LIKE '''||V_YYYYMM||'__'' ORDER BY AGENTNUM DESC) WHERE ROWNUM <= A) +

                      (SELECT ROUND(SUM(AGENTNUM)/B,0) SL_B FROM ( SELECT * FROM TJ_MONEY WHERE FLAG = ''0'' AND CTIME LIKE '''||V_YYYYMM||'__'' ORDER BY AGENTNUM DESC) WHERE ROWNUM <= B) +

                      (SELECT ROUND(SUM(AGENTNUM)/C,0) SL_C FROM ( SELECT * FROM TJ_MONEY WHERE FLAG = ''0'' AND CTIME LIKE '''||V_YYYYMM||'__'' ORDER BY AGENTNUM DESC) WHERE ROWNUM <= C) +

                      (SELECT ROUND(SUM(AGENTNUM)/D,0) SL_D FROM ( SELECT * FROM TJ_MONEY WHERE FLAG = ''0'' AND CTIME LIKE '''||V_YYYYMM||'__'' ORDER BY AGENTNUM DESC) WHERE ROWNUM <= D) )/4,0) AGENTNUM,

                      '''||V_YYYYMM||''' CTIME,

                      ''1'' FLAG

                FROM (SELECT ROUND(TS * 0.7,0) A, ROUND(TS * 0.75) B, ROUND(TS * 0.8) C, ROUND(TS * 0.85) D

                FROM (SELECT COUNT(1) TS FROM TJ_MONEY WHERE FLAG=''0'' AND CTIME LIKE '''||V_YYYYMM||'__'')) ';

      EXECUTE IMMEDIATE V_SQL;

      COMMIT;     

      --- 标记循环

      V_COUNTS := V_COUNTS - 1;

      IF V_COUNTS>=1 THEN 

         --- 获取下一个月

         V_SQL := 'SELECT TO_CHAR(ADD_MONTHS(TO_DATE('''||V_YYYYMM||''',''YYYYMM''),1),''YYYYMM'') YM FROM DUAL';

         EXECUTE IMMEDIATE V_SQL INTO V_YYYYMM;

      END IF;

  END LOOP;

  OUT_FLAG := '成功..！';

  --- 异常

  EXCEPTION

   when others then

         rollback; -- 回滚

             DBMS_OUTPUT.PUT_LINE(SQLERRM);

             OUT_FLAG := '失败..！';

         return;

END P_TJ_MONEY;

 

---------------------------------------------------------------------------------------------------------------------------------

 

 

-- Create table

 

create table TJ_MONEY

(

  AGENTNUM NUMBER,

  CTIME    VARCHAR2(30),

  FLAG     VARCHAR2(2)

)

 

 

评论

1 楼 fengweiyou 2011-07-26  

写的很好