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