CREATE OR REPLACE PROCEDURE CCOD.license_detail (monthstr IN STRING,
day1 IN STRING,
day2 IN STRING)
AUTHID CURRENT_USER
AS
/*********************************
名称:license_detail
功能描述:没有表就创建有就直接插入数据
**********************************/
v_tablename VARCHAR2 (30); --表名
v_flag NUMBER (10, 0);
v_sqlfalg VARCHAR (200);
strSql VARCHAR2 (32766) := '';
BEGIN
strSql :=
'insert into a_' || monthstr
|| '
select a.agent_id as CTI工号 ,MAX(r.agent_name) as 坐席名称 ,MAX(r.agent_dn) as 分机号 ,Max(u.skill_group_desc) as 技能组,to_char(start_time, ''yyyymmdd'') as 日期 ,
MAX(a.company) as 业务线 ,MAX(a.product) as 产品 ,MAX(a.business) as 职场 from (
select * from "PA1".r_ags_e_h_'
|| monthstr
|| ' WHERE START_time >= TO_DATE ('''
|| day1
|| ''', ''yyyymmdd'') AND START_time < TO_DATE ('''
|| day2
|| ''', ''yyyymmdd'')
) r, CCOD.UCD_ENTERPRISE_SERVICE_DESC u, CCOD.UCD_ENTERPRISE_AGENT_INFO a
WHERE r.SKILL_ID = u.skill_group_id AND a.AGENT_ID=r.AGENT_ID AND u.skill_group_desc LIKE ''产%'' group by a.agent_id, to_char(start_time, ''yyyymmdd'')';
v_flag := 0;
v_tablename := CONCAT ('A_', UPPER (monthstr));
v_sqlfalg :=
'select count(*) from user_TABLES where table_name='''
|| v_tablename
|| '''';
DBMS_OUTPUT.put_line (v_sqlfalg);
EXECUTE IMMEDIATE v_sqlfalg INTO v_flag;
IF v_flag = 0
THEN --如果没有这个表 则去创建
BEGIN
EXECUTE IMMEDIATE 'create table ' || v_tablename
|| '
(
"CTI工号" VARCHAR2(255 BYTE) NOT NULL,
"坐席名称" VARCHAR2(255 BYTE),
"分机号" VARCHAR2(255 BYTE) NOT NULL,
"技能组" VARCHAR2(255 BYTE),
"日期" NUMBER,
"业务线" VARCHAR2(255 BYTE),
"产品" VARCHAR2(255 BYTE),
"职场" VARCHAR2(255 BYTE)
)';
-- execute immediate sqlstr;
END;
END IF;
EXECUTE IMMEDIATE strSql;
END;
/