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

短信调用系统的设计和实现 摘要：随着公司短信业务的增加，基于数据管理人员手工调用短信数据的模式已不再适用。因此公司决定开发一套短信调用系统，由业务人员自己操作，方便、快捷地进行短信调用。短信调用系统的实施，成功地解决了公司面临的问题。 关键词：短信调用；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_smsrwdata 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：,23|2|广州|奢侈品消费,高消费|越秀区,天河区| 代表本次项目的ID 23 代表当前操作用户ID 2 表示是批次数据 广州 表示数据的城市 奢侈品消费,高消费要求的消费能力 越秀区,天河区要求的行政区 P_projid： 去重项目ID为，将项目的数据写入到去重表SMS_QC_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_23 where mobile=a.mobile)and not exists (select x from SMS_QC_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_23 select customerid,mobile,2 databat