SQL SERVER中表变量之妙用*

庄　彦，未　培

(安徽工商职业学院 电子信息系，安徽 合肥 231131)



SQL SERVER中表变量之妙用*

庄彦，未培

(安徽工商职业学院 电子信息系，安徽 合肥 231131)

摘要:Table是SQL SERVER中一种特殊的数据类型，其生命周期短，具有表特性，在解决复杂的实际问题时有着非常重要的作用.该文以企业工资管理系统的开发为切入点，简单介绍了表变量的概念、特点，重点介绍了表变量在员工月工资核算模块中的应用.本系统经过测试已经上线运行，经实践证明，在存储数据量不是特别大的情况下，合理利用表变量可以有效的提高系统执行效率.

关键词:表变量；数据库；系统开发

社会信息化的发展已经使我们现在的生活处于大数据时代，我们在生活中所获取到的各种信息来自于计算机对大量数据的处理，而计算机对数据处理的效率影响着信息的获取与更新.目前数据处理的方式有多种，对于常用的中小型信息管理系统，数据的存储和处理使用SQL SERVER数据库的比较多.但SQL SERVER数据库也存在着吞吐量、并发控制、执行效率等问题，表变量是SQL SERVER中一种特殊的数据类型，它不写入磁盘，执行速度非常快，应用完成后不需要删除，在处理复杂问题时能起到临时保存中间数据的作用，适合应用在数据库的自定义函数、存储过程和触发器中.但目前在实际的系统开发中，很多人忽视了表变量的作用，应用表变量解决实际问题的程序员或系统开发人员非常少，大家遇到具有表结构的中间数据存储，首先想到的是临时表[1].表变量和临时表各有优缺点，临时表在使用时写入磁盘，执行效率相对表变量要低一些，在普通数据量的情况下，使用表变量更合适.[2]

1表变量

1.1表变量介绍

在SQL SERVER数据库中有一种特殊的数据类型叫Table，依据Table数据类型定义的变量称作为表变量.表变量不同于一般数据类型的变量，一般数据类型的变量一次性只能存储一个值，而表变量能像普通表格的样式存储表中的字段和记录，表变量的赋值通过Insert into语句进行，被赋值过的表变量可以像普通表格一样被增、删、改、查，使用起来非常方便.

1.2表变量定义

在SQL SERVER数据库中表变量定义格式如下：

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

下面的多条SQL语句是对表变量的定义及增、删、改、查的示例：

use工资管理数据库

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

insertinto@empselect工号,姓名,性别from职工基本信息表--对表变量@emp执行追加记录操作

update@emp

set sex='女'

whereeno='2015101'--对表变量@emp执行更新操作

delete@emp

whereeno='2015102'--对表变量@emp执行删除操作

select*from@emp--对表变量@emp执行查询操作

1.3表变量的特点

很多人在使用表变量时，容易把表变量和临时表混淆，其实表变量和临时表不同，在使用时一定要先弄清楚表变量的特点.首先表变量占据的内存非常小，并且表变量只在定义它的批中有效，当定义表变量的批处理语句结束后，表变量会被自动清除；其次表变量的事务只在数据更新时生效，产生的日志和锁都比较少；另外表变量作用域小，不是数据库中的持久部分，所以表变量不受事务回滚的影响.[3-4]

2表变量在数据库中的应用

下面以企业工资管理系统项目为例，介绍表变量在系统开发中的应用.

2.1项目介绍

企业工资管理系统是目前市场上应用比较成熟的系统，由于各企业的性能、规模、管理体系等不同，针对不同企业应用的工资管理系统还在不断地被开发.本文引用的工资管理系统适用于中小企业.员工的月工资部分包括不同工种的基础性工资，以及每月员工受到的奖励和惩罚.本系统的用户有管理员、财务工作人员及普通员工，管理员主要负责用户基本信息及用户权限管理，普通用户可以通过系统管理个人信息及进行工资查询，财务工作人员用户负责员工日常奖惩情况记录及员工月工资核算等.系统功能模块图如图1所示.

图1　企业工资管理系统功能模块图

2.2后台数据库

对于信息管理系统来说，数据处理主要集中在后台数据库，适用于中小企业的工资管理系统，后台数据库采用SQL SERVER开发较多.根据前文的分析可知，本系统后台数据库中需要员工、部门、基础工资标准、奖惩制度等实体信息的内容，本系统后台数据中的表有：

部门表(部门编号，部门名称，部门电话)；

职工基本信息表(工号，姓名，性别，婚否，参加工作时间，部门编号，工种编号，电话，家庭住址)；

基础工资级别表(工种编号，工种名称，基本工资，津贴，午餐补助，交通补贴，通讯补贴，公积金)；

奖惩制度表(奖惩类型编号，奖惩名称，金额)；

奖惩情况记录表(序号，工号，奖惩类型编号，记录人员，记录日期)；

各表之间的关系如下图2所示.

图2　企业工资管理系统后台数据库表之间的关系图

2.3表变量应用

在信息管理系统中，需要实现的功能往往比较复杂，通常通过单独的一条SQL语句是没有办法实现的.但我们又不可能把所有的数据都存储在数据库中，在数据库中数据库存储具有结构化，数据的冗余度要降到最低，所以当我们需要实现复杂功能时，可以借助表变量暂时存储计算过程的中间数据，当计算结束，表变量自动清除，这样既不增加系统存储数据的负担，又能利用这些中间数据实现复杂的应用功能.

在企业工资管理系统中，核算员工月工资应该是系统的核心，但通过上文系统分析知道，员工的月工资中包含基础工资、奖金、罚金等多部分内容，要核算出员工的月工资需要分多步骤进行，同时每个步骤产生的中间结果可以使用表变量进行存储.下面以核算2015年11月公司员工工资为例，介绍表变量的应用.

步骤一：通过表中现有的数据，根据每个员工的工种，计算出每个员工的基础性工资，并保存到表变量“@应发工资表”中，以备在后面计算实发工资使用.

declare @应发工资表 table(工号char(10),姓名varchar(20),应发工资money)

insert into @应发工资表select 工号,姓名,基本工资+津贴+午餐补助+交通补贴+通讯补贴-公积金 as 应发工资from 职工基本信息表,基础工资级别表where职工基本信息表.工种编号=基础工资级别表.工种编号

步骤二：通过奖惩制度表和奖惩情况记录表，计算出2015年11月员工的奖金情况，并保存到表变量“@奖金情况表”中.由于一名员工一个月可能会有多次奖励记录，也有可能一次奖金也没有，在这里需要按员工的工号进行分组，最终每个员工的奖金应该是在本月多次奖金之和，另外为了计算方便，在奖惩制度表中，所有代表奖励的制度编号都打“J”开头，所有代表惩罚的制度编号都打“F”开头.

declare @奖金情况表table(工号char(10),奖金money)

insert into @奖金情况表select 工号,sum(金额) as 奖金from 奖惩情况记录表,奖惩制度表 where奖惩情况记录表.奖惩类型编号=奖惩制度表.奖惩类型编号and year(记录日期)=2015 and month(记录日期)=11 and left(奖惩情况表.奖惩类型编号,1)='J'group by 工号

步骤三：同理步骤二，计算出2015年11月员工的罚金情况，并保存到表变量“@罚金情况表”中.

declare @罚金情况表table(工号char(10),罚金money)

insert into @罚金情况表select 工号, sum(金额) as 罚金from 奖惩情况记录表,奖惩制度表 where奖惩情况记录表.奖惩类型编号=奖惩制度表.奖惩类型编号and year(记录日期)=2015 and month(记录日期)=11 and left(奖惩情况表.奖惩类型编号,1)='F'group by 工号

在上面第二步和第三步中计算出的奖金情况和罚金情况还是不能应用在计算实发工资中，因为上面两步骤计算的结果中只包含在该月获得过奖金或者受到处罚的那些员工的信息，对于大部分员工，如果在该月没有获得过奖励或受到惩罚，他们的信息就不会出现在上面两步的结果中.在最后计算实发工资时，我们需要利用“应发工资+本月奖金-本月罚金”公式进行计算，那些没有奖金或没有罚金的员工，奖金或罚金项就会被当作NULL处理.但在SQL SERVER中，任何数据加上或减去NULL，结果都为NULL，所以利用上面第二步骤和第三步骤的结果我们还不能计算出每个员工的实发工资.我们需要把职工基本信息表和上面两步骤的结果进行左连接，让所有员工的信息都出现在奖金或罚金情况表中，有奖金或罚金的就以实际金额填充，没有奖金或罚金的借助SQL SERVER中 ISNULL函数，将NULL值用0取代掉即可.

步骤四：将职工基本“@奖金情况表”进行左连接，得到每个员工在2015年11月的奖金情况，并保存到表变量“@月奖金汇总表”中.

declare @月奖金汇总表table(工号char(10),奖金小计money)

insert into @月奖金汇总表select 职工基本信息表.工号，isnull(奖金,0) as奖金小计

from 职工基本信息表left join @奖金情况表as jj on 职工基本信息表.工号=jj.工号

步骤五：同理步骤四，得到每个员工在2015年11月的罚金情况，并保存到表变量“@月罚金汇总表”中.

declare @月罚金汇总表table(工号char(10),罚金小计money)

insert into @月罚金汇总表select 职工基本信息表.工号，isnull(罚金,0) as罚金小计

from 职工基本信息表left join @罚金情况表as fj on 职工基本信息表.工号=fj.工号

步骤六：利用步骤一、步骤四和步骤五所得到结果，计算出2015年11月每个员工的实发工资.

select zf.工号,姓名,应发工资+奖金小计-罚金小计from @应发工资表 aszf ,@月奖金汇总表 as yjj ,@月罚金汇总表 as yfjwhere zf.工号=yjj.工号and zf.工号=yfj.工号

通过上面六个步骤即可计算出企业员工2015年11的实发工资情况，在实际的工资管理系统开发中，是把上面的六个步骤写成存储过程或多语句表值自定义函数供前台调用，并且不是将时间具体到固定的年和月，而是把年份和月份设置成参数，前台在调用时通过参数传递可以计算出任意时间企业员工的工资情况.[5-6]

3小结

本文介绍了在工资管理系统中，通过表变量的应用有效解决了员工月工资核算的问题.本系统后台数据库的数据量不是很大，但月工资核算过程复杂，其中有很多中间数据产生，将这些中间数据保存在表变量中，既不会增加系统负担，系统执行速度还非常快，系统稳定性也很好.本人在数据库的教学实践中，多次应用表变量解决实际应用问题，比如在学生成绩管理系统中，根据相关规定利用表变量解决学生奖学金的评定问题、在教师测评系统中，利用表变量对教师的教学测评分数进行核算及排名等.由此可见表变量在关系型数据库中有一定的通用性，故本系统对表变量的应用有一定的推广价值.表变量在数据量不是特别大的情况下应用非常方便，但表变量在应用过程中也有一定的局限性，比如不能将表变量直接赋值给另一个变量，不能在表变量的基础上再进一步建立索引或修改其结构等.所以大家在使用表变量时需综合考虑，在合适的环境下充分发挥表变量的作用.

参考文献：

[1]未培.存储过程在企业工资管理系统中的设计与实现[J].赤峰学院学报，2013(24).

[2]亓文娟.对Sql Server存储过程存储过程的研究与应用[J].廊坊师范学院学报，2010(12).

[3]黄龙军.应用存储过程实现数据分页[J].计算机系统应用,2012(12).

[4]陈晓珊.Sql Server存储过程浅析[J].科技信息,2008(7).

[5]赵灼.浅谈如何优化SQL Server数据库[J].中国管理信息化,2014(03).

[6]叶柯.浅谈MSSQL Server数据库查询优化[J].宁波广播电视大学学报,2007(01).

(责任编辑:王前)

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

基金项目：2015安徽高校自然科学研究重点项目“基于分级特征值算法的重复信息过滤研究”(KJ2015A419)；2016年度安徽省高校优秀青年人才支持计划重点项目(gxyqZD2016436)

作者简介：庄彦，女，安徽淮北人，讲师.

中图分类号：TP31

文献标志码：A

文章编号：1008-7974(2016)03-0059-04