SQL SERVER中表變量之妙用*

莊　彥，未　培

(安徽工商職業(yè)學(xué)院 電子信息系，安徽 合肥 231131)

?

SQL SERVER中表變量之妙用*

莊彥，未培

(安徽工商職業(yè)學(xué)院 電子信息系，安徽 合肥 231131)

摘要:Table是SQL SERVER中一種特殊的數(shù)據(jù)類型，其生命周期短，具有表特性，在解決復(fù)雜的實(shí)際問(wèn)題時(shí)有著非常重要的作用.該文以企業(yè)工資管理系統(tǒng)的開(kāi)發(fā)為切入點(diǎn)，簡(jiǎn)單介紹了表變量的概念、特點(diǎn)，重點(diǎn)介紹了表變量在員工月工資核算模塊中的應(yīng)用.本系統(tǒng)經(jīng)過(guò)測(cè)試已經(jīng)上線運(yùn)行，經(jīng)實(shí)踐證明，在存儲(chǔ)數(shù)據(jù)量不是特別大的情況下，合理利用表變量可以有效的提高系統(tǒng)執(zhí)行效率.

關(guān)鍵詞:表變量；數(shù)據(jù)庫(kù)；系統(tǒng)開(kāi)發(fā)

社會(huì)信息化的發(fā)展已經(jīng)使我們現(xiàn)在的生活處于大數(shù)據(jù)時(shí)代，我們?cè)谏钪兴@取到的各種信息來(lái)自于計(jì)算機(jī)對(duì)大量數(shù)據(jù)的處理，而計(jì)算機(jī)對(duì)數(shù)據(jù)處理的效率影響著信息的獲取與更新.目前數(shù)據(jù)處理的方式有多種，對(duì)于常用的中小型信息管理系統(tǒng)，數(shù)據(jù)的存儲(chǔ)和處理使用SQL SERVER數(shù)據(jù)庫(kù)的比較多.但SQL SERVER數(shù)據(jù)庫(kù)也存在著吞吐量、并發(fā)控制、執(zhí)行效率等問(wèn)題，表變量是SQL SERVER中一種特殊的數(shù)據(jù)類型，它不寫入磁盤，執(zhí)行速度非?？?，應(yīng)用完成后不需要?jiǎng)h除，在處理復(fù)雜問(wèn)題時(shí)能起到臨時(shí)保存中間數(shù)據(jù)的作用，適合應(yīng)用在數(shù)據(jù)庫(kù)的自定義函數(shù)、存儲(chǔ)過(guò)程和觸發(fā)器中.但目前在實(shí)際的系統(tǒng)開(kāi)發(fā)中，很多人忽視了表變量的作用，應(yīng)用表變量解決實(shí)際問(wèn)題的程序員或系統(tǒng)開(kāi)發(fā)人員非常少，大家遇到具有表結(jié)構(gòu)的中間數(shù)據(jù)存儲(chǔ)，首先想到的是臨時(shí)表[1].表變量和臨時(shí)表各有優(yōu)缺點(diǎn)，臨時(shí)表在使用時(shí)寫入磁盤，執(zhí)行效率相對(duì)表變量要低一些，在普通數(shù)據(jù)量的情況下，使用表變量更合適.[2]

1表變量

1.1表變量介紹

在SQL SERVER數(shù)據(jù)庫(kù)中有一種特殊的數(shù)據(jù)類型叫Table，依據(jù)Table數(shù)據(jù)類型定義的變量稱作為表變量.表變量不同于一般數(shù)據(jù)類型的變量，一般數(shù)據(jù)類型的變量一次性只能存儲(chǔ)一個(gè)值，而表變量能像普通表格的樣式存儲(chǔ)表中的字段和記錄，表變量的賦值通過(guò)Insert into語(yǔ)句進(jìn)行，被賦值過(guò)的表變量可以像普通表格一樣被增、刪、改、查，使用起來(lái)非常方便.

1.2表變量定義

在SQL SERVER數(shù)據(jù)庫(kù)中表變量定義格式如下：

DECLARE @表名 TABLE(字段名1 字段描述，字段名2 字段描述，字段名3 字段描述……)

下面的多條SQL語(yǔ)句是對(duì)表變量的定義及增、刪、改、查的示例：

use工資管理數(shù)據(jù)庫(kù)

declare @emp table(eno char(10),ename varchar(20),sex char(2))--定義表變量@emp

insertinto@empselect工號(hào),姓名,性別from職工基本信息表--對(duì)表變量@emp執(zhí)行追加記錄操作

update@emp

set sex='女'

whereeno='2015101'--對(duì)表變量@emp執(zhí)行更新操作

delete@emp

whereeno='2015102'--對(duì)表變量@emp執(zhí)行刪除操作

select*from@emp--對(duì)表變量@emp執(zhí)行查詢操作

1.3表變量的特點(diǎn)

很多人在使用表變量時(shí)，容易把表變量和臨時(shí)表混淆，其實(shí)表變量和臨時(shí)表不同，在使用時(shí)一定要先弄清楚表變量的特點(diǎn).首先表變量占據(jù)的內(nèi)存非常小，并且表變量只在定義它的批中有效，當(dāng)定義表變量的批處理語(yǔ)句結(jié)束后，表變量會(huì)被自動(dòng)清除；其次表變量的事務(wù)只在數(shù)據(jù)更新時(shí)生效，產(chǎn)生的日志和鎖都比較少；另外表變量作用域小，不是數(shù)據(jù)庫(kù)中的持久部分，所以表變量不受事務(wù)回滾的影響.[3-4]

2表變量在數(shù)據(jù)庫(kù)中的應(yīng)用

下面以企業(yè)工資管理系統(tǒng)項(xiàng)目為例，介紹表變量在系統(tǒng)開(kāi)發(fā)中的應(yīng)用.

2.1項(xiàng)目介紹

企業(yè)工資管理系統(tǒng)是目前市場(chǎng)上應(yīng)用比較成熟的系統(tǒng)，由于各企業(yè)的性能、規(guī)模、管理體系等不同，針對(duì)不同企業(yè)應(yīng)用的工資管理系統(tǒng)還在不斷地被開(kāi)發(fā).本文引用的工資管理系統(tǒng)適用于中小企業(yè).員工的月工資部分包括不同工種的基礎(chǔ)性工資，以及每月員工受到的獎(jiǎng)勵(lì)和懲罰.本系統(tǒng)的用戶有管理員、財(cái)務(wù)工作人員及普通員工，管理員主要負(fù)責(zé)用戶基本信息及用戶權(quán)限管理，普通用戶可以通過(guò)系統(tǒng)管理個(gè)人信息及進(jìn)行工資查詢，財(cái)務(wù)工作人員用戶負(fù)責(zé)員工日常獎(jiǎng)懲情況記錄及員工月工資核算等.系統(tǒng)功能模塊圖如圖1所示.

圖1　企業(yè)工資管理系統(tǒng)功能模塊圖

2.2后臺(tái)數(shù)據(jù)庫(kù)

對(duì)于信息管理系統(tǒng)來(lái)說(shuō)，數(shù)據(jù)處理主要集中在后臺(tái)數(shù)據(jù)庫(kù)，適用于中小企業(yè)的工資管理系統(tǒng)，后臺(tái)數(shù)據(jù)庫(kù)采用SQL SERVER開(kāi)發(fā)較多.根據(jù)前文的分析可知，本系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)中需要員工、部門、基礎(chǔ)工資標(biāo)準(zhǔn)、獎(jiǎng)懲制度等實(shí)體信息的內(nèi)容，本系統(tǒng)后臺(tái)數(shù)據(jù)中的表有：

部門表(部門編號(hào)，部門名稱，部門電話)；

職工基本信息表(工號(hào)，姓名，性別，婚否，參加工作時(shí)間，部門編號(hào)，工種編號(hào)，電話，家庭住址)；

基礎(chǔ)工資級(jí)別表(工種編號(hào)，工種名稱，基本工資，津貼，午餐補(bǔ)助，交通補(bǔ)貼，通訊補(bǔ)貼，公積金)；

獎(jiǎng)懲制度表(獎(jiǎng)懲類型編號(hào)，獎(jiǎng)懲名稱，金額)；

獎(jiǎng)懲情況記錄表(序號(hào)，工號(hào)，獎(jiǎng)懲類型編號(hào)，記錄人員，記錄日期)；

各表之間的關(guān)系如下圖2所示.

圖2　企業(yè)工資管理系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)表之間的關(guān)系圖

2.3表變量應(yīng)用

在信息管理系統(tǒng)中，需要實(shí)現(xiàn)的功能往往比較復(fù)雜，通常通過(guò)單獨(dú)的一條SQL語(yǔ)句是沒(méi)有辦法實(shí)現(xiàn)的.但我們又不可能把所有的數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)庫(kù)中，在數(shù)據(jù)庫(kù)中數(shù)據(jù)庫(kù)存儲(chǔ)具有結(jié)構(gòu)化，數(shù)據(jù)的冗余度要降到最低，所以當(dāng)我們需要實(shí)現(xiàn)復(fù)雜功能時(shí)，可以借助表變量暫時(shí)存儲(chǔ)計(jì)算過(guò)程的中間數(shù)據(jù)，當(dāng)計(jì)算結(jié)束，表變量自動(dòng)清除，這樣既不增加系統(tǒng)存儲(chǔ)數(shù)據(jù)的負(fù)擔(dān)，又能利用這些中間數(shù)據(jù)實(shí)現(xiàn)復(fù)雜的應(yīng)用功能.

在企業(yè)工資管理系統(tǒng)中，核算員工月工資應(yīng)該是系統(tǒng)的核心，但通過(guò)上文系統(tǒng)分析知道，員工的月工資中包含基礎(chǔ)工資、獎(jiǎng)金、罰金等多部分內(nèi)容，要核算出員工的月工資需要分多步驟進(jìn)行，同時(shí)每個(gè)步驟產(chǎn)生的中間結(jié)果可以使用表變量進(jìn)行存儲(chǔ).下面以核算2015年11月公司員工工資為例，介紹表變量的應(yīng)用.

步驟一：通過(guò)表中現(xiàn)有的數(shù)據(jù)，根據(jù)每個(gè)員工的工種，計(jì)算出每個(gè)員工的基礎(chǔ)性工資，并保存到表變量“@應(yīng)發(fā)工資表”中，以備在后面計(jì)算實(shí)發(fā)工資使用.

declare @應(yīng)發(fā)工資表 table(工號(hào)char(10),姓名varchar(20),應(yīng)發(fā)工資money)

insert into @應(yīng)發(fā)工資表select 工號(hào),姓名,基本工資+津貼+午餐補(bǔ)助+交通補(bǔ)貼+通訊補(bǔ)貼-公積金 as 應(yīng)發(fā)工資from 職工基本信息表,基礎(chǔ)工資級(jí)別表where職工基本信息表.工種編號(hào)=基礎(chǔ)工資級(jí)別表.工種編號(hào)

步驟二：通過(guò)獎(jiǎng)懲制度表和獎(jiǎng)懲情況記錄表，計(jì)算出2015年11月員工的獎(jiǎng)金情況，并保存到表變量“@獎(jiǎng)金情況表”中.由于一名員工一個(gè)月可能會(huì)有多次獎(jiǎng)勵(lì)記錄，也有可能一次獎(jiǎng)金也沒(méi)有，在這里需要按員工的工號(hào)進(jìn)行分組，最終每個(gè)員工的獎(jiǎng)金應(yīng)該是在本月多次獎(jiǎng)金之和，另外為了計(jì)算方便，在獎(jiǎng)懲制度表中，所有代表獎(jiǎng)勵(lì)的制度編號(hào)都打“J”開(kāi)頭，所有代表懲罰的制度編號(hào)都打“F”開(kāi)頭.

declare @獎(jiǎng)金情況表table(工號(hào)char(10),獎(jiǎng)金money)

insert into @獎(jiǎng)金情況表select 工號(hào),sum(金額) as 獎(jiǎng)金from 獎(jiǎng)懲情況記錄表,獎(jiǎng)懲制度表 where獎(jiǎng)懲情況記錄表.獎(jiǎng)懲類型編號(hào)=獎(jiǎng)懲制度表.獎(jiǎng)懲類型編號(hào)and year(記錄日期)=2015 and month(記錄日期)=11 and left(獎(jiǎng)懲情況表.獎(jiǎng)懲類型編號(hào),1)='J'group by 工號(hào)

步驟三：同理步驟二，計(jì)算出2015年11月員工的罰金情況，并保存到表變量“@罰金情況表”中.

declare @罰金情況表table(工號(hào)char(10),罰金money)

insert into @罰金情況表select 工號(hào), sum(金額) as 罰金from 獎(jiǎng)懲情況記錄表,獎(jiǎng)懲制度表 where獎(jiǎng)懲情況記錄表.獎(jiǎng)懲類型編號(hào)=獎(jiǎng)懲制度表.獎(jiǎng)懲類型編號(hào)and year(記錄日期)=2015 and month(記錄日期)=11 and left(獎(jiǎng)懲情況表.獎(jiǎng)懲類型編號(hào),1)='F'group by 工號(hào)

在上面第二步和第三步中計(jì)算出的獎(jiǎng)金情況和罰金情況還是不能應(yīng)用在計(jì)算實(shí)發(fā)工資中，因?yàn)樯厦鎯刹襟E計(jì)算的結(jié)果中只包含在該月獲得過(guò)獎(jiǎng)金或者受到處罰的那些員工的信息，對(duì)于大部分員工，如果在該月沒(méi)有獲得過(guò)獎(jiǎng)勵(lì)或受到懲罰，他們的信息就不會(huì)出現(xiàn)在上面兩步的結(jié)果中.在最后計(jì)算實(shí)發(fā)工資時(shí)，我們需要利用“應(yīng)發(fā)工資+本月獎(jiǎng)金-本月罰金”公式進(jìn)行計(jì)算，那些沒(méi)有獎(jiǎng)金或沒(méi)有罰金的員工，獎(jiǎng)金或罰金項(xiàng)就會(huì)被當(dāng)作NULL處理.但在SQL SERVER中，任何數(shù)據(jù)加上或減去NULL，結(jié)果都為NULL，所以利用上面第二步驟和第三步驟的結(jié)果我們還不能計(jì)算出每個(gè)員工的實(shí)發(fā)工資.我們需要把職工基本信息表和上面兩步驟的結(jié)果進(jìn)行左連接，讓所有員工的信息都出現(xiàn)在獎(jiǎng)金或罰金情況表中，有獎(jiǎng)金或罰金的就以實(shí)際金額填充，沒(méi)有獎(jiǎng)金或罰金的借助SQL SERVER中 ISNULL函數(shù)，將NULL值用0取代掉即可.

步驟四：將職工基本“@獎(jiǎng)金情況表”進(jìn)行左連接，得到每個(gè)員工在2015年11月的獎(jiǎng)金情況，并保存到表變量“@月獎(jiǎng)金匯總表”中.

declare @月獎(jiǎng)金匯總表table(工號(hào)char(10),獎(jiǎng)金小計(jì)money)

insert into @月獎(jiǎng)金匯總表select 職工基本信息表.工號(hào)，isnull(獎(jiǎng)金,0) as獎(jiǎng)金小計(jì)

from 職工基本信息表left join @獎(jiǎng)金情況表as jj on 職工基本信息表.工號(hào)=jj.工號(hào)

步驟五：同理步驟四，得到每個(gè)員工在2015年11月的罰金情況，并保存到表變量“@月罰金匯總表”中.

declare @月罰金匯總表table(工號(hào)char(10),罰金小計(jì)money)

insert into @月罰金匯總表select 職工基本信息表.工號(hào)，isnull(罰金,0) as罰金小計(jì)

from 職工基本信息表left join @罰金情況表as fj on 職工基本信息表.工號(hào)=fj.工號(hào)

步驟六：利用步驟一、步驟四和步驟五所得到結(jié)果，計(jì)算出2015年11月每個(gè)員工的實(shí)發(fā)工資.

select zf.工號(hào),姓名,應(yīng)發(fā)工資+獎(jiǎng)金小計(jì)-罰金小計(jì)from @應(yīng)發(fā)工資表 aszf ,@月獎(jiǎng)金匯總表 as yjj ,@月罰金匯總表 as yfjwhere zf.工號(hào)=yjj.工號(hào)and zf.工號(hào)=yfj.工號(hào)

通過(guò)上面六個(gè)步驟即可計(jì)算出企業(yè)員工2015年11的實(shí)發(fā)工資情況，在實(shí)際的工資管理系統(tǒng)開(kāi)發(fā)中，是把上面的六個(gè)步驟寫成存儲(chǔ)過(guò)程或多語(yǔ)句表值自定義函數(shù)供前臺(tái)調(diào)用，并且不是將時(shí)間具體到固定的年和月，而是把年份和月份設(shè)置成參數(shù)，前臺(tái)在調(diào)用時(shí)通過(guò)參數(shù)傳遞可以計(jì)算出任意時(shí)間企業(yè)員工的工資情況.[5-6]

3小結(jié)

本文介紹了在工資管理系統(tǒng)中，通過(guò)表變量的應(yīng)用有效解決了員工月工資核算的問(wèn)題.本系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)的數(shù)據(jù)量不是很大，但月工資核算過(guò)程復(fù)雜，其中有很多中間數(shù)據(jù)產(chǎn)生，將這些中間數(shù)據(jù)保存在表變量中，既不會(huì)增加系統(tǒng)負(fù)擔(dān)，系統(tǒng)執(zhí)行速度還非?？?，系統(tǒng)穩(wěn)定性也很好.本人在數(shù)據(jù)庫(kù)的教學(xué)實(shí)踐中，多次應(yīng)用表變量解決實(shí)際應(yīng)用問(wèn)題，比如在學(xué)生成績(jī)管理系統(tǒng)中，根據(jù)相關(guān)規(guī)定利用表變量解決學(xué)生獎(jiǎng)學(xué)金的評(píng)定問(wèn)題、在教師測(cè)評(píng)系統(tǒng)中，利用表變量對(duì)教師的教學(xué)測(cè)評(píng)分?jǐn)?shù)進(jìn)行核算及排名等.由此可見(jiàn)表變量在關(guān)系型數(shù)據(jù)庫(kù)中有一定的通用性，故本系統(tǒng)對(duì)表變量的應(yīng)用有一定的推廣價(jià)值.表變量在數(shù)據(jù)量不是特別大的情況下應(yīng)用非常方便，但表變量在應(yīng)用過(guò)程中也有一定的局限性，比如不能將表變量直接賦值給另一個(gè)變量，不能在表變量的基礎(chǔ)上再進(jìn)一步建立索引或修改其結(jié)構(gòu)等.所以大家在使用表變量時(shí)需綜合考慮，在合適的環(huán)境下充分發(fā)揮表變量的作用.

參考文獻(xiàn)：

[1]未培.存儲(chǔ)過(guò)程在企業(yè)工資管理系統(tǒng)中的設(shè)計(jì)與實(shí)現(xiàn)[J].赤峰學(xué)院學(xué)報(bào)，2013(24).

[2]亓文娟.對(duì)Sql Server存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的研究與應(yīng)用[J].廊坊師范學(xué)院學(xué)報(bào)，2010(12).

[3]黃龍軍.應(yīng)用存儲(chǔ)過(guò)程實(shí)現(xiàn)數(shù)據(jù)分頁(yè)[J].計(jì)算機(jī)系統(tǒng)應(yīng)用,2012(12).

[4]陳曉珊.Sql Server存儲(chǔ)過(guò)程淺析[J].科技信息,2008(7).

[5]趙灼.淺談如何優(yōu)化SQL Server數(shù)據(jù)庫(kù)[J].中國(guó)管理信息化,2014(03).

[6]葉柯.淺談MSSQL Server數(shù)據(jù)庫(kù)查詢優(yōu)化[J].寧波廣播電視大學(xué)學(xué)報(bào),2007(01).

(責(zé)任編輯:王前)

The Effects of Table Variables in SQL SERVER

ZHUANG Yan, WEI Pei

(ElectronInformationDepartment,AnhuiBusinessVocationalCollege,Hefei,Anhui231131,China)

Abstract:Table is a special kind of data type in SQL Server, and has the characteristic of short life cycle and table.Table variables can play an important role in solving complex problems if the flexible use of good. This paper takes the enterprise wage management system as an example to introduce the concept and characteristics of the table variable, and mainly introduces the application of the table variable in the employee's monthly salary calculation module. The enterprise wage management system has been running. Proved by practice, table variables can improve the efficiency of system execution if the amount of data is not particularly large.

Key words:Table variables; DB; System development

DOI:10.13877/j.cnki.cn22-1284.2016.06.019

*收稿日期：2015-12-01

基金項(xiàng)目：2015安徽高校自然科學(xué)研究重點(diǎn)項(xiàng)目“基于分級(jí)特征值算法的重復(fù)信息過(guò)濾研究”(KJ2015A419)；2016年度安徽省高校優(yōu)秀青年人才支持計(jì)劃重點(diǎn)項(xiàng)目(gxyqZD2016436)

作者簡(jiǎn)介：莊彥，女，安徽淮北人，講師.

中圖分類號(hào)：TP31

文獻(xiàn)標(biāo)志碼：A

文章編號(hào)：1008-7974(2016)03-0059-04