SQL SERVER表变量使用的一个示例

（这是一个虚构的例子,请忽略事务）

首先，我们来看一个简化单据修改的例子，单据数据表结构如下：
1. MOrder（id,remark,createtime）
id：单号
createtime：创建时间
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：单号
pku：商品编号
qty：数量

面对这个简单的需求，我们有第一版的SQL

View Code

update MOrder set remark=@remark
where id=@id
delete from MOrderDetail
where MOrderid=@id
insert into MOrderDetail  values(@id,@pku0,@qty0)
insert into MOrderDetail  values(@id,@pku1,@qty1)
……

针对需求，目前的处理没有什么问题。

演化一个新的需求：
1. MOrder（id,remark,createtime）
id：单号
createtime：创建时间
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：单号
pku：商品编号
qty：数量
remark：备注


某些员工可以在商品上加一个备注，而制单人允许随时修改数量，或调整商品（增删）
此时，我们不能通过先delete，然后再insert的方式插入MOrderDetail数据，因为那会删掉别人添加的东西
（假设允许制单人连商品带注释一起删掉，但不能只删注释）
那么制单人在修改单据后，就隐含了几种变动：
1. 新增了一些商品
2. 修改了一些商品
3. 删除了一些商品

一个直接SQL实现：

--存在则更新，不存在则插入
If  exists(select  1 from MOrder with(nolock) where MOrderid=@id and pku=@pku0)
         update MOrderDetail set  qty=@qty0
            where MOrderid=@id and pku=@pku0
Else
         insert into MOrderDetail  values(@id,@pku0,@qty0)
……

--删除
Delete from MOrderDetail where MOrderid=@id and pku not in(@pku0,@pku1……);

比如有100个以上的商品，我们的sql代码量就很可观了，而且商品越多效率将越低下。

我们来用表变量优化一下实现：

View Code

declare @tb table
(
   pku int,
       qty    int
)
Insert into @tb values(@pku0,@qty0)
Insert into @tb values(@pku1,@qty1)
……

Update a set qty=b.qty
From MOrderDetail as a
inner join @tb as b on a.pku=b.pku
Where a.MOrderid=@id


Delete from MOrderDetail
Where pku not in(select pku from @tb) and MOrderid=@id


Insert into MOrderDetail
Select @id,a.pku,b.qty from @tb
Where pku not in
(select pku from MOrderDetail where MOrderid=@id)

首先，我们将要更新的数据插入了表变量@tb

然后：

先关联更新数据数据表已存在的数据，

删除@tb的不存在商品

再将新增的商品插入数据表。

表变量的使用方式，与临时表类似，但是不需要你显式删除临时表。

表变量是在内存中，临时表是物理表。再使用的时候，视情况而定，表变量处理小量数据更好，但是任务多、数据量大的操作对内存是个考验。

经过反复测试，表变量做这种批量更新效率更好。

ozdoo技术系列文章 转载请署名