最近写的一个使用了表变量参数的存储过程

最新推荐文章于 2023-09-20 20:38:38 发布

为什么没有人加

最新推荐文章于 2023-09-20 20:38:38 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/hffyyff/article/details/84039443

版权

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

alter proc SP_DISPLAY_PART_RATIO_BY_MODEL_NO(  @drive varchar(20),  @epullid varchar(30),  @functionname varchar(50),  @invLoc varchar(20),  @server varchar(20),  @sqlmodel varchar(max))as  declare @ModelList MCS_ModelList   declare  @sch_id varchar(12)   declare  @revision numeric(18, 0)   declare  @trans_id varchar(20)   declare  @model varchar(20)   declare  @start_date datetime  declare  @end_date datetime  declare  @line varchar(30)   declare  @qty numeric(18, 0)   declare  @shift_qty bigint   declare  @family varchar(20)    CREATE TABLE #t(    [sch_id] [varchar](12) NOT NULL,    [revision] [numeric](18, 0) NOT NULL,    [trans_id] [varchar](12) NOT NULL,    [model] [varchar](20) NOT NULL,    [start_date] [datetime] NOT NULL,    [end_date] [datetime] NOT NULL,    [line] [varchar](30) NOT NULL,    [qty] [numeric](18, 0) NOT NULL,    [shift_qty] [bigint] NOT NULL,    [family] [varchar](20) NULL  )  insert into #t  exec(@sqlmodel)    declare cur_model_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for      select   [model]   from #t    open cur_model_sql    fetch next from cur_model_sql into @model     while @@fetch_status=0         begin         select top 1 @trans_id = trans_id from MC_EPULL_MODEL_HOURLY(nolock) where EPULL_ID = @epullid and MODEL_NO = @model       if(LEN(@trans_id)>0)       begin       insert into @ModelList([model],[prime],[item_id],       [item_nr],[item_desc],[family],[class],[component_quantity],[component_yield_factor],[component_sequence_id])        exec('select '''+@model+''' as model,a.* from '+@functionname+'('''+@model+''') a')     end     fetch next from cur_model_sql into @model     end     deallocate cur_model_sql     create table #tempMainPart(      [model] [varchar](20) NOT NULL,      [trans_id] [varchar](10) NOT NULL,      [new_item] [int] NOT NULL,      [prime] [varchar](20) NULL,      [item_nr] [varchar](20) NULL,      [family] [varchar](20) NULL,      [item_desc] [varchar](80) NULL,      [component_yield_factor] [tinyint] NULL,      [component_sequence_id] [int] NULL,      [item_class] [varchar](10) NULL,      [component_quantity] [tinyint] NULL,      [requested_per] [int] NOT NULL,      [required_qty] [int] NULL,      [plan_qty] [int] NOT NULL,      [part_available] [int] NULL,      [item_type] [varchar](1) NOT NULL  )    create table #tempMainPartGroup(      [model] [varchar](20) NOT NULL,      [trans_id] [varchar](10) NOT NULL,      [new_item] [int] NOT NULL,      [prime] [varchar](20) NULL,      [item_nr] [varchar](20) NULL,      [family] [varchar](20) NULL,      [item_desc] [varchar](80) NULL,      [item_class] [varchar](10) NULL,      [item_type] [varchar](1) NOT NULL,      [count] int  )    create table #tempPrimaryAlternatePart(       orig_part_no varchar(20),       alt_part_no varchar(20),       Part_Ratio numeric(18,0)  )      declare cur_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for      select [sch_id],[revision],  [model],[start_date],  [end_date],[line],[qty],[shift_qty],[family]   from #t    open cur_sql    fetch next from cur_sql into @sch_id ,@revision ,                     @model ,                     @start_date,@end_date ,                     @line ,@qty ,@shift_qty ,                     @family      while @@fetch_status=0         begin         select top 1 @trans_id = trans_id from MC_EPULL_MODEL_HOURLY(nolock) where EPULL_ID = @epullid and MODEL_NO = @model       if(LEN(@trans_id)>0)       begin       insert into #tempMainPart        exec SP_DISPLAY_MAIN_PART_BY_MODEL_NO @drive,@model,@epullid,@trans_id,@tab = @ModelList     end     fetch next from cur_sql into @sch_id ,@revision ,                   @model ,@start_date,                   @end_date ,@line ,                   @qty ,@shift_qty ,                   @family      end     deallocate cur_sql           insert into #tempMainPartGroup     select        [model]      ,[trans_id]      ,[new_item]      ,[prime]      ,[item_nr]      ,[family]      ,[item_desc]      ,[item_class]      ,[item_type]      ,COUNT(*) as [count]      from #tempMainPart       group by       [model]      ,[trans_id]      ,[new_item]      ,[prime]      ,[item_nr]      ,[family]      ,[item_desc]      ,[item_class]      ,[item_type]    declare  @new_item int    declare  @prime    varchar(20)     declare  @item_nr  varchar(20)     declare  @item_desc varchar(80)    declare  @item_class varchar(10)     declare  @item_type varchar(1)       declare cur_ratio_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for       select        [model]                     ,[trans_id]      ,[new_item]      ,[prime]      ,[item_nr]      ,[family]      ,[item_desc]      ,[item_class]      ,[item_type]      from #tempMainPartGroup      open cur_ratio_sql       fetch next from cur_ratio_sql into @model,                                         @trans_id,                                         @new_item,                                          @prime,                                             @item_nr,                                           @family,                                          @item_desc,                                         @item_class,                                         @item_type   while @@fetch_status=0         begin           insert into #tempPrimaryAlternatePart           exec SP_GET_PRIMARY_ALTERNATE_Part @item_nr,@model,@epullid,@trans_id,@tab = @ModelList           fetch next from cur_ratio_sql into @model,                                              @trans_id,                                              @new_item,                                               @prime,                                                  @item_nr,                                                @family,                                               @item_desc,                                              @item_class,                                              @item_type      end                      deallocate cur_ratio_sql       select distinct part.item_nr as PrimaryNo,          isnull(partratio.alt_part_no,part.item_nr) as PartNo,         part.item_desc,         part.family,         ISNULL(partratio.Part_Ratio,0) as part_ratio    into #tempPrimaryAlternatePartRatio    from #tempMainPartGroup part    left join #tempPrimaryAlternatePart partratio    on part.item_nr = partratio.orig_part_no    declare @fiscalYear varchar(10)    declare @quarter varchar(4)    declare @startTime datetime    declare @endTime datetime    set @fiscalYear = (select FISCAL_YEAR  from MCS_FISCAL_YEAR(nolock)         where START_DATE<=convert(varchar(10),GETDATE(),10)           and END_DATE>=convert(varchar(10),GETDATE(),10))        set @quarter = (select QUARTER  from MCS_FISCAL_YEAR(nolock)         where START_DATE<=convert(varchar(10),GETDATE(),10)           and END_DATE>=convert(varchar(10),GETDATE(),10))        set @startTime = (SELECT [START_DATE] FROM dbo.MCS_FISCAL_YEAR(nolock)         where START_DATE<=convert(varchar(10),GETDATE(),10) and END_DATE>=convert(varchar(10),GETDATE(),10))            set @endTime = (SELECT [End_DATE] FROM dbo.MCS_FISCAL_YEAR(nolock)         where START_DATE<=convert(varchar(10),GETDATE(),10) and END_DATE>=convert(varchar(10),GETDATE(),10))    create table #tempPartAchivedQuantity(      part_item_no VARCHAR(20),          achivedQuantity int  )    create table #tempPartAchivedRatio(      part_item_no VARCHAR(20),          partratio int  )    declare @PartNo varchar(20)  declare cur_part_sql cursor for    select PartNo         from #tempPrimaryAlternatePartRatio         open cur_part_sql         fetch next from cur_part_sql into @PartNo     while @@fetch_status=0         begin        insert into #tempPartAchivedQuantity(part_item_no,achivedQuantity)        select * from DISPLAY_PART_ACHIVED_QUANTITY(@PartNo,@startTime,@endTime)        fetch next from cur_part_sql into @PartNo     end     deallocate cur_part_sql         select 0 as RowNo,         (case when PrimaryNo = PartNo then 'O' else 'E' end) as RowInd,           PartRatio.*,       ISNULL(AchivedQuantity.achivedQuantity, 0) as achivedQuantity,       ISNULL(AchivedRatio.partratio, 0) as partratio       into #tempPartAll    from #tempPrimaryAlternatePartRatio PartRatio    left join #tempPartAchivedQuantity AchivedQuantity    on PartRatio.PartNo = AchivedQuantity.part_item_no    left join #tempPartAchivedRatio  AchivedRatio      on PartRatio.PartNo = AchivedRatio.part_item_no    order by PrimaryNo asc,RowInd desc      declare @RowNo int    declare @RowInd varchar(1)    declare @PrimaryNo varchar(20)    declare @part_ratio int    declare @achivedQuantity int    declare @partratio int        CREATE TABLE #tempVendor(    PrimaryNo varchar(20),    [item_id] [int] ,    [item_nr] [varchar](20) ,    [vendor_code] [varchar](20) ,    [inv_loc] [varchar](20) ,    [qty] [bigint] ,    [update_datetime] [datetime] ,    [item_desc] [varchar](50) ,    [va_std_pack_l2] [bigint]       )        declare cur_partall_sql cursor LOCAL FORWARD_ONLY READ_ONLY STATIC for    select *         from #tempPartAll         open cur_partall_sql       fetch next from cur_partall_sql into @RowNo,@RowInd,@PrimaryNo,                                          @PartNo,@item_desc,@family,@part_ratio,                                          @achivedQuantity,@partratio    while @@fetch_status=0        begin     insert into #tempVendor     exec SP_DISPLAY_PARTVENDOR @PrimaryNo,@drive,@PartNo,@InvLoc,@server     fetch next from cur_partall_sql into @RowNo,@RowInd,@PrimaryNo,                                          @PartNo,@item_desc,@family,@part_ratio,                                          @achivedQuantity,@partratio    end    deallocate cur_partall_sql         select distinct a.RowNo,       a.RowInd,       a.PrimaryNo,       a.PartNo,       a.item_desc,       a.family,       a.part_ratio,       a.achivedQuantity,       a.partratio,       ISNULL(a.partratio, 0) - ISNULL(a.part_ratio, 0) as partdelta,       b.inv_loc as boh,       b.vendor_code,       b.qty as [@7am],       b.update_datetime,       b.va_std_pack_l2       into #tempPartVendor    from #tempPartAll a    left join #tempVendor b    on a.PrimaryNo = b.PrimaryNo     and a.PartNo = b.item_nr;         WITH CTE AS (SELECT RowNo,      ROW_NUMBER() OVER(      PARTITION BY PartNo,RowInd    order by PrimaryNo,RowInd ,boh ) - 1 AS x_new      FROM #tempPartVendor)      UPDATE CTE      SET RowNo = x_new          select * into #tempMainPartVendor from #tempPartVendor k where k.PrimaryNo = k.PartNo    select a.*,       isnull(dbo.Func_Get_TargetRaio(a.PartNo, a.Vendor_Code, @quarter, @fiscalYear),0) as Ratio,       isnull(dbo.Func_Get_BuyQty(a.PartNo, a.Vendor_Code,@startTime,@endTime),0) as buyerqty,       ISNULL(dbo.Func_Get_AchievedPercent(a.PartNo, a.Vendor_Code,@startTime,@endTime),0) as AchievedPercent    from #tempPartVendor a     where a.PrimaryNo in (select PrimaryNo from #tempMainPartVendor)       order by a.PrimaryNo asc,RowInd desc,boh asc

--=======================================下面是call 这个存储过程================================================================

declare @sql varchar(max)set @sql ='SELECT TBL2.*, TBL3.Family as Family' +'  FROM (SELECT SCH_ID, REVISION' +'          FROM MCS_PC_SCHEDULE_MASTER WITH(NOLOCK)' +'         WHERE CATEGORY = ''DRIVE''' +'           AND PLAN_BUILDING = ''B1''' +'           AND PLAN_LEVEL = ''L2''' +'           AND PUBLISH_FLAG = ''Y''' +'           AND CONVERT(VARCHAR(10), ENTRY_DATE, 103) =' +'               CONVERT(VARCHAR(10), ''01/12/2012 07:00:00'', 103)) TBL1' +' INNER JOIN (SELECT SCH_ID,' +'                    REVISION,' +'                    TRANS_ID,' +'                    MODEL,' +'                    START_DATE,' +'                    END_DATE,' +'                    LINE,' +'                    QTY,' +'                    IsNULL((SELECT SUM(PLAN_QTY)' +'                             FROM MC_EPULL_MODEL_HOURLY WITH(NOLOCK)' +'                            WHERE EPULL_ID = ''L2NPLHDA-121201-000''' +'                              AND MODEL_NO = A.MODEL' +'                              and TRANS_ID = A.TRANS_ID' +'                              AND REVISION = 0' +'                              AND CONVERT(DATETIME, PLAN_DATE, 103) <=' +'                                  CONVERT(DATETIME,' +'                                          ''01/12/2012 18:59:59'',' +'                                          103)' +'                              AND CONVERT(DATETIME, PLAN_DATE, 103) >=' +'                                  CONVERT(DATETIME,' +'                                          ''01/12/2012 07:00:00'',' +'                                          103)),' +'                           0) SHIFT_QTY' +'               FROM MCS_PC_SCHEDULE_TRANSMAIN A WITH(NOLOCK)' +'              WHERE PUBLISH_FLAG = ''Y''' +'                AND CONVERT(DATETIME, START_DATE, 103) <=' +'                    CONVERT(DATETIME, ''01/12/2012 18:59:59'', 103)' +'                AND CONVERT(DATETIME, END_DATE, 103) >=' +'                    CONVERT(DATETIME, ''01/12/2012 07:00:00'', 103)) TBL2' +'    on TBL1.SCH_ID = TBL2.SCH_ID' +'   AND TBL1.REVISION = TBL2.REVISION' +'  Left outer join MCS_ITEM TBL3 WITH(NOLOCK)' +'    on TBL3.Item_Nr = TBL2.MODEL' +' ORDER BY LINE, END_DATE, TBL2.MODEL'exec SP_DISPLAY_PART_RATIO_BY_MODEL_NO 'DRIVE','L2NPLHDA-121201-000','DISPLAY_DRV_CLEANROOM_BOM_BY_MODEL','INV_LOC_STKMAIN','ORACLE_SQL',@sql

在使用这个存储过程之前,没有使用表变量,每次在游标内部都要select一个表值函数,而有两个游标都需要用到这个表值函数,那么重复的查询导致了严重的性能问题.

而现在用sql server 2008里的新技术,表变量参数解决了这个问题,先把表值函数的结果放进一个表变量里,然后在游标循环的时候就可以直间使用表变量了,而不用再

查询一次表值函数. 在没有使用表变量为参数时,运行时间为1分34秒,在使用后降为10秒.性能的提升是非常明显的!

注意,在使用表变量参数前,要先定义一个用户自定义数据类型的表结构

CREATE TYPE [dbo].[MCS_ModelList] AS TABLE(  [model] [varchar](20) NULL,  [prime] [varchar](20) NULL,  [item_id] [varchar](20) NULL,  [item_nr] [varchar](20) NULL,  [item_desc] [varchar](80) NULL,  [family] [varchar](20) NULL,  [class] [varchar](10) NULL,  [component_quantity] [tinyint] NULL,  [component_yield_factor] [tinyint] NULL,  [component_sequence_id] [int] NULL)