Mysql-Procedure1

Mysql存储过程案例1

该存储过程是为了数据的同步，将销售订单主表与明细表的中间表的数据同步到本地的销售订单表与明细表中。
在中间表中有一个同步状态字段，若同步状态字段为0时表示没有同步，则去同步数据。
若同步的数据中在本地已存在，则更新订单数据，否则新增。

-- DROP PROCEDURE InterfaceSync;
CREATE PROCEDURE InterfaceSync(OUT pReturn INT)
BEGIN
    /*
        ReturnP 返回0表示存储过程执行成功，返回1表示存储过程执行失败
    */
    -- DECLARE pReturn INT;
    -- 销售订单接口主表 SKT39  SKT16
    -- 订单号 SKF947 SKF182
    DECLARE mOrderCode VARCHAR(255);
    -- 制单日期 SKF948 SKF183
    DECLARE mbuildDate DATE;
    -- 合同日期 SKF949 SKF184
    DECLARE mContractDate DATE;
    -- 样本日期 SKF950 SKF185
    DECLARE mSampleDate DATE;
    -- 最终客户 SKF951 SKF186
    DECLARE mFinalCustomer VARCHAR(255);
    -- 医院 SKF952 SKF187
    DECLARE mHospital VARCHAR(255);
    -- 取样人 SKF953 SKF188
    DECLARE mSampler VARCHAR(255);
    -- 代理商 SKF954 SKF189
    DECLARE mAgent VARCHAR(255);
    -- 医院等级 SKF955 SKF190
    DECLARE mHospitalLevel VARCHAR(255);
    -- 出生日期 SKF956 SKF191
    DECLARE mBirthday DATE;
    -- 客户身份证号 SKF957 SKF192
    DECLARE mCustomerIdCard VARCHAR(255);
    -- 收货人电话 SKF958 SKF193
    DECLARE mConsigneePhone VARCHAR(255);
    -- 收货人地址 SKF959 SKF194
    DECLARE mConsigneeAddress VARCHAR(255);
    -- 省份 SKF960 SKF195
    DECLARE mProvince VARCHAR(255);
    -- 总数量 SKF961 SKF196
    DECLARE mTotalQuantity INT;
    -- 订单类型 SKF962 SKF197
    DECLARE mOrderType VARCHAR(255);
    -- 销售区域主管 SKF963 SKF198
    DECLARE mSaleManager VARCHAR(255);
    -- 交接状态 SKF964 SKF199
    DECLARE mHandoverState VARCHAR(255);
    -- 收样人 SKF969 SKF299
    DECLARE mSampleCollector VARCHAR(255);
    -- 收养日期 SKF970 SKF300
    DECLARE mSampleCollectorDate DATE;
    -- 交接人 SKF971 SKF301
    DECLARE mHandoverMan VARCHAR(255);
    -- 交接时间 SKF972 SKF302
    DECLARE mHandoverDate DATE;
    -- 检测人群 SKF973 SKF812
    DECLARE mDetectCrowd VARCHAR(255);
    -- 性别 SKF974 SKF813
    DECLARE mSex VARCHAR(255);
    -- 市场 SKF975 SKF814
    DECLARE mMarket VARCHAR(255);
    -- 业务员 SKF976 SKF815
    DECLARE mSaleMan VARCHAR(255);
    -- 是否尿样 SKF977 SKF816
    DECLARE mIsUrineSample VARCHAR(255);
    -- 客户编号 SKF978 SKF925
    DECLARE mCustomerCode VARCHAR(255);
    -- 代理商编号 SKF979 SKF926
    DECLARE mAgentCode VARCHAR(255);
    -- 医院编号 SKF980 SKF927
    DECLARE mHospitalCode VARCHAR(255);
    -- 接收状态 SKF1001
    DECLARE mSyncStatus SMALLINT;
    
    -- 销售订单接口子表 SKT40 SKT18
    -- 订单编号 SKF981 SKF216
    DECLARE sOrderCode VARCHAR(255);
    -- 物料编号 SKF982 SKF217
    DECLARE sMaterialCode VARCHAR(255);
    -- 物料名称 SKF983 SKF218
    DECLARE sMaterialName VARCHAR(255);
    -- 基因主编号 SKF984 SKF219
    DECLARE sGeneMasterCode VARCHAR(255);
    -- 基因编号 SKF985 SKF220
    DECLARE sGeneSlaveCode VARCHAR(255);
    -- 单位 SKF986 SKF221
    DECLARE sUnit VARCHAR(255);
    -- 数量 SKF987 SKF222
    DECLARE sQuantity INT;
    -- 含税单价 SKF988 SKF223
    DECLARE sUnitPriceTax DOUBLE;
    -- 价税合计 SKF989 SKF224
    DECLARE sTotalPriceTax DOUBLE;
    -- 备注 SKF990 SKF225
    DECLARE sMark VARCHAR(255);
    -- 剂型 SKF995 SKF311
    DECLARE sDosageForm VARCHAR(255);
    -- 报告类型 SKF996 SKF312
    DECLARE sReportType VARCHAR(255);
    -- 检测物质 SKF998 SKF823
    DECLARE sDetectionSubstance VARCHAR(255);
    -- 样本状态 SKF999 SKF824
    DECLARE sSampleType VARCHAR(255);
    -- 样本体积 SKF1000 SKF825
    DECLARE sSampleVolume VARCHAR(255);
    -- 接收状态 SKF1002
    DECLARE sSyncStatus SMALLINT;
    
    -- 判断主子表是否拥有未同步数据
    DECLARE mIsNoSync INT;
    DECLARE sIsNoSync INT;

    -- 判断主子表中是否存在该订单信息
    DECLARE mExist INT;
    DECLARE sExist INT;
    
    -- 判断主子表是否更新成功
    DECLARE mReturn INT;
    DECLARE sReturn INT;
    -- 循环游标判断变量
    DECLARE done INT DEFAULT 0;
    /*主表游标的定义*/
    DECLARE cur_1 CURSOR FOR 
    SELECT SKF947,SKF948,SKF949,SKF950,SKF951,SKF952,SKF953,SKF954,
    SKF955,SKF956,SKF957,SKF958,SKF959,SKF960,SKF961,SKF962,SKF963,
    SKF964,SKF969,SKF970,SKF971,SKF972,SKF973,SKF974,SKF975,SKF976,
    SKF977,SKF978,SKF979,SKF980 FROM SKT39 WHERE SKF1001 = 0 AND
    SKF947 IS NOT NULL AND SKF947 != '';
    /*子表游标定义*/
    DECLARE cur_2 CURSOR FOR
    SELECT SKF981,SKF982,SKF983,SKF984,SKF985,SKF986,SKF987,SKF988,SKF989,
    SKF990,SKF995,SKF996,SKF998,SKF999,SKF1000 
    FROM SKT40 WHERE SKF1002 = 0 AND SKF981 IS NOT NULL AND SKF982 IS NOT NULL
    AND SKF981 != '' AND SKF982 != '';
    /*主子游标循环判断,指定循环结束时的返回值*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 开启事务
    START TRANSACTION;
    -- 开始获取主子表未同步数据
    SELECT COUNT(*) INTO mIsNoSync FROM SKT39 WHERE SKF1001 = 0;
    SELECT COUNT(*) INTO sIsNoSync FROM SKT40 WHERE SKF1002 = 0;
    -- 主表要是拥有未同步数据，开始同步
    IF mIsNoSync > 0 THEN
        -- 设置主子表默认返回值
        SET mReturn = 1;
        SET sReturn = 1;
    
        -- 开启游标
 		OPEN cur_1;
 		WHILE done != 1 DO
            -- 初始化游标主表数据
 			FETCH cur_1 INTO mOrderCode,mbuildDate,mContractDate,mSampleDate,mFinalCustomer,mHospital,
            mSampler,mAgent,mHospitalLevel,mBirthday,mCustomerIdCard,mConsigneePhone,mConsigneeAddress,
            mProvince,mTotalQuantity,mOrderType,mSaleManager,mHandoverState,mSampleCollector,
            mSampleCollectorDate,mHandoverMan,mHandoverDate,mDetectCrowd,mSex,mMarket,mSaleMan,
            mIsUrineSample,mCustomerCode,mAgentCode,mHospitalCode;
 			-- 判断主表数据是否存在，不存在则进行插入操作，并更新接口表同步状态
            -- 若存在，则进行主表更新操作，并更新接口表同步状态
 			-- SKT16是销售订单的主表
 			SELECT COUNT(*) INTO mExist FROM  SKT16 WHERE SKF182= mOrderCode;
 			IF mExist = 0 THEN
 				INSERT INTO SKT16
 				(SKF182,SKF183,SKF184,SKF185,SKF186,SKF187,SKF188,SKF189,SKF190,SKF191,SKF192,SKF193,
                SKF194,SKF195,SKF196,SKF197,SKF198,SKF199,SKF299,SKF300,SKF301,SKF302,SKF812,SKF813,
                SKF814,SKF815,SKF816,SKF925,SKF926,SKF927) 
                VALUE 
                (mOrderCode,mbuildDate,mContractDate,mSampleDate,mFinalCustomer,mHospital,mSampler,
                mAgent,mHospitalLevel,mBirthday,mCustomerIdCard,mConsigneePhone,mConsigneeAddress,
                mProvince,mTotalQuantity,mOrderType,mSaleManager,mHandoverState,mSampleCollector,
                mSampleCollectorDate,mHandoverMan,mHandoverDate,mDetectCrowd,mSex,mMarket,mSaleMan,
                mIsUrineSample,mCustomerCode,mAgentCode,mHospitalCode);
            
                UPDATE SKT39 SET SKF1001 = 1 WHERE SKF947 = mOrderCode;
            -- 当存在值等于1时更新数据
            ELSEIF mExist = 1 THEN
                UPDATE SKT16 SET SKF182=mOrderCode,SKF183=mbuildDate,SKF184=mContractDate,SKF185=mSampleDate,
                SKF186=mFinalCustomer,SKF187=mHospital,SKF188=mSampler,SKF189=mAgent,SKF190=mHospitalLevel,
                SKF191=mBirthday,SKF192=mCustomerIdCard,SKF193=mConsigneePhone,SKF194=mConsigneeAddress,
                SKF195=mProvince,SKF196=mTotalQuantity,SKF197=mOrderType,SKF198=mSaleManager,SKF199=mHandoverState,
                SKF299=mSampleCollector,SKF300=mSampleCollectorDate,SKF301=mHandoverMan,SKF302=mHandoverDate,
                SKF812=mDetectCrowd,SKF813=mSex,SKF814=mMarket,SKF815=mSaleMan,SKF816=mIsUrineSample,
                SKF925=mCustomerCode,SKF926=mAgentCode,SKF927=mHospitalCode WHERE SKF182= mOrderCode;
                UPDATE SKT39 SET SKF1001 = 1 WHERE SKF947 = mOrderCode;
        
            END IF;
    END WHILE;
        -- 关闭主表的游标
    CLOSE cur_1;
        -- 设置主表是否更成功
        SET mReturn = 0;
    -- 当同步的数据等于0代表数据同步完成，则设置mReturn 返回值为0
    ELSE
        
        SET mReturn = 0;
    END IF;
    -- 初始化循环参数
    SET done = 0;
    -- 子表要是拥有未同步数据，开始同步。
    IF sIsNoSync > 0 THEN
        -- 开启子表的游标
        OPEN cur_2;
        
        WHILE done != 1 DO
            -- 初始化游标子表数据
            FETCH cur_2 INTO sOrderCode,sMaterialCode,sMaterialName,sGeneMasterCode,
            sGeneSlaveCode,sUnit,sQuantity,sUnitPriceTax,sTotalPriceTax,sMark,
            sDosageForm,sReportType,sDetectionSubstance,sSampleType,sSampleVolume;
            -- 判断子表数据是否存在，不存在则进行插入操作，并更改接口表同步状态
            -- 存在则做更新操作，并更改接口表同步状态
            -- SKT18是销售订单的子表
            SELECT COUNT(*) INTO sExist FROM SKT18 WHERE SKF216=sOrderCode AND SKF217=sMaterialCode;
            IF sExist = 0 THEN
                INSERT INTO SKT18 
                (SKF216,SKF217,SKF218,SKF219,SKF220,SKF221,SKF222,SKF223,SKF224,SKF225,SKF311,SKF312,
                SKF823,SKF824,SKF825) 
                VALUE
                (sOrderCode,sMaterialCode,sMaterialName,sGeneMasterCode,sGeneSlaveCode,sUnit,sQuantity,
                sUnitPriceTax,sTotalPriceTax,sMark,sDosageForm,sReportType,sDetectionSubstance,sSampleType,
                sSampleVolume);
                
                UPDATE SKT40 SET SKF1002 = 1 WHERE SKF981=sOrderCode AND SKF982=sMaterialCode;
            -- 当存在值等于1时, 更新数据
            ELSEIF sExist = 1 THEN
                
                UPDATE SKT18 SET SKF216=sOrderCode,SKF217=sMaterialCode,SKF218=sMaterialName,SKF219=sGeneMasterCode,
                SKF220=sGeneSlaveCode,SKF221=sUnit,SKF222=sQuantity,SKF223=sUnitPriceTax,SKF224=sTotalPriceTax,
                SKF225=sMark,SKF311=sDosageForm,SKF312=sReportType,SKF823=sDetectionSubstance, SKF824=sSampleType,SKF825=sSampleVolume;
                UPDATE SKT40 SET SKF1002 = 1 WHERE SKF981=sOrderCode AND SKF982=sMaterialCode;
            END IF;
        END WHILE;
        -- 关闭子表的游标
        CLOSE cur_2;
        -- 设置子表是否更新成功
        SET sReturn = 0;
    -- 当同步的数据等于0代表数据同步完成，则设置sReturn 返回值为0
    ELSE
        SET sReturn = 0;
    END IF;
    -- 同步成功，设置返回值,依据mReturn以及sReturn的值
    IF mReturn = 0 AND sReturn = 0 THEN
        SET pReturn = 0;
        
        -- 事务提交
        COMMIT;
    ELSE
        -- 回滚事务
        SET pReturn = 1;
        ROLLBACK;
    END IF;
END;