短信调用系统的设计和实现

摘要：随着公司短信业务的增加，基于数据管理人员手工调用短信数据的模式已不再适用。因此公司决定开发一套《短信调用系统》，由业务人员自己操作，方便、快捷地进行短信调用。《短信调用系统》的实施，成功地解决了公司面临的问题。

关键词：短信调用；SQL性能优化；大批量数据快速导出；快速去重

中图分类号：TP393文献标识码：A文章编号：1009-3044(2011)20-4805-03

随着公司规模的不断扩大，作为公司产品的数据的规模、信息的深度及广度也随之扩张，公司的数据库管理人员承担着日益繁重的工作。

1 问题描述

短信调用原来的工作方式是由短信运营人员将调用要求提交给数据管理人员，再由数据管理人员在PL/SQL中写SQL代码，将满足条件的短信数据调出，交给短信运营人员。这种工作方式所产生的问题是：

1）由于项目多，数据管理人员需要经常加班。

2）调用条件多，不规范，只要数据管理人员有一个条件漏掉，就会将数据调用条件弄错，从而产生客户投诉。

在这种背景下为了减少数据管理人员工作量，满足公司的大批量的短信项目调用需求，自主开发了短信调用系统。

2 系统总体设计

系统流程如图1所示。

1）相关技术

后台数据库：Oracle 10g；前台软件：Delphi 6.0；体系结构：C/S；计算机语言：Sql、Object Pascal。

2）功能设置

基本信息：记录项目的基本信息，项目负责人，调用条件

运作情况：一个项目可能调几批数据，按城市或条件，记录每个批次的调条件和数量。

去重功能：与以前运作过的项目数据进行去重，以前已经发过的数据要滤掉，由于是短信数据，所以手机号是没有重复的，唯一的，自身没重复。

查询数量：确定条件，查询数据量

保存数据：查询好的数据保存到项目临时表

数据导出：项目数据导出为CSV文件，交给负责运营的人员

数据回库：将调出的数据写入到项目数据表op_smsdata_auto中

样本提取：提取已运作好的项目样本数据给客户。

3）实现要点

调用条件的确定；调用小表的生成；查询数据量的函数；保存数据的存储过程；数据导出程序；写入项目数据表的存储过程。

3 系统功能实现

1）后台表设计

短信调用项目表：op_projinfo(projid 项目流水号，projname 项目名称，projdate 项目日期, principalid 项目负责人ID，princalname 项目负责人，princalname希望数量，deliverdesire调用条件，realsendqty 实际调出数量)；一次数据调用对应一个项目。

项目批次表：op_smsproj(smsprojid 批次流水号，sendbatch 批次序号，projid 项目ID，createdate 创建日期，removeproj 去重ID ，realcustomercount 实际调用数量，transferflag 导出标志)；一个项目可能对应几个批次数据。

调表条件表：op_smsremark(smsremarkid 记录流水号，projid 项目ID，batchid 调用批次序号，parameters 查询参数， querystring 查询SQL， demand 查询要求， lastqty 调出数量，removedupprojlist 去重项目， fieldorrelation 或关系字符串 )；记录每次查询和调用的条件，以备查询。

项目数据表：op_smsdata(projid 项目ID，customerid 客户ID，mobile 手机，importbatch 数据批次)；记录调出数据。

2）确定调用条件为基本信息，车主信息，楼盘信息三大类，具体条件如图2所示。

3）调用小表的生成

公司数据的情况是所有手机数据都在客户数据表b2c_customer中，这张表很大，有1亿多条记录，另外还有两个相关联的楼盘表b2c_building和车主表b2c_carexpens。客户表是按城市区号进行分区的，人工调取数据时会根据分区进行查询和调用，会根据条件和楼盘表、车主表连查，会做很多临时表以便提高速度，减少调用时间。这三张表都会经常做增加，更新操作。根据这种情况，将客户数据表按区号生成328个调用小表，同时将车主信息整合到查询小表中，因为楼盘表数据量不大，两表连查时并不耗费时间，所以就没将楼盘表整到调用小表中。

生成调用小表的的存储过程是B2c_Query_Recreate，其功能按区号循环生成调用基础表，其关键代码如下：

execute immediate 'create table '||v_tablename||' as

(select * from ( select a.customerid,marrydate,name,idcard,a.areacode,mobile,expenseability,sex,birthyear,educationlevel,buildingid,district,mobilerank,

invalid,reach,incorrect,road,mobilecity,address,postcode,location,bizpost,bizbuildingid,email,blacklist,addressstatus,mobilestatus,

addressrank,salary,duty,adddate,lastcleandate,case when b.price>=0 then 1 else null end is_car,b.carbrand,b.smallbrand,b.cartype,

b.expensedate,b.price,b.licenseplate from b2c_customer a,b2c_carexpense b

where a.areacode='''||v_areacode||''' and a.customerid=b.customerid(+) order by dbms_random.value))';

v_tablename需要生成的调用小表表名

b2c_customer客户数据表

b2c_carexpense车主表

v_areacode城市区号变量

基础表生成好之后，按mobile手机进行去重将调用小表中符合调用短信条件的customerid写入到表B2C_QUERY_TEMP中

execute immediate 'create table B2C_QUERY_TEMP as (select customerid from

(select a.*,ROW_NUMBER() OVER (PARTITION BY mobile ORDER BY

case when mobilerank=''R'' then 1

when mobilerank=''R-'' then 2

when mobilerank=''O'' then 3

when mobilerank=''O-'' then 4

when mobilerank=''A'' then 5

when mobilerank=''D'' then 6

when mobilerank is nullthen 7

else 99 end asc

) AS rrr from

(select customerid,mobile,mobilerank from '||v_tablename||' a where not exists (select ''x'' from dm2.b2c_backlist_sms@rwdata where mobile=a.mobile)

and mobile is not null

and (invalid in (''A'',''B'',''C'') or invalid is null )

and blacklist is null

and mobilecity='''||v_city||''') a) where rrr=1)';

将表v_tablename中的customerid存在于表B2C_QUERY_TEMP表中的记录的sms设置为1，这样经过更新后的数据就是能够进行短信的去重过的所有数据了。

调用小表建立好之后，在经常查询的字段增加索引，因为小表不做插入，更新动作，并且消费能力只有几种类型，适合建立BITMAP索引，例如在EXPENSEABILITY上建立BITMAP索引，代码如下：

execute immediate 'create bitmap index IDX_B2C_'||v_areacode||'_EXPENSEABILITY on '||v_tablename||' (EXPENSEABILITY) tablespace TBS_INDEX_CDMQ'；

年龄，性别，行政区也适合建立BITMAP索引，

而在手机字段上则建立普通索引。

建立调用小表的优点是数量小，查询、调用响应快，时间短！缺点是由客户数据表b2c_customer这张大表生成的，信息有一个延迟，大约一个星期左右，短信业务要求数据量大，快速，信息延迟一个星期，这对业务没有影响。

4）查询数据量函数Sms_Query

Sms_Query有两个输入参数Condition和P_projid，Condition是前台生成的条件字符串，P_projid是存放需要去重的projid，之前的调用小表按sms =1查询就可以达到项目数据的手机不重复的效果。

条件字符串简单实例：

Condition：198440,23|2|广州|||奢侈品消费,高消费|||越秀区,天河区|||||||||||||||||||

198440 代表本次项目的ID

23 代表当前操作用户ID

2 表示是批次数据

广州 表示数据的城市

奢侈品消费,高消费要求的消费能力

越秀区,天河区要求的行政区

P_projid：198590

去重项目ID为198590，将项目198590的数据写入到去重表SMS_QC_198440_23

后台生成的查询SQL为

57296|select count(*) from b2c_query_020 a where 1=1and (expenseability in ('奢侈品消费','高消费')) and (a.district in ('越秀区','天河区')) and (sms =1) and not exists (select 'x' from SMS_198440_23 where mobile=a.mobile)and not exists (select 'x' from SMS_QC_198440_23 where mobile=a.mobile)从而得到符合条件的数据量。

显示界面如图3所示。

图3

5）保存符合查询条件数据的存储过程Sms_Arrange

Sms_Arrange有3个参数Condition和P_projid，与查询函数不同的是，它还多了一个Want_num参数，这个参数控制想到保存的数量，当然它的值不超过查询的最大值，如果超过则认为是查询最大值。与Sms_Query相同的是它也生成一个SQL代码并执行，例如上例条件执行，并希望保存10000条，所得到的关键代码为：

Insert into SMS_198440_23 select customerid,mobile,’2’ databatchfrom b2c_query_020 a where 1=1and (expenseability in ('奢侈品消费','高消费')) and (a.district in ('越秀区','天河区')) and (sms =1) and not exists (select 'x' from SMS_198440_23 where mobile=a.mobile)and not exists (select 'x' from SMS_QC_198440_23 where mobile=a.mobile) and rownum

这样就将数据保存到调用临时表SMS_198440_23

显示界面如图4所示。

图4

6）导出程序sqluldr

ORACLE本身自带的SPOOL速度比较慢，UTL_FILE包速度虽然快，但是只能将数据导出到服务器端，不符合公司要求导出到客户端的要求。导出程序sqluldr

是由PRO*C编写的一个数据导出程序，其调用方法实例为：

E:\roadway_data\CDM\CDMAPP\sqluldr.exe cdm/f2t9h3j5$@rwdata query="select * fromSMS_198440_23 " record=0x0d0x0a field= ","file=E:\交大_198440.csv head=yes

E:\roadway_data\CDM\CDMAPP\sqluldr.exe是程序的位置，cdm/f2t9h3j5$@rwdata分别代表数据库登录名，密码和库名称，0x0d0x0a代表回车加换行，field= ,代表字段间的分割符号是逗号，file=E:\交大_198440.csv代表文件写入位置，head=yes表示导出的数据带字段名。=号后内容可以是在程序中的变量，所以利用这个程序就可以导出任何select查询结果，并且由于是调用底层OCI函数，所以导出速度会特别的快。

7）数据回库原存储过程Back_To_CDM

Back_To_CDM有3个参数，P_projid、P_userid和P_databatch，P_userid为项目负责人ID，P_databatch为数据批次，执行导出后会执行这个存储过程，将调用临时表的数据写到op_smsdata表中，以备后期查询统计， 同时项目所产生的调用临时表和去重临时表的表名写入到只有一个表名字段的CDM_SMS_NEED_REMOVE表中。

8）删除临时表的存储过程Remove_invalid_smsTable

执行删除的存储过程，会将这张表CDM_SMS_NEED_REMOVE中所列表名的表drop 掉。同时将表名这条记录删除。将这个存储过程设置为任务，由系统自动定期完成，不需人工维护。

4 总结

原来的短信调用方式，一个数据管理人员一天最多调用20个短信项目，超过20个就要加班。自从短信调用系统投入使用后，由短信运营部门的人员自己来调，一个人一天可以调30个，她们只要点击鼠标就可以在数据库中获取想要的数据集合，并且整个获取数据的过程是快速且准确的，不需要再催促数据库管理人员，自已轻松完成。数据库管理人员也解放出来做其他的工作。短信调用系统成功地解决了公司所面临的问题，公司非常满意。

参考文献：

[1] 王海亮,林立新.精通ORACLE 10g PL/SQL 编程[M].北京:中国水利水电出版社,2004.

注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文