【例子】表变量与临时表例子

表变量和临时表差不多，但是不会在临时库中存放。

与临时表相比，表变量具有下列优点：

表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。 * 与临时表相比，表变量导致存储过程的重新编译更少。 * 涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。

表变量例子：

declare @t table(id int identity,col varchar(4))
declare @sql1 varchar(60)
declare @i int
declare @n int
set @sql1='吕布赵云马超典韦关羽张飞许褚黄忠颜良文丑庞德孙策'
set @i=1
set @n=2 --定义分段长度

while(@i<len(@sql1))
begin
    insert into @t select substring(@sql1,@i,@n)
    set @i=@i+@n
end

select * from @t

 结果：

 

 临时表例子：

--declare @t table(id int identity,col varchar(4))
create table #t(id int identity,col VARCHAR(4))
declare @sql1 varchar(60)
declare @i int
declare @n int
set @sql1='吕布赵云马超典韦关羽张飞'
set @i=1
set @n=2 --定义分段长度

while(@i<len(@sql1))
begin
    insert into #t select substring(@sql1,@i,@n)
    set @i=@i+@n
end

select * from #t
	  -- 结果：跟表对象结果相同
select * from #t for xml path('')
	  --结果：<id>1</id><col>吕布</col><id>2</id><col>赵云</col><id>3</id><col>马超</col><id>4</id><col>典韦</col><id>5</id><col>关羽</col><id>6</id><col>张飞</col>
select col from #t for xml path('')
	  --结果：<col>吕布</col><col>赵云</col><col>马超</col><col>典韦</col><col>关羽</col><col>张飞</col>
select col+',' from #t for xml path('')
	  --结果：吕布,赵云,马超,典韦,关羽,张飞,

 

 二、取父节点下所有子节点（包括子节点的子节点）

ALTER FUNCTION [dbo].[GetChildID]
( @ParentID int
)
returns @t table(ID int)
as
begin
   insert @t select ID from SYS_RESOURCE where SUP_ID = @ParentID
   while @@rowcount<>0
   begin
      insert @t select a.ID from SYS_RESOURCE a inner join @t b
      on a.SUP_ID =b.id and 
      not exists(select 1 from @t where id=a.id)
   end 
return
end

 

 三、表变量在存储过程中的运用

 

PROC
USE [xb_quotation_dev2]
GO

/****** Object:  StoredProcedure [dbo].[usp_UpdateTodayPriceMange]    Script Date: 02/18/2012 14:34:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_UpdateTodayPriceMange] 
    @dt Ty_PublicTodayPrice READONLY
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ERRORSMALL SMALLINT =0;
    BEGIN TRAN;
        SET @ERRORSMALL=-1;
        DECLARE @dt_Prices TABLE(AreaID INT NOT NULL ,GoodsId INT NOT NULL,GuidePrice DECIMAL(18,4) NOT NULL );
            WITH temp_1 AS(
                SELECT AreaID,GoodsId,GuidePrice FROM @dt
                UNION ALL
                SELECT a.AreaId,t.GoodsId,t.GuidePrice FROM MD_Area a INNER JOIN @dt t
                    ON a.QuotationType= t.AreaID 
                    AND EXISTS(SELECT 0 FROM MD_Goods g WHERE g.TypeId=a.GoodsTypeId AND g.ID= t.GoodsId)
            )
        INSERT INTO @dt_Prices SELECT AreaID,GoodsId,GuidePrice FROM temp_1;
            UPDATE  up SET up.GuidePrice=tp.GuidePrice  FROM  MD_UpdatePrice up INNER JOIN  @dt_Prices tp on up.AreaId=tp.AreaId and up.GoodsId=tp.GoodsId
            AND CONVERT(VARCHAR(10),up.PriceDate,120)=CONVERT(VARCHAR(10),GETDATE(),120)
                IF(@@ERROR<>0) GOTO ERROR_Handler;
        SET @ERRORSMALL=-2;
            INSERT INTO MD_UpdatePrice(AreaID,GoodsId,GuidePrice, PriceDate) 
                SELECT t.*,GETDATE() FROM @dt_Prices t WHERE NOT EXISTS(SELECT 0 FROM MD_UpdatePrice up WHERE up.AreaID=T.AreaID AND up.GoodsId= t.GoodsId)    
                IF(@@ERROR<>0) GOTO ERROR_Handler;
        SET @ERRORSMALL=-3;
            UPDATE h SET h.GuidePrice=t.GuidePrice  FROM  MD_HistoryPrices h,@dt_Prices t WHERE  h.GoodsId = t.GoodsId
                 AND h.AreaID=t.AreaID AND CONVERT(VARCHAR(10),h.PriceDate,120)=CONVERT(VARCHAR(10),GETDATE(),120);
                IF(@@ERROR<>0) GOTO ERROR_Handler;
        SET @ERRORSMALL=-4;
            UPDATE p SET  p.GuidePrice=tt.GuidePrice  FROM MD_Prices p inner join @dt_Prices tt
                 on  P.GoodsId = tt.GoodsId AND
                 p.AreaID =tt.AreaID AND CONVERT(VARCHAR(10),p.PriceDate,120)=CONVERT(VARCHAR(10),GETDATE(),120);
        IF(@@ERROR<>0) GOTO ERROR_Handler;
    COMMIT TRAN;
    RETURN 0;
ERROR_Handler:
    ROLLBACK TRAN;
    RETURN @ERRORSMALL;
END

GO

 

可以看到这上面的存储过程中，定义一个表变量，

DECLARE @dt_Prices TABLE(AreaID INT NOT NULL ,GoodsId INT NOT NULL,GuidePrice DECIMAL(18,4) NOT NULL );

此表变量@dt_Prices 有3列，分别是AreaID,GoodsId,GuidePrice，他们类型分别是整形，整形，双精度，都是非空；

可以看到在下面的功能模块中我分别用到这个表变量@dt_Prices

对于上面的存储过程，我定义了一个表值变量Ty_PublicTodayPrice，创建这个表值变量的语法是：

表值变量
USE [xb_quotation_dev2]
GO

CREATE TYPE [dbo].[Ty_PublicTodayPrice] AS TABLE(
    [AreaID] [int] NOT NULL,
    [GoodsId] [int] NOT NULL,
    [GuidePrice] [decimal](18, 4) NULL
)
GO

 

 

。。