DELIMITER $$CREATE DEFINER=`sa`@`%` PROCEDURE `UpdateProvinceSalePrice`()    MODIFIES SQL DATA    SQL SECURITY INVOKERBEGIN-- 需要定义接收游标数据的变量   DECLARE priceid int(11);  DECLARE pricecode varchar(36);-- PricingCategory.code  DECLARE pricecorpid varchar(36);  DECLARE pricecorpcode varchar(50);  DECLARE pricecustomertypeid int(11);  DECLARE pricegrouptype smallint(6);  DECLARE pricegroupcode varchar(50);  DECLARE pricemethod smallint(6);  DECLARE priceIsForced smallint(6);  DECLARE priceSalePrice decimal(11);  DECLARE pricePrecision int(11);  DECLARE priceRetentionway smallint(6);  DECLARE pricestatus smallint(6);  DECLARE pricecheckprice INT;  DECLARE MapSalePrice varchar(30);  DECLARE MapCategoryCode varchar(30);  DECLARE SalePriceFormat varchar(150);  DECLARE PinGroupCode varchar(30);  DECLARE PinCode varchar(30);  DECLARE CompanyName varchar(30);  DECLARE ExistProductId varchar(38);  DECLARE ExistProductCode varchar(38);  -- 遍历数据结束标志  DECLARE done bool DEFAULT false;  -- 游标  DECLARE cur CURSOR FOR SELECT id,Code, corpid,corpcode,customertypeid, grouptype,groupcode,pricemethods,IsForced,SalePrice,`Precision`,`Retentionway`,`status` FROM ProvincePricingCategory category  join ProvincePricingCategory_SYNC categorysync on category.Id=BillId   join schedulelog log on categorysync.SyncNum> log.syncnum where log.tagbillname='ProvincePricingCategory' and !category.IsForced and id=19474;  -- 将结束标志绑定到游标  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 打开游标  OPEN cur;  -- 开始循环  read_loop: LOOP    -- 提取游标里的数据，这里只有一个，多个的话也一样；    FETCH cur INTO priceid,pricecode, pricecorpid,pricecorpcode,pricecustomertypeid,pricegrouptype,pricegroupcode,pricemethod,priceIsForced,priceSalePrice,pricePrecision,priceRetentionway,pricestatus;    -- 声明结束的时候    IF done THEN      LEAVE read_loop;    END IF;    -- 这里做你想做的循环的事件--     SET SQL_SAFE_UPDATES = 0;     set CompanyName= concat('P',pricecorpcode);     set @pricecode=pricecode; -- 动态拼sql     select  salepricefieldmap.PartsSalePrice,salepricefieldmap.PriceCategoryCode from customertype      join salepricefieldmap on  customertype.TypeCode =salepricefieldmap.TypeCode where customertype.Id=pricecustomertypeid into MapSalePrice,MapCategoryCode;         select  SalePriceFormat(pricemethod,pricePrecision,priceRetentionway,priceSalePrice) into SalePriceFormat; -- 获得销售价计算公式     select pricemethod,pricePrecision,priceRetentionway,priceSalePrice,SalePriceFormat;     CREATE TEMPORARY TABLE if not exists  PartsSalePrice_TMP(         ProductId varchar(36),         ProductCode varchar(36)     ) ENGINE = MEMORY;     CREATE TEMPORARY TABLE if not exists  sparepart_tmp(         ProductId varchar(36),         ProductCode varchar(36),         SupplierCode varchar(36)     ) ENGINE = MEMORY;    if(pricegrouptype=3) then       select part.ProductId from sparepart part       inner join partsgroup fz on fz.groupcode = part.groupcode  and fz.category=1       inner join partsgroup pp on pp.parentcode = fz.code and pp.groupcode = part.groupcode and pp.category=2       join ProvincePricingCategory category on pp.code =category.Groupcode and category.grouptype=2 and category.corpid = pricecorpid and category.status=2       where  part.ProductCode =pricegroupcode and category.IsForced=1 limit 1 into ExistProductId;       if ExistProductId is null        THEN        set ExistProductCode=null;       select  pgroup.code from sparepart part       join PartsGroup pgroup on part.groupcode=pgroup.groupcode and pgroup.category=1       where part.ProductCode= pricegroupcode  into ExistProductCode;              select * from  ProvincePricingCategory category        join PartsGroup pgroup on category.groupcode=pgroup.code  and category.corpid = pricecorpid and category.status=2       where  FIND_IN_SET(pgroup.code, GetAncestry(ExistProductCode))  and category.grouptype=1 and  category.corpid = pricecorpid limit 1 into ExistProductId ;          if(ExistProductId is null)THEN              insert into sparepart_tmp             SELECT  part.ProductId,  part.ProductCode,cost.SupplierCode FROM  sparepart part              join enterprisepartscost cost on cost.productcode=part.productcode  where part.productcode=pricegroupcode;             set @insertsql= concat('INSERT INTO PartsSalePrice_TMP             select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode              WHERE price.productcode is null');                   prepare stmpinsert from @insertsql;             execute stmpinsert;             if(exists(select 1 from PartsSalePrice_TMP)) then             set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')             select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost             on  part.Productcode=cost.Productcode;');             prepare stmp from @SalePricesql;             execute stmp using @pricecode;          end if;              set @SalePricesql=concat('update ',CompanyName,' price join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode                                  set price.',MapSalePrice,'=',SalePriceFormat,',' ,MapCategoryCode,'= ? ',' where price.productcode =cost.productcode and price.productcode=',pricegroupcode);              prepare stmp from @SalePricesql;              execute stmp using @pricecode;            END IF;              END IF;    end if;    if(pricegrouptype=2) then       select  fz.code from partsgroup fz       join PartsGroup pgroup on pgroup.parentcode=fz.code and pgroup.category=2       where pgroup.code= pricegroupcode  into ExistProductCode;              select  part.ProductId from partsgroup fz       join ProvincePricingCategory category on fz.code=category.Groupcode and category.grouptype=2 and category.corpid = pricecorpid        where   FIND_IN_SET(fz.code, GetAncestry(ExistProductCode)) and category.IsForced=1  limit 1 into ExistProductCode;          if (ExistProductId is null) THEN          insert into sparepart_tmp         SELECT  part.ProductId,  part.ProductCode,cost.SupplierCode   FROM  sparepart part                        join PartsGroup parentgroup  on parentgroup.groupcode=part.groupcode and parentgroup.category=1                       join PartsGroup childrengroup on childrengroup.ParentCode=parentgroup.code and childrengroup.groupcode = part.SpareBrandCode and childrengroup.category=2                       left join ProvincePricingCategory category on part.productcode=category.groupcode and category.grouptype=3 and category.corpid = pricecorpid  and category.IsForced=1 and status=2                       join enterprisepartscost cost on cost.productcode=part.productcode                       where category.code is null and childrengroup.groupcode=pricegroupcode;        set @insertsql= concat('INSERT INTO PartsSalePrice_TMP             select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode              WHERE price.productcode is null');                   prepare stmpinsert from @insertsql;             execute stmpinsert;           if(exists(select 1 from PartsSalePrice_TMP)) then            set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')            select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost            on  part.Productcode=cost.Productcode;');            prepare stmp from @SalePricesql;            execute stmp using @pricecode;        end if;                set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp  part on part.ProductCode =price.productcode and cost.suppliercode=price.suppliercode                        set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' , MapCategoryCode,'= ? ');              prepare stmp from @SalePricesql;              execute stmp using @pricecode;            END IF;         end if;    if(pricegrouptype=1) then    select pgroup.code from PartsGroup pgroup    join ProvincePricingCategory category on pgroup.code=category.Groupcode and category.grouptype=1 and category.corpid = pricecorpid  and status=2    where FIND_IN_SET(pgroup.code, GetAncestry(pricegroupcode)) and category.IsForced limit 1 into ExistProductId;    if ExistProductId is null  THEN          insert into sparepart_tmp         select distinct fenzu.ProductId,fenzu.ProductCode,fenzu.SupplierCode from           (select distinct part.ProductId,part.ProductCode, b.SupplierCode from PartsGroup pgroup          left join ProvincePricingCategory category on pgroup.code=category.GroupCode and category.grouptype=1 and category.corpid = pricecorpid  and category.IsForced=1 and category.status=2 and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode))          join sparepart part on  pgroup.groupcode=part.groupcode          join EnterprisePartsCost b on part.productcode = b.productcode         where category.GroupCode is null and pgroup.category=1)fenzu         join(         select distinct part.ProductId ,part.ProductCode, b.SupplierCode from PartsGroup pgroup          join PartsGroup cgroup on pgroup.code=cgroup.parentcode and  FIND_IN_SET(cgroup.parentcode, queryChildrenGroup(pricegroupcode))  and cgroup.category=2          left join ProvincePricingCategory category on cgroup.code=category.GroupCode and category.grouptype=2  and category.corpid = pricecorpid  and category.IsForced=1 and category.status=2         join sparepart part on  cgroup.groupcode=part.SpareBrandCode  and part.groupcode = pgroup.groupcode         join EnterprisePartsCost b on part.productcode = b.productcode         where  category.GroupCode is null) pinpai         on fenzu.ProductId=pinpai.ProductId         join (         select distinct part.ProductId,part.ProductCode ,b.SupplierCode from PartsGroup pgroup          join sparepart part on  pgroup.groupcode=part.groupcode         left join ProvincePricingCategory category on part.productcode=category.GroupCode and category.grouptype=3 and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode))  and category.corpid = pricecorpid  and category.IsForced=1 and category.status=2         join EnterprisePartsCost b on part.productcode = b.productcode         where  category.GroupCode is null)peijian         on fenzu.ProductId=peijian.ProductId;         set @insertsql= concat('INSERT INTO PartsSalePrice_TMP             select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode              WHERE price.productcode is null');              prepare stmpinsert from @insertsql;             execute stmpinsert;         if(exists(select 1 from PartsSalePrice_TMP)) then          set @SalePriceInsertsql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')          select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost          on  part.Productcode=cost.Productcode;');              prepare stmpin from @SalePriceInsertsql;              execute stmpin using @pricecode;         end if;         set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode         join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode          set price.',MapSalePrice,'=',SalePriceFormat, ',' ,MapCategoryCode,'= ? ');         prepare stmp from @SalePricesql;         execute stmp using @pricecode;   end if;   END IF;          drop table PartsSalePrice_TMP;       drop table sparepart_tmp;  END LOOP;   -- 关闭游标  CLOSE cur;END;$$use dcs;call UpdateProvinceSalePrice()call UpdateSalePrice()INSERT INTO PartsSalePrice_TMP            select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join P1001 price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode            WHERE price.productcode is nullINSERT INTO PartsSalePrice_TMP            select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join P1001 price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode             WHERE price.productcode is null CREATE  TABLE if not exists  PartsSalePrice_TMP(         ProductId varchar(36),         ProductCode varchar(36)     ) ENGINE = MEMORY;use dcs;DELIMITER $$select * from p1001CREATE DEFINER=`sa`@`%` PROCEDURE `UpdateSalePrice`()    MODIFIES SQL DATA    SQL SECURITY INVOKERBEGIN-- 需要定义接收游标数据的变量   DECLARE priceid int(11);-- PricingCategory.id  DECLARE pricecode varchar(36);-- PricingCategory.code  DECLARE pricecorpid varchar(36);-- PricingCategory.corpid  DECLARE pricecorpcode varchar(50);-- PricingCategory.corpcode  DECLARE pricecustomertypeid int(11);-- PricingCategory.customertypeid  DECLARE pricegrouptype smallint(6);-- PricingCategory.grouptype  DECLARE pricegroupcode varchar(50);-- PricingCategory.groupcode  DECLARE pricemethod smallint(6);-- PricingCategory.pricemethods  DECLARE priceIsForced smallint(6);-- PricingCategory.IsForced  DECLARE priceSalePrice decimal(11);-- PricingCategory.SalePrice  DECLARE pricePrecision int(11);-- PricingCategory.Precision  DECLARE priceRetentionway smallint(6);-- PricingCategory.Retentionway  DECLARE pricestatus smallint(6);-- PricingCategory.status  DECLARE SalePriceFormat varchar(150);-- SalePriceFormat()  DECLARE MapSalePrice varchar(30);--  salepricefieldmap.PartsSalePrice  DECLARE MapCategoryCode varchar(30);-- salepricefieldmap.PriceCategoryCode  DECLARE CompanyName varchar(30);-- 'p'+PricingCategory.corpcode  -- 遍历数据结束标志  DECLARE done bool DEFAULT false;  -- 游标  DECLARE cur CURSOR FOR SELECT id,Code, corpid,corpcode,customertypeid,grouptype,groupcode,pricemethods,IsForced,category.SalePrice,`Precision`,`Retentionway`,`status` FROM PricingCategory category  join PricingCategory_SYNC categorysync on category.Id=BillId   join schedulelog log on categorysync.SyncNum> log.syncnum where log.tagbillname='PricingCategory' ;    -- 将结束标志绑定到游标  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 打开游标  OPEN cur;  -- 开始循环  read_loop: LOOP    -- 提取游标里的数据，这里只有一个，多个的话也一样；    FETCH cur INTO priceid,pricecode, pricecorpid,pricecorpcode,pricecustomertypeid,pricegrouptype,pricegroupcode,pricemethod,priceIsForced,priceSalePrice,pricePrecision,priceRetentionway,pricestatus;    -- 声明结束的时候    IF done THEN      LEAVE read_loop;    END IF;    -- 这里做你想做的循环的事件--     SET SQL_SAFE_UPDATES = 0;     set CompanyName= 'PartsSalePrice'; -- 需要更新的销售价表     select  salepricefieldmap.PartsSalePrice,salepricefieldmap.PriceCategoryCode from customertype      join salepricefieldmap on  customertype.TypeCode =salepricefieldmap.TypeCode where customertype.Id=pricecustomertypeid into MapSalePrice,MapCategoryCode; -- 获得需要更新的销售价具体列      select  SalePriceFormat(pricemethod,pricePrecision,priceRetentionway,priceSalePrice) into SalePriceFormat; -- 获得销售价计算公式     set @pricecode=pricecode; -- 动态拼sql          CREATE TEMPORARY TABLE if not exists  PartsSalePrice_TMP(         ProductId varchar(50),         ProductCode varchar(36)     ) ENGINE = MEMORY;  -- 需要新增的销售价临时表     CREATE TEMPORARY TABLE if not exists  sparepart_tmp(         ProductId varchar(50),         ProductCode varchar(36),         SupplierCode varchar(36)     ) ENGINE = MEMORY; -- 需要更新的动态备件表         if(pricegrouptype=3) then -- 定价原则为备件         insert into sparepart_tmp SELECT  part.ProductId,  part.ProductCode ,cost.SupplierCode FROM  sparepart part join enterprisepartscost cost on cost.productcode=part.productcode where part.productcode=1150;-- 需要更新的 配件临时表                  INSERT INTO PartsSalePrice_TMP  -- 需要新增的销售价临时表         select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join  PartsSalePrice  price on part.ProductCode =price.productcode  and  part.SupplierCode =price.SupplierCode  WHERE price.productcode is null;                        if(exists(select 1 from PartsSalePrice_TMP)) then -- 新增销售价            set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')            select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost            on  part.Productcode=cost.Productcode;');            prepare stmp from @SalePricesql;            execute stmp using @pricecode;         end if;         -- Update 销售价         set @SalePricesql=concat('update ',CompanyName,' price join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode                                  set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' ,MapCategoryCode,'= ?  where price.productcode =cost.productcode and price.productcode=',pricegroupcode);         prepare stmp from @SalePricesql;         execute stmp  using @pricecode;     end if;    if(pricegrouptype=2) then         insert into sparepart_tmp -- 需要新增的销售价临时表         SELECT  part.ProductId,  part.ProductCode ,cost.SupplierCode FROM  sparepart part          join PartsGroup parentgroup  on parentgroup.groupcode=part.groupcode and parentgroup.category=1         join PartsGroup childrengroup on childrengroup.ParentCode=parentgroup.code and childrengroup.groupcode = part.SpareBrandCode and childrengroup.category=2         left join PricingCategory category on part.productcode=category.groupcode and category.grouptype=3         join enterprisepartscost cost on cost.productcode=part.productcode         where category.code is null and childrengroup.groupcode=pricegroupcode;                  INSERT INTO PartsSalePrice_TMP-- 需要新增的销售价临时表         select part.ProductCode,part.ProductId from  sparepart_tmp part  LEFT join  PartsSalePrice  price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode          WHERE price.productcode is null;                        if(exists(select 1 from PartsSalePrice_TMP)) then-- 新增销售价            set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')            select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost            on  part.Productcode=cost.Productcode;');            prepare stmp from @SalePricesql;            execute stmp using @pricecode;        end if;        -- Update 销售价        set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp  part on part.ProductCode =price.productcode  join enterprisepartscost on cost.suppliercode=price.suppliercode                        set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' , MapCategoryCode,'= ? ');            prepare stmp from @SalePricesql;            execute stmp using @pricecode;            end if;    if(pricegrouptype=1) then         insert into sparepart_tmp(ProductId,ProductCode,SupplierCode) -- 需要新增的销售价临时表         select distinct fenzu.ProductId,fenzu.ProductCode,fenzu.SupplierCode from           (select distinct part.ProductId,part.ProductCode ,b.SupplierCode from PartsGroup pgroup          left join PricingCategory category on pgroup.code=category.GroupCode and  category.grouptype=1  and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode))          join sparepart part on  pgroup.groupcode=part.groupcode          join EnterprisePartsCost b on part.productcode = b.productcode         where category.GroupCode is null and pgroup.category=1)fenzu         join(         select distinct part.ProductId ,part.ProductCode  ,b.SupplierCode from PartsGroup pgroup          join PartsGroup cgroup on pgroup.code=cgroup.parentcode and  FIND_IN_SET(cgroup.parentcode, queryChildrenGroup(pricegroupcode))  and cgroup.category=2         left join PricingCategory category on cgroup.code=category.GroupCode and category.grouptype=2          join sparepart part on  cgroup.groupcode=part.SpareBrandCode  and part.groupcode = pgroup.groupcode         join EnterprisePartsCost b on part.productcode = b.productcode         where  category.GroupCode is null) pinpai         on fenzu.ProductId=pinpai.ProductId         join (         select distinct part.ProductId,part.ProductCode ,b.SupplierCode  from PartsGroup pgroup          join sparepart part on  pgroup.groupcode=part.groupcode         left join PricingCategory category on part.productcode=category.GroupCode and category.grouptype=3 and FIND_IN_SET(pgroup.parentcode, queryChildrenGroup(pricegroupcode))          join EnterprisePartsCost b on part.productcode = b.productcode         where  category.GroupCode is null )peijian         on fenzu.ProductId=peijian.ProductId;         -- 需要新增的销售价临时表         INSERT INTO PartsSalePrice_TMP         select part.ProductId ,part.ProductCode from  sparepart_tmp part  LEFT join  PartsSalePrice  price on part.ProductCode =price.productcode and  part.SupplierCode =price.SupplierCode          WHERE price.productcode is null and;            -- 新增销售价         if(exists(select 1 from PartsSalePrice_TMP)) then          set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')            select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join  enterprisepartscost cost            on  part.Productcode=cost.Productcode;');            prepare stmp from @SalePricesql;            execute stmp using @pricecode;         end if;         -- Update 销售价         set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode         join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode          set price.',MapSalePrice,'=',SalePriceFormat, ',' ,MapCategoryCode,'= ?');         prepare stmp from @SalePricesql;         execute stmp using @pricecode;   end if;     drop table PartsSalePrice_TMP;     drop table sparepart_tmp;  END LOOP;  -- 关闭游标  CLOSE cur;END;$$use security