原创《SQLServer2005中的数据定时统计方法》
本文是统计方法有关研究生毕业论文范文跟SQLServer2005和定时和统计有关研究生毕业论文范文。
王 睿
（兰州铁路技师学院,甘肃 兰州 730030）
摘 要：在软件系统研发过程中,数据统计汇总的效率始终是难点,如何快速有效地将零碎的业务数据统计汇总成报表数据,成为数据库开发人员的研究重点.文章介绍了一种基于视图＋存储过程＋静态数据表＋任务的方式,对报表数据进行定时统计汇总.
关键词：SQLServer2005；数据定时统计方法；业务数据；报表数据；数据库 文献标识码：A
中图分类号：TP311 文章编号：1009-2374（2016）17-0021-03 DOI：10.13535/j.cnki.11-4406/n.2016.17.009
1 概述
SQLServer数据库是Microsoft公司推出的关系型数据库管理系统.具有使用方便可伸缩性好与相关软件集成程度高等优点,可跨越从运行Microsoft Windows 98的膝上型电脑到运行Microsoft Windows 2012的大型多处理器的服务器等多种平台使用.目前最新版本是SQLServer2012.本文主要针对日常工作中比较常用版本SQLServer2005进行分析,研究其数据定时统计的相关方法,并附实例加以说明.
2 软件项目数据统计中遇到的问题
在日常工作中,我们会经常遇到类似于这样的问题：“一张统计报表需要从十几张,甚至几十张业务基础数据表中汇总.”传统的处理方法是直接拼接SQL查询语句,然后将查询结果传递给报表控件,这样的处理方法看似没有问题,但随着业务数据的不断增加,用户打开报表的速度越来越慢,最后报表直接无法打开或者打开需要很长时间,从而导致整个项目的用户体验大打折扣.
3 数据定时统计方法的研究
面对这一问题,我们可以采用数据定时统计的方法将基础业务数据的统计结果保存在一张业务表中,然后数据报表直接从统计结果表中获取数据,如此一来,数据统计效率大大提升,同时也简化了报表编码的复杂度.
我们在SQLServer2005中编写基础业务数据统计视图,然后建立与视图结构一致的数据表,同时编写存储过程将视图统计的结果性数据同步到结果表中,最后采用SQLServer2005的定时任务,定期执行同步数据的存储过程即可,具体实现方法及步骤,如图1所示.
案例说明：
定时统计各单位累计消费情况：
第一步：创建视图
CREATE VIEW [V_ORG_ CONSUME]
AS
SELECT O.CODE_,O.NAME_, SUM(C.MONEY_) AS TOTALMONEY_
FROM T_ CONSUME AS C
LEFT JOIN T_ACTOR AS A ON A.ID等于C.ACTORID_
LEFT JOIN T_ACTOR_ORG AS AO A.ID等于AO.ACTORID_
LEFT JOIN T_ORG AS O ON AO.ORGID_等于O.ID
GROUP BY O.ID,O.CODE_,O.NAME_
ORDER BY ID
第二步：创建统计结果表
CREATE TABLE [TV_ORG_ CONSUME](
[ID] [bigint] NOT NULL,
[CODE_] [varchar](100) NULL,
[NAME_] [varchar](200) NULL,
[TOTALMONEY_] [float]NULL
PRIMARY KEY CLUSTERED
([ID]ASC)WITH(PAD_INDEX等于OFF,STATISTICS_NORECOMPUTE等于OFF, IGNORE_DUP_KEY 等于 OFF, ALLOW_ROW_LOCKS等于 ON, ALLOW_PAGE_LOCKS 等于 ON) ON [PRIMARY]
) ON [PRIMARY]
第三步：创建数据同步存储过程
数据同步插入存储过程：
Create procedure [P_ORG_ CONSUME_INSERT]
@ID int,
@CODE_ varchar,
@NAME_ varchar,
@TOTALMONEY_ decimal(18, 10)
as
declare @sum int
begin tran
insert into TV_ORG_ CONSUME(ID,CODE_,NAME_, TOTALMONEY_)
values(@ID,@ CODE_,@ NAME_,@ TOTALMONEY_)
select @sum 等于 count(*) from TV_ ORG_ CONSUME where (ID等于@ID)
if(@sum>1)
begin
raiserror(‘记录已经存在´,16,8)
rollback tran——回滚事物
end
else
commit tran——提交事务
创建返回游标的存储过程：
Create procedure [P_ ORG_ CONSUME_GET]
@VChcekCursor Cursor Varying Output
As
Set @VChcekCursor 等于 Cursor
For
SelectID, CODE_,NAME_, TOTALMONEY_
from V_ORG_ CONSUME
Open @VChcekCursor
执行数据同步插入的存储过程：
Create procedure[P_ORG_ CONSUME_INSERT_EXCUTE]
As
Declare @ID int
Declare @ CODE_varchar
Declare @ NAME_varchar
Declare @ TOTALMONEY_decimal(18, 10)
Exec P_ORG_CONSUME_GET @VChcekCursor out
Fetch Next From @VChcekCursor
InTo @ID,@ CODE_,@ NAME_,@ TOTALMONEY_
While(@@Fetch_Status 等于 0)
Begin
exec[P_ORG_CONSUME_INSERT]
@ID,@ CODE_,@ NAME_,@ TOTALMONEY_
Fetch Next From @VChcekCursor
InTo @ID,@ CODE_,@ NAME_,@ TOTALMONEY_
End
Close @VChcekCursor
Deallocate@VChcekCursor
Go
第四步：创建任务定时执行存储过程
DECLARE @jobId BINARY(16)
EXEC @ReturnCode等于
msdb.dbo.sp_add_job @job_name等于N’Job_Consume_Excute_Procedure’,
@enabled等于1,
@notify_level_eventlog等于2,
@notify_level_email等于0,
@notify_level_netsend等于0,
@notify_level_page等于0,
@delete_level等于0,
@description等于N’无描述.’,
@category_name等于N’[Uncategorized (Local)]’,
@owner_login_name等于N’admin’, @job_id 等于 @jobId OUTPUT--数据库登录帐号
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode 等于
msdb.dbo.sp_add_jobstep @job_id等于@jobId, @step_name等于N’DELETE_DATA’,
@step_id等于1,
@cmdexec_success_code等于0,
@on_success_action等于3,
@on_success_step_id等于0,
@on_fail_action等于2,
@on_fail_step_id等于0,
@retry_attempts等于0,
@retry_interval等于0,
@os_run_priority等于0, @subsystem等于N’TSQL’,
@command等于N’ DELETE FROM TV_ORG_CONSUME ‘,——清除临时表中结果数据
@database_name等于N’TESTDB’, ——数据库名称
@output_file_name等于N’C:\TEMP\T_SQL\DELETE_ LOG.txt’,——删除数据日志
@flags等于22
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode 等于
msdb.dbo.sp_add_jobstep@job_id等于@jobId, @step_name等于N’SYNCHRONIZATION_DATA’,
@step_id等于2,
@cmdexec_success_code等于0,
@on_success_action等于1,
@on_success_step_id等于0,
@on_fail_action等于2,
@on_fail_step_id等于0,
@retry_attempts等于0,
@retry_interval等于0,
@os_run_priority等于0, @subsystem等于N’TSQL’,
@command等于N’
DECLARE @RC int
EXECUTE @RC等于[P_ ORG_CONSUME_INSERT_EXCUTE] ——执行数据同步存储过程
GO’,
@database_name等于N’TESTDB’, ——数据库名称
@output_file_name等于N’C:\TEMP\T_SQL\SYNCHRONIZATION_lOG.txt’,——日志存放目录
@flags等于22
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode等于msdb.dbo.sp_update_job @job_id等于@jobId, @start_step_id等于1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode等于msdb.dbo.sp_add_jobschedule @job_id等于@jobId, @name等于N’EXCUTE’,
@enabled等于1,
@freq_type等于4,
@freq_interval等于1,
@freq_subday_type等于8,
@freq_subday_interval等于6,
@freq_relative_interval等于0,
@freq_recurrence_factor等于0,
@active_start_date等于20160320,
@active_end_date等于99991231,
@active_start_time等于203000,——每天晚上八点半定时任务开始执行
@active_end_time等于210000——每天晚上九点定时任务执行结束
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode 等于 msdb.dbo.sp_add_jobserver @job_id 等于 @jobId, @server_name 等于 N’(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSe
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSe:
4 结语
采用存储过程定期的将视图中的数据同步至静态表的数据汇总方法适合与数据量较大的业务处理上,例如银行财务扎账、各类成本汇总等方面均有明显的优势,其充分利用了数据库的数据处理能力,减少了应用程序对数据的二次处理,同时大大提高了报表的查询效率.对于小规模的数据统计,不建议采用此方法进行数据统计汇总.
同时采用此方法也存在一些问题,例如：定时任务执行过程中有业务数据产生,业务数据不会被统计到结果中；用户不能看到实时的统计结果等.
参考文献
[1]明日科技．SQL Server从入门到精通[M]．北京：清华大学出版社,2012．
[2][美]Ben Forta,钟鸣,刘晓霞．SQL必知必会[M]．北京：人民邮电出版社,2013．
[3][美]西尔伯沙茨,杨冬青．数据库系统概念[M]．北京：机械工业出版社,2012．
（责任编辑：黄银芳）
统计方法论文参考资料：
上文评论：上文是关于对写作SQLServer2005和定时和统计论文范文与课题研究的大学硕士、统计方法本科毕业论文统计方法论文开题报告范文和相关文献综述及职称论文参考文献资料有帮助。
