这篇文章主要介绍了呼叫中心数据入库脚本的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。CREATE OR REPLACE PROCEDURE "M_
这篇文章主要介绍了呼叫中心数据入库脚本的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
CREATE OR REPLACE PROCEDURE "M_XA_WH_DaiLY_COUNT" is
cursor cur_28424 is
select * from item_28424_sp@cm_xa
where conclusion in (61673,61680,61695) --61694 拒收
AND (qc_first !=0 or qc_first is null)
and done_flag<>7
and substr(donetime,1,10) <=to_char(sysdate-2,'yyyy-mm-dd')
and nvl(recycle_result,0)=0;
rec_28424 cur_28424%rowtype;
nMemberID number(10);
vName varchar2(30);
vAreaID number(10);
vAddress varchar2(200);
vState varchar2(10);
vStatDate varchar2(20);
nCount number(10);
vMobile varchar2(20);
vMemberName varchar2(255);
-- sID number(10);
v_name varchar2(255);
-- v_areacode varchar2(64);
-- nParentID number(10);
-- Cnt number(10);
v_passwd number(10);
v_passwdid number(10);
v_ccnum1 number(10);
v_ccnum2 number(10);
v_ccid number(10);
v_id number(10);
vregsource varchar2(50);
vcometime varchar2(20);
begin
--************************************************************************************************************************************
select COUNT(*) INTO v_ccnum1 from item_28424_sp@cm_xa
where (qc_first !=0 or qc_first is null)
and done_flag<>7
and substr(donetime,1,10) <=to_char(sysdate-2,'yyyy-mm-dd')
and nvl(recycle_result,0)=0;
open cur_28424;
loop
fetch cur_28424 into rec_28424;
exit when cur_28424%notfound;
vStatDate:=substr(rec_28424.DoneTime,1,10);
vState := '合格';
---名字规范
v_name:='X';
if jay_is_chinesename(regexp_replace(to_single_byte(replace(rec_28424.col_0,' ')),'.∗.∗')) =1 then
v_name:=regexp_replace(to_single_byte(trim(rec_28424.col_0)),'.∗.∗');
end if;
if jay_is_chinesename(regexp_replace(to_single_byte(replace(rec_28424.col_8,' ')),'.∗.∗')) =1 then
v_name:=regexp_replace(to_single_byte(trim(rec_28424.col_8)),'.∗.∗');
end if;
if v_name='X' then
vState := '姓名不规范'; --vState := '拒收';
end if;
---------------------------------------------------------------------------------------------------
if rec_28424.Col_12 is null then --地址更新为空
vState := '地址不规范';
end if;
if (rec_28424.Col_11 is null or lengthb(trim(rec_28424.Col_11))<>6 or substr(trim(rec_28424.Col_11),-4)='0000' or length(trim(rec_28424.Col_11))!=6) then --邮编更新为空
vState := '邮编不规范';
end if;
if rec_28424.Col_2 is null and rec_28424.Col_10 is null then
vState := '手机不规范';
end if;
if instr(rec_28424.Col_10,'*')>0 or (length(trim(rec_28424.Col_10))>0 and fun_IsNumber(rec_28424.col_10)!=1) or rec_28424.col_10 is null or length(trim(rec_28424.Col_10))<>11 then
if instr(rec_28424.Col_2,'*')>0 or (length(trim(rec_28424.Col_2))>0 and fun_IsNumber(rec_28424.col_2)!=1) or rec_28424.col_2 is null or length(trim(rec_28424.Col_2))<>11 then
vState := '手机不规范';
else
vmobile := rec_28424.Col_2;
end if;
--elsif instr(rec_28424.Col_2,'*')>0 or (length(trim(rec_28424.Col_2))>0 and fun_IsNumber(rec_28424.col_2)!=1) then
-- vState := '手机不规范';
else vmobile := rec_28424.Col_10;
end if;
-- if length(trim(rec_28424.Col_105)) >64 then
-- vState := 'email不规范';
-- end if;
vAddress := to_single_byte(trim(rec_28424.Col_12));
--如果地址止于数字，则补上“室”
if substr(vAddress,length(vAddress),1) in ('0','1','2','3','4','5','6','7','8','9') then
vAddress := vAddress || '室';
end if;
--地址准确性
vAddress := replace(vAddress,'地址可收','');
if instr(vaddress,')')>0 then
vaddress := replace(vaddress,')','');
end if;
if instr(vaddress,'(')>0 then
vaddress := replace(vaddress,'(','');
end if;
if instr(vaddress,'（')>0 then
vaddress := replace(vaddress,'（','');
end if;
if instr(vaddress,'）')>0 then
vaddress := replace(vaddress,'）','');
end if;
--匹配邮编(外地数据不匹配邮编)
if vState='合格' and v_name!='X' then
select Name
into vName
from userinfo@cm_xa
where id=rec_28424.oper_site;
--武汉
select count(*)
into nMemberID
from dic_area
where parentid=2954 and substr(areaname,1,2)=substr(rec_28424.col_16,1,2) and cancelflag=0;
if nMemberID>0 then
select AreaID
into vAreaID
from dic_area
where parentid=2954 and substr(areaname,1,2)=substr(rec_28424.col_16,1,2) and cancelflag=0;
else
vAreaID := null;
end if;
--姓名、手机号整理
vMemberName := v_name;
-- vmobile := nvl(rec_28424.Col_10,rec_28424.Col_2);
vmobile := substr(vmobile,-11);
--end of姓名、手机号整理
select count(*)
into nMemberID
from Memberinfo@yesmynet
where LogID=vMobile or Mobile=vMobile ;
-- or email=(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)
-- or name||address=v_name||vAddress;
if nMemberID=0 then
select seq_m_member_account_id.nextval@yesmynet
into nMemberID
from dual;
vaddress := '湖北省武汉市'||rec_28424.col_16||vaddress;
-- end if;
SELECT trunc(dbms_random.value(100000,999999)) INTO v_passwd FROM dual ;
insert into memberinfo@yesmynet(MemberID,LogID,LogPassWord,MemberTypeID,name,sex,address,postcode,
Mobile,RegSourceID,ComeSource,ComeAgent,ComeAgentNo,
SubmitDate,AreaID,
AddressType,AreaCode,come_time,Goodsline,exchange_point,total_point,cancelflag,rank_id)
values(nMemberID,vMobile,v_passwd,'MEMBER_TYPE_PERSONAL',vMemberName,decode(nvl(rec_28424.col_9,rec_28424.col_1),'女','f','男','m'),vAddress,trim(rec_28424.Col_11),
vMobile,'MEMBER_REG_SOURCE_OB','西安',vName,rec_28424.oper_site,
sysdate,vAreaID,substrb(rec_28424.Col_15,1,10),
'027',to_date(rec_28424.DoneTime,'yyyy-mm-dd hh34:mi:ss'),'GOODS_TYPE_WINE',100,100,0,'MEMBER_LEVEL_NORMAL');
SELECT max(id)+1 INTO v_passwdid from m_user_password_notify;
INSERT INTO m_user_password_notify(user_id,mobile,TRUE_NAME,PASSWORD,SUBMIT_TIME,ID)
VALUES (nMemberID,vMobile,vMemberName,v_passwd,SYSDATE,v_passwdid);
COMMIT;
--2009增加积分表信息
insert into m_Score_History@yesmynet(score_his_id,member_id,cha_reason,cha_score,
total_score,exchange_score,is_gain,version,CHA_DATE)
select seq_m_score_history_id.nextval@yesmynet,nMemberID,'SCORE_CHANGE_REASON_REG',100,
100,100,1,0,sysdate from dual;
--end of 2009增加积分表信息
--插入记录表
insert into REDWINE_TOMATO_TAB values (rec_28424.cid,rec_28424.oper_site,rec_28424.donetime,vMobile,'西安','武汉',sysdate,28424);
update item_28424_sp@cm_xa
set done_flag=7,recycle_result=1,recycle_date=sysdate
where cid=rec_28424.cid;
commit;
else
-------------------如果重复需要看重复日期
select count(*)
into nCount
from memberinfo@yesmynet
where (LogID=vMobile or Mobile=vMobile
-- or email=(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)
or name||address=v_name||vAddress) and to_char(submitdate,'yyyy-mm-dd')>=substr(rec_28424.DoneTime,1,10);
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
if nCount>=1 then --挖掘成功后用户自行注册
insert into REDWINE_TOMATO_TAB values (rec_28424.cid,rec_28424.oper_site,rec_28424.donetime,vMobile,'西安','武汉',sysdate,28424);
update item_28424_sp@cm_xa
set done_flag=7,recycle_result=1,recycle_date=sysdate
where cid=rec_28424.cid;
COMMIT;
else --拨打前注册，重复，拒收 ID 61694
update item_28424_sp@cm_xa
set done_flag=7,conclusion=61694,recycle_reason='拨打前注册'
where cid=rec_28424.cid; --设定座席不可见
end if;
END IF ;
else
INSERT INTO item_28424_problem VALUES (rec_28424.cid,vMobile,to_date(rec_28424.donetime,'yyyy-mm-dd hh34:mi:ss'));
--拒收ID: 61694
update item_28424_sp@cm_xa
set conclusion=61694,recycle_reason=vState
where cid=rec_28424.cid;
end if;
commit;
end loop;
close cur_28424;
end;
感谢你能够认真阅读完这篇文章，希望小编分享的“呼叫中心数据入库脚本的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持编程网，关注编程网精选频道，更多相关知识等着你来学习!
--结束END--
本文标题: 呼叫中心数据入库脚本的示例分析
本文链接: https://www.lsjlt.com/news/243952.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
2024-05-01
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0