最近接到的需求文档颇多，可谓忙得一塌糊涂，决定在周末给自己一些时间，静下来总结和思考。这里暂且先不讨论需求本身的严密性和合理性，只是针对需求简单谈谈自己对合理将需求转化成我们的程序，提高程序设计的严谨性以及可扩展性的一些理解和薄见。欢迎大家提出你们想法和意见。

需求描述大致如下：

CRM将信息同步到DBS

触发同步的条件：CRM侧走完一个工作流，产生正式表的数据，同时入同步任务表。

同步的方式：增量

方向：CRM-->DBS

具体流程可以是：CRM侧的接口机每天定时通过FTP/WebService等协议从对端DBS的文件服务器上取得DBS侧接口机放上去的文件并将该文件进行解析，同时调用DBS侧本身的存储过程(具体见下)将解析的信息入到DBS的数据表中，从而完成了信息的同步。同时同步的方式有三种：增加，修改和删除。即每天同步的数据中有增加的，也有对原来已经同步到DBS侧的捆绑品进行修改的，也有对已经同步到DBS侧的捆绑品进行删除的。

CRM侧需要同步的数据：
streamingNo          流水号ID
OPFlag                  1：增加 2：修改 3：删除 
PProductOfferID   捆绑类销售品ID：CRM生成的传统业务＋增值业务的捆绑类销售品ID
PackageID             捆绑类套餐ID：DBS生成并传递给CRM的套餐ID，该套餐被CRM打入捆绑类销售品[1...n]
ProductID             捆绑类产品ID，DBS生成并传递给CRM的产品ID，该产品被CRM打入销售品[1...n]
PNameCN             CRM侧中文套餐名称
PNameEN              CRM侧英文套餐名称
PDesCN                CRM侧套餐中文描述
PDesEn                 CRM侧套餐英文描述
chargingPolicyCN  CRM侧计费策略描述
chargingPolicyID   CRM侧计费策略标识

面对这样一个需求，首先考虑的是面对CRM过来的这些信息，需要在DBS侧建表进行存储。通过PProductOfferID,PackageID和ProductID字段可以分析得到，需要建立三张表：[1]存储捆绑类销售品[2]被打入捆绑类销售品的套餐[3]被打入捆绑类销售品的产品表。其中PackageID和ProductID分别是DBS同步之前过去的数据后被CRM打入捆绑类销售品后再返回给DBS的数据，其数据是1..n.(如1$2$3$4这样的形式表示有4个产品或套餐)

下面三个表分别对应上面的[1][2][3]
mixservice_crm：（其中主键为PProductOfferID）
PProductOfferID streamingNo OPFlag PNameCN PNameEN PDesCN PDesEn chargingPolicyCN chargingPolicyID

mixservice_ismp_pkgid：（其中主键为PkgIndex）

PkgIndex PackageID PackageName PProductOfferID

mixservice_ismp_proid：（其中主键为ProductIndex ）

ProductIndex ProductID ProductName PProductOfferID
注：PKGINDEX和PackageName通过PackageID在DBS侧系统中查询到,PRODUCTINDEX和ProductName一样.

下面是对上面需求，设计存储过程的思路：

【1】依据opflag的值1：增加 2：修改 3：删除 来对流程的整体把握。其中opflag=2：修改，这里没有采用我们程序理解上的update操作，而是先delete然后再insert的操作。因为这样，即可以完成修改的功能，也可以减少我们没必要的的代码量和程序执行时间和空间的浪费。

【2】下面只是程序的整体流程，具体代码有所省略，只列出整体的框架。欢迎大家提出更好的想法和建议。

  程序的流程，根据接口提供的信息，利用此过程将其入到DBS侧的mixservice_crm表中，同时将PackageID 和ProductID进行解析并分别循环入到mixservice_ismp_pkgid表和mixservice_ismp_proid表中。完成一次信息的增加操作，同时接口提供的数据还可能是对DBS侧已同步增加了的数据做修改和删除操作。

create or replace procedure pro_insert_dbs_crm
(
    i_streamingNo     varchar2,
    i_opflag          number,
    i_pproductofferid varchar2,
    i_packageid       varchar2,--1..n
    i_productid       varchar2,--1..n
    i_pnamecn         varchar2,
    i_pnameen         varchar2,
    i_pdescn          varchar2,
    i_pdesen          varchar2,
    i_chargingpolicycn         varchar2,
    i_chargingpolicyid         varchar2,
    o_ret    out number,    --存储过程的执行结果0-成功,1-未查询到相关数据,9-异常
    o_result out varchar2   --异常信息
)
as
    v_sposition1         number;
    v_sposition2         number;
    v_sposition11        number;
    v_sposition22        number;
    v_packageid          varchar2(100);
    v_productid          varchar2(100);
    v_pproductpkgnum     number(10,0) := 0;
    v_packagenum         number(10,0) := 0;
    v_productnum         number(10,0) := 0;
    v_productindex       number(10,0) := 0;
    v_pkgindex           number(10,0) := 0;
begin

    begin
        select count(1) into v_pproductpkgnum from mixservice_crm where pproductofferid = i_pproductofferid;
        if (v_pproductpkgnum = 0) then
            if (i_opflag = 1 ) the
                --insert dbs侧的crm表
                insert into mixservice_crm(streamingNo,opflag,pproductofferid,pnamecn,pnameen,pdescn,pdesen,chargingpolicycn,chargingpolicyid)
                values(i_streamingNo,i_opflag,i_pproductofferid,i_pnamecn,i_pnameen,i_pdescn,i_pdesen,i_chargingpolicycn,i_chargingpolicyid);
                if i_packageid is not null then
                    --将套餐ID分解并insert into mixservice_ismp_pkgid表   ----[1]
                    v_sposition1 := 1;
                    loop
                    v_sposition2 := instr(i_packageid, '$', v_sposition1);
                    if v_sposition2 = 0 then
                       v_packageid := substr(i_packageid, v_sposition1);
                    else
                       v_packageid := substr(i_packageid, v_sposition1, v_sposition2 - v_sposition1);
                    end if;
                    begin
                        select productpkgindex into v_pkgindex from v_ssrv_product_pkg where productpkgid = v_packageid;
                    exception
                        when others then
                       rollback;
                        o_ret  := 9;--异常
                        o_result := 'packageid not exists';
                        return;
                    end;
                    --insert dbs侧的被crm打入捆绑销售品的套餐表
                    insert into mixservice_ismp_pkgid(pkgindex,packageid,pproductofferid) values(v_pkgindex,v_packageid,i_pproductofferid);
                    v_sposition1 := v_sposition2 + 1;
                    v_packagenum := v_packagenum + 1;
                    exit when v_sposition2 = 0;
                    end loop;
                end if;
                if i_productid is not null then
                    --将产品ID分解并insert into mixservice_ismp_proid表，代码流程同[1],此略.
                end if;
                if ((v_packagenum = 0) and  (v_productnum = 0) ) then
                    rollback; -------rollback;
                    o_ret  := 9;
                    o_result := 'packageid and productid null';
                    return;
                end if;
            elsif (i_opflag = 2) then
                o_ret  := 1;
                o_result := 'no record';
            elsif (i_opflag = 3 ) then
                o_ret  := 1;
                o_result := 'no record';
            else
                o_ret  := 9;
                o_result := 'opflag error';
            end if;
            commit;-------commit;
                o_ret  := 0;
                o_result := 'sucess';
            return;
        else
            if (i_opflag = 1 ) then
                o_ret  := 9;
                o_result := 'opflag error';
            elsif ( i_opflag = 2 ) then ----opFlag=2 修改DBS侧同步过的捆绑销售品
                update  mixservice_crm set streamingNo = i_streamingNo,opflag = i_opflag,
                pproductofferid = i_pproductofferid,pnamecn = i_pnamecn,pnameen = i_pnameen,
                pdescn  = i_pdescn,pdesen = i_pdesen,chargingpolicycn = i_chargingpolicycn,
                chargingpolicyid = i_chargingpolicyid
                where pproductofferid = i_pproductofferid;
                delete from mixservice_ismp_pkgid where pproductofferid = i_pproductofferid;
                delete from mixservice_ismp_proid where pproductofferid = i_pproductofferid;
                if i_packageid is not null then
                    --将套餐ID分解并insert into mixservice_ismp_pkgid表,代码流程同[1]，此略.
                end if;
                if i_productid is not null then
                    --将产品ID分解并insert into mixservice_ismp_proid表,代码流程同[1]，此略.
                end if;
                if ((v_packagenum = 0) and  (v_productnum = 0) ) then
                   rollback;
                    o_ret  := 9;
                    o_result := 'packageid and productid null';
                    return;
                else
                    commit;
                    o_ret  := 0;
                    o_result := 'sucess';
            return;
                end if;
            elsif (i_opflag = 3) then --opFlag=3 删除DBS侧同步过的捆绑销售品
                delete from mixservice_crm where pproductofferid = i_pproductofferid;
                delete from mixservice_ismp_pkgid where pproductofferid = i_pproductofferid;
                delete from mixservice_ismp_proid where pproductofferid = i_pproductofferid;
            else
                rollback;
                o_ret  := 9；
                o_result := 'opflag error';
            end if;
            commit;
                o_ret  := 0;
                o_result := 'sucess';
            return;
        end if;
    exception
        when others then
            rollback;
        o_ret  := 9;--异常
        o_result := substr(sqlerrm, 1, 80);
    end;
    return;
end pro_insert_dbs_crm;

伪代码如下：

开始BEGIN
      先查看DBS侧的mixservice_crm表中是否已经有该捆绑销售品id。
      IF 没有
              IF 增加流程
                           捆绑销品信息入mixservice_crm表
                            IF 捆绑类套餐ID 不为空
                                    将捆绑类套餐ID循环入mixservice_ismp_pkgid表
                                    f1标志位+1【f1初始值为0】
                            IF 捆绑类产品ID 不为空
                                    将捆绑类产品ID循环入mixservice_ismp_proid表
                                    f2标志位+1【f2初始值为0】
                            IF f1和f2都等于零
                                    回滚，返回套餐id和产品为空，结束过程。
              ELSE IF修改流程
                    返回 无该类记录
              ELSE IF删除流程 
                    返回 无该类记录
              ELSE 其他
                      流程异常   
              END IF
              提交SQL语句，返回成功。
       ELSE 
              IF 增加流程
                       流程异常
              ELSE IF修改流程
                           更新mixservice_crm表，根据捆绑销售品id。
                           删除mixservice_ismp_pkgid表信息，根据捆绑品id。
                           删除mixservice_ismp_proid表信息，根据捆绑品id。
                           IF 捆绑类套餐ID 不为空
                                    将捆绑类套餐ID循环入mixservice_ismp_pkgid表
                                    f1标志位+1【f1初始值为0】
                            IF 捆绑类产品ID 不为空
                                    将捆绑类产品ID循环入mixservice_ismp_proid表
                                    f2标志位+1【f2初始值为0】
                            IF f1和f2都等于零
                                    回滚，返回套餐id和产品为空，结束过程。
               ELSE IF删除流程 
                           删除mixservice_crm表信息，根据捆绑品id。
                           删除mixservice_ismp_pkgid表信息，根据捆绑品id。
                           删除mixservice_ismp_proid表信息，根据捆绑品id。
               ELSE 其他
                      流程异常  
               END IF 
              提交SQL语句，返回成功。 
       END IF
异常EXCEPTION
       回滚，返回异常信息，结束过程。
结束END