程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL SERVER表變量使用的一個示例

SQL SERVER表變量使用的一個示例

編輯:關於SqlServer

(這是一個虛構的例子,請忽略事務)

首先,我們來看一個簡化單據修改的例子,單據數據表結構如下:
1. MOrder(id,remark,createtime)
id:單號
createtime:創建時間
2. MOrderDetail (MOrderid,pku,qty)
MOrderid:單號
pku:商品編號
qty:數量

面對這個簡單的需求,我們有第一版的SQL

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代碼量就很可觀了,而且商品越多效率將越低下。

我們來用表變量優化一下實現:

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的不存在商品

再將新增的商品插入數據表。


表變量的使用方式,與臨時表類似,但是不需要你顯式刪除臨時表。

表變量是在內存中,臨時表是物理表。再使用的時候,視情況而定,表變量處理小量數據更好,但是任務多、數據量大的操作對內存是個考驗。

經過反復測試,表變量做這種批量更新效率更好。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved