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

/短信调用系统的设计和实现摘要：随着公司短信业务的增加，基于数据管理人员手工调用短信数据的模式已不再适用。因此公司确定开发一套《短信调用系统》，由业务人员自己操作，便利、快捷地进行短信调用。《短信调用系统》的实施，成功地解决了公司面临的问题。关键词：短信调用；SQL性能优化；大批量数据快速导出；快速去重中图分类号：TP393文献标识码：A文章编号：1009-3044(2011)20-4805-03随着公司规模的不断扩大，作为公司产品的数据的规模、信息的深度及广度也随之扩张，公司的数据库管理人员担当着日益繁重的工作。1问题描述短信调用原来的工作方式是由短信运营人员将调用要求提交给数据管理人员，再由数据管理人员在PL/SQL中写SQL代码，将满足条件的短信数据调出，交给短信运营人员。这种工作方式所产生的问题是：1）由于项目多，数据管理人员须要经常加班。2）调用条件多，不规范，只要数据管理人员有一个条件漏掉，就会将数据调用条件弄错，从而产生客户投诉。在这种背景下为了削减数据管理人员工作量，满足公司的大批量的短信项目调用需求，自主开发了短信调用系统。2系统总体设计系统流程如图1所示。1）相关技术后台数据库：Oracle10g；前台软件：Delphi6.0；体系结构：C/S；计算机语言：Sql、ObjectPascal。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，其功能按区号循环生成调用基础表，其关键代码如下：executeimmediate'createtable'||v_tablename||'as(select*from(selecta.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,casewhenb.price>=0then1elsenullendis_car,b.carbrand,b.smallbrand,b.cartype,b.expensedate,b.price,b.licenseplatefromb2c_customera,b2c_carexpensebwherea.areacode='''||v_areacode||'''anda.customerid=b.customerid(+)orderbydbms_random.value))';v_tablename须要生成的调用小表表名b2c_customer客户数据表b2c_carexpense车主表v_areacode城市区号变量基础表生成好之后，按mobile手机进行去重将调用小表中符合调用短信条件的customerid写入到表B2C_QUERY_TEMP中executeimmediate'createtableB2C_QUERY_TEMPas(selectcustomeridfrom(selecta.*,ROW_NUMBER()OVER(PARTITIONBYmobileORDERBYcasewhenmobilerank=''R''then1whenmobilerank=''R-''then2whenmobilerank=''O''then3whenmobilerank=''O-''then4whenmobilerank=''A''then5whenmobilerank=''D''then6whenmobilerankisnullthen7else99endasc)ASrrrfrom(selectcustomerid,mobile,mobilerankfrom'||v_tablename||'awherenotexists(select''x''fromdm2.b2c_backlist_sms@rwdatawheremobile=a.mobile)andmobileisnotnulland(invalidin(''A'',''B'',''C'')orinvalidisnull)andblacklistisnullandmobilecity='''||v_city||''')a)whererrr=1)';将表v_tablename中的customerid存在于表B2C_QUERY_TEMP表中的记录的sms设置为1，这样经过更新后的数据就是能够进行短信的去重过的全部数据了。调用小表建立好之后，在经常查询的字段增加索引，因为小表不做插入，更新动作，并且消费实力只有几种类型，适合建立BITMAP索引，例如在EXPENSEABILITY上建立BITMAP索引，代码如下：executeimmediate'createbitmapindexIDX_B2C_'||v_areacode||'_EXPENSEABILITYon'||v_tablename||'(EXPENSEABILITY)tablespaceTBS_INDEX_CDMQ'；年龄，性别，行政区也适合建立BITMAP索引，而在手机字段上则建立一般索引。建立调用小表的优点是数量小，查询、调用响应快，时间短！缺点是由客户数据表b2c_customer这张大表生成的，信息有一个延迟，大约一个星期左右，短信业务要求数据量大，快速，信息延迟一个星期，这对业务没有影响。4）查询数据量函数Sms_QuerySms_Query有两个输入参数Condition和P_projid，Condition是前台生成的条件字符串，P_projid是存放须要去重的projid，之前的调用小表按sms=1查询就可以达到项目数据的手机不重复的效果。条件字符串简洁实例：Condition：198440,23|2|广州|||奢侈品消费,高消费|||越秀区,天河区|||||||||||||||||||198440代表本次项目的ID23代表当前操作用户ID2表示是批次数据广州表示数据的城市奢侈品消费,高消费要求的消费实力越秀区,天河区要求的行政区P_projid：198590去重项目ID为198590，将项目198590的数据写入到去重表SMS_QC_198440_23后台生成的查询SQL为57296|selectcount(*)fromb2c_query_020awhere1=1and(expenseabilityin('奢侈品消费','高消费'))and(a.districtin('越秀区','天河区'))and(sms=1)andnotexists(select'x'fromSMS_198440_23wheremobile=a.mobile)andnotexists(select'x'fromSMS_QC_198440_23wheremobile=a.mobile)从而得到符合条件的数据量。显示界面如图3所示。图35）保存符合查询条件数据的存储过程Sms_ArrangeSms_Arrange有3个参数Condition和P_projid，和查询函数不同的是，它还多了一个Want_num参数，这个参数限制想到保存的数量，当然它的值不超过查询的最大值，假如超过则认为是查询最大值。和Sms_Query相同的是它也生成一个SQL代码并执行，例如上例条件执行，并希望保存10000条，所得到的关键代码为：InsertintoSMS_198440_23selectcustomerid,mobile,’2’databatchfromb2