Shulou(Shulou.com)06/01 Report--

This article mainly introduces the example analysis of the call center data storage script, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

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 rejected

AND (qc_first! = 0 or qc_first is null)

And done_flag7

And substr (donetime,1,10) 12 or length (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: = 'mobile phone is not standard'

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: = 'Mobile phone is not standard'

Else vmobile: = rec_28424.Col_10

End if

-- if length (trim (rec_28424.Col_105)) > 64 then

-- vState: = 'email is not standard'

-- end if

VAddress: = to_single_byte (trim (rec_28424.Col_12))

If the address ends with a number, fill in the "room".

If substr (vAddress,length (vAddress), 1) in (then)

VAddress: = vAddress | | 'Room'

End if

-- address accuracy

/ * if (instr (vAddress,')') = 0 or instr (vAddress,')') = 0) then

If rec_28424.Col_15=' company address' then

If GetDigitalCount (vAddress) = 0 or (substr (vAddress,length (vAddress)) ='no.'or substr (vAddress,length (vAddress)) = 'building') then

VState: = 'address is not standard'

End if

Else-Home address

If GetDigitalCount (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

-- match the zip code (non-local data do not match the zip code)

If vState=' qualified 'and vault nameplate' qualified X' then

Select Name

Into vName

From userinfo@cm_xa

Where id=rec_28424.oper_site

-- Wuhan

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

-- sorting out names and mobile phone numbers

VMemberName: = v_name

/ * if rec_28424.Col_10 is null or instr (rec_28424.Col_10,'*') > 0 or

(fun_IsNumber (rec_28424.Col_10) = 1 and length (rec_28424.Col_10) 11 and length (rec_28424.Col_10) 12 and length (rec_28424.Col_10) 0 or

(fun_IsNumber (rec_28424.col_2) = 1 and length (rec_28424.col_2) 11 and length (rec_28424.col_2) 12 and length (rec_28424.col_2) 0 then replace (trim (rec_28424.Col_105), 'none') 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

/ * if instr (vaddress,rec_28424.col_10) > 0 and instr (vaddress,rec_28424.col_9) > 0 then

Vaddress: = vaddress

End if

If instr (vaddress,rec_28424.col_10) > 0 and instr (vaddress,rec_28424.col_9) = substr (rec_28424.DoneTime,1,10)

-- +

If nCount > = 1 then-users register after successful mining

/ * if substr (vAddress,1,length (rec_28424.Col_100)) = rec_28424.Col_100 then

VAddress: = substr (vAddress,length (rec_28424.Col_100) + 1)

End if

VAddress: = 'Beijing' | | rec_28424.Col_100 | | vAddress

Update memberinfo@yesmynet

Set name=nvl (name,vMemberName)

Sex=nvl (sex,decode (nvl (rec_28424.col_18,rec_28424.col_1), 'female', 0 'male', 1))

Address=nvl (address,vAddress)

PostCode=nvl (postcode,trim (rec_28424.Col_11))

-- Mobile=nvl (mobile,vMobile)

Email=nvl (email, (case when instr (replace (trim (rec_28424.Col_105), 'none'),'@') > 0 then replace (trim (rec_28424.Col_105), 'none') else null end))

ComeAgent=vName

ComeAgentNo=rec_28424.oper_site

ComeCallCenterID=2

Addresstype=nvl (addresstype,substrb (rec_28424.Col_19,1,10))

Areacode=nvl (areacode, '010')

Where LogID=vMobile or Mobile=vMobile

Or email= (case when instr (replace (trim (rec_28424.Col_105), 'none'),'@') > 0 then replace (trim (rec_28424.Col_105), 'none') else null end)

Or name | | address=v_name | | vAddress

COMMIT;*/

Insert into REDWINE_TOMATO_TAB values (rec_28424.cid,rec_28424.oper_site,rec_28424.donetime,vMobile,' Xi'an', 'Wuhan', 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-register before dialing, repeat, reject ID 61694

Update item_28424_sp@cm_xa

Register before set done_flag=7,conclusion=61694,recycle_reason=' dialing'

Where cid=rec_28424.cid;-sets the agent invisible

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

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

/ * select COUNT (*) INTO v_ccnum2 from item_28424_sp@cm_xa a meme projectresultinfoothcmroomxa b

Where conclusion in (59605, 59612)-- 59626

AND (qc_first! = 0 or qc_first is null)

And done_flag=7

And substr (donetime,1,10)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.