oracle统计money存储过程

taowayi

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