-- =============================================
-- Author:  杨明
-- Create date: 2014-02-26
-- Description:更新7万条数据只要两秒
-- =============================================
--step.1先执行下面的代码
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

--step.2再执行正则函数
create function dbo.regexReplace
(
   @source varchar(5000),    --原字符串
   @regexp varchar(1000),    --正則表換式
   @replace varchar(1000),   --替換值
   @globalReplace bit = 0,   --是否是全局替換
   @ignoreCase bit = 0       --是否忽略大小寫
)
returnS varchar(1000) AS
begin
  declare @hr integer
  declare @objRegExp integer
  declare @result varchar(5000)
  exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
  IF @hr <> 0 begin
     exec @hr = sp_OADestroy @objRegExp
     return null
  end
  exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
  IF @hr <> 0 begin
     exec @hr = sp_OADestroy @objRegExp
     return null
  end
  exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
  IF @hr <> 0 begin
     exec @hr = sp_OADestroy @objRegExp
     return null
  end
  exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
  IF @hr <> 0 begin
     exec @hr = sp_OADestroy @objRegExp
     return null
  end
  exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
  IF @hr <> 0 begin
     exec @hr = sp_OADestroy @objRegExp
     return null
  end
  exec @hr = sp_OADestroy @objRegExp
  IF @hr <> 0 begin
     return null
  end
  return @result
end
GO

--step3.再创建存储过程
Create PROCEDURE UpdateOnlyCode
(
   @OrderGuid Uniqueidentifier,--项目Guid
   @POPRandom varchar(max),--POP随机数
   @DistributorRandom varchar(max),--分销商随机数
   @ShopRandom varchar(max)--贴筒随机数
)
as
begin
    --注--*表示调试代码，勿删
 --更新POP唯一码
 declare @POPMax varchar(max);
 declare @POPMax1 int;
 select @POPMax1=count(AddData) from OrderDetail where OrderGuid=@OrderGuid;
 --print @POPMax1;--*
 set @POPMax=convert(varchar(max),@POPMax1);
 --print @POPMax;--*
 set @POPMax=dbo.regexReplace(@POPMax1,'\d','0',1,0);
 --print @POPMax;--*
 with tempDetail as
 (
    select POPOnlyCode, ROW_NUMBER() over(order by AddData asc) as rownum from OrderDetail where OrderGuid=@OrderGuid
 )update tempDetail set POPOnlyCode='P'+@POPRandom+right(@POPMax+cast(rownum as varchar),len(@POPMax))

 --更新分销商唯一码
 update ShopInfo set DistributorOnlyCode='' where OrderGuid=@OrderGuid;--先清空已有唯一码，防止出错
 declare @DNMax int
 declare @DNPMax varchar(max);
 with tempShopInfo as (select distinct DistributorName from ShopInfo Where OrderGuid=@OrderGuid ) select @DNMax=count(*) from tempShopInfo;
 --select @DNMax;--*
 set @DNPMax=dbo.regexReplace(@DNMax,'\d','0',1,0);
 --select @DNPMax;--*
 create table #tempShopInfoU (rownum int,DistributorName nvarchar(500),DistributorOnlyCode nvarchar(500));
  insert into #tempShopInfoU Select ROW_NUMBER() over(order by AddData asc) as rownum,DistributorName,DistributorOnlyCode From ShopInfo where OrderGuid=@OrderGuid Group By DistributorName,DistributorOnlyCode,AddData Having Count(*)>=1;
  update #tempShopInfoU set DistributorOnlyCode='R'+@DistributorRandom+right(@DNPMax+cast(rownum as varchar),len(@DNPMax));
  --select * from #tempShopInfoU;--*
  update ShopInfo set ShopInfo.DistributorOnlyCode=#tempShopInfoU.DistributorOnlyCode from #tempShopInfoU where ShopInfo.DistributorName=#tempShopInfoU.DistributorName;
 drop table #tempShopInfoU;

 --更新店铺唯一码
 declare @SNNum int,
 @SNMax varchar(max);
 update ShopInfo set ShopOnlyCode='' where OrderGuid=@OrderGuid;--先清空已有唯一码，防止出错
 create table #tempShopOnlyCode(rownum int,ShopName nvarchar(500),DistributorName nvarchar(500),ShopOnlyCode nvarchar(500),noname int);
 insert into #tempShopOnlyCode Select ROW_NUMBER() over(order by ShopName asc) as rownum,ShopName,DistributorName,ShopOnlyCode,Count(*) From ShopInfo  where OrderGuid=@OrderGuid Group By ShopName,DistributorName,ShopName,ShopOnlyCode Having Count(*)>=1;
 select @SNNum=count(*) from #tempShopOnlyCode;
 --select @SNNum;--*
 set @SNMax=dbo.regexReplace(@SNNum,'\d','0',1,0);
 --select @SNMax;--*
 update #tempShopOnlyCode set ShopOnlyCode='S'+@ShopRandom+right(@SNMax+cast(rownum as varchar),len(@SNMax));
 update ShopInfo set ShopInfo.ShopOnlyCode=#tempShopOnlyCode.ShopOnlyCode from #tempShopOnlyCode where ShopInfo.DistributorName=#tempShopOnlyCode.DistributorName and ShopInfo.ShopName=#tempShopOnlyCode.ShopName;
 drop table #tempShopOnlyCode;
end