王睿

摘要：在軟件系統(tǒng)研發(fā)過程中，數(shù)據(jù)統(tǒng)計(jì)匯總的效率始終是難點(diǎn)，如何快速有效地將零碎的業(yè)務(wù)數(shù)據(jù)統(tǒng)計(jì)匯總成報(bào)表數(shù)據(jù)，成為數(shù)據(jù)庫開發(fā)人員的研究重點(diǎn)。文章介紹了一種基于視圖+存儲過程+靜態(tài)數(shù)據(jù)表+任務(wù)的方式，對報(bào)表數(shù)據(jù)進(jìn)行定時(shí)統(tǒng)計(jì)匯總。

關(guān)鍵詞：SQLServer2005；數(shù)據(jù)定時(shí)統(tǒng)計(jì)方法；業(yè)務(wù)數(shù)據(jù)；報(bào)表數(shù)據(jù)；數(shù)據(jù)庫 文獻(xiàn)標(biāo)識碼：A

中圖分類號：TP311 文章編號：1009-2374（2016）17-0021-03 DOI：10.13535/j.cnki.11-4406/n.2016.17.009

1 概述

SQLServer數(shù)據(jù)庫是Microsoft公司推出的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。具有使用方便可伸縮性好與相關(guān)軟件集成程度高等優(yōu)點(diǎn)，可跨越從運(yùn)行Microsoft Windows 98的膝上型電腦到運(yùn)行Microsoft Windows 2012的大型多處理器的服務(wù)器等多種平臺使用。目前最新版本是SQLServer2012。本文主要針對日常工作中比較常用版本SQLServer2005進(jìn)行分析，研究其數(shù)據(jù)定時(shí)統(tǒng)計(jì)的相關(guān)方法，并附實(shí)例加以說明。

2 軟件項(xiàng)目數(shù)據(jù)統(tǒng)計(jì)中遇到的問題

在日常工作中，我們會經(jīng)常遇到類似于這樣的問題：“一張統(tǒng)計(jì)報(bào)表需要從十幾張，甚至幾十張業(yè)務(wù)基礎(chǔ)數(shù)據(jù)表中匯總。”傳統(tǒng)的處理方法是直接拼接SQL查詢語句，然后將查詢結(jié)果傳遞給報(bào)表控件，這樣的處理方法看似沒有問題，但隨著業(yè)務(wù)數(shù)據(jù)的不斷增加，用戶打開報(bào)表的速度越來越慢，最后報(bào)表直接無法打開或者打開需要很長時(shí)間，從而導(dǎo)致整個(gè)項(xiàng)目的用戶體驗(yàn)大打折扣。

3 數(shù)據(jù)定時(shí)統(tǒng)計(jì)方法的研究

面對這一問題，我們可以采用數(shù)據(jù)定時(shí)統(tǒng)計(jì)的方法將基礎(chǔ)業(yè)務(wù)數(shù)據(jù)的統(tǒng)計(jì)結(jié)果保存在一張業(yè)務(wù)表中，然后數(shù)據(jù)報(bào)表直接從統(tǒng)計(jì)結(jié)果表中獲取數(shù)據(jù)，如此一來，數(shù)據(jù)統(tǒng)計(jì)效率大大提升，同時(shí)也簡化了報(bào)表編碼的復(fù)雜度。

我們在SQLServer2005中編寫基礎(chǔ)業(yè)務(wù)數(shù)據(jù)統(tǒng)計(jì)視圖，然后建立與視圖結(jié)構(gòu)一致的數(shù)據(jù)表，同時(shí)編寫存儲過程將視圖統(tǒng)計(jì)的結(jié)果性數(shù)據(jù)同步到結(jié)果表中，最后采用SQLServer2005的定時(shí)任務(wù)，定期執(zhí)行同步數(shù)據(jù)的存儲過程即可，具體實(shí)現(xiàn)方法及步驟，如圖1所示。

案例說明：

定時(shí)統(tǒng)計(jì)各單位累計(jì)消費(fèi)情況：

第一步：創(chuàng)建視圖

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

第二步：創(chuàng)建統(tǒng)計(jì)結(jié)果表

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]

第三步：創(chuàng)建數(shù)據(jù)同步存儲過程

數(shù)據(jù)同步插入存儲過程：

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（‘記錄已經(jīng)存在'，16，8）

rollback tran——回滾事物

end

else

commit tran——提交事務(wù)

創(chuàng)建返回游標(biāo)的存儲過程：

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

執(zhí)行數(shù)據(jù)同步插入的存儲過程：

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

第四步：創(chuàng)建任務(wù)定時(shí)執(zhí)行存儲過程

DECLARE @jobId BINARY（16）

EXEC @ReturnCode=

msdb.dbo.sp_add_job @job_name=NJob_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=Nadmin， @job_id = @jobId OUTPUT--數(shù)據(jù)庫登錄帳號

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep @job_id=@jobId， @step_name=NDELETE_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=NTSQL，

@command=N DELETE FROM TV_ORG_CONSUME ‘，——清除臨時(shí)表中結(jié)果數(shù)據(jù)

@database_name=NTESTDB， ——數(shù)據(jù)庫名稱

@output_file_name=NC：＼TEMP＼T_SQL＼DELETE_ LOG.txt，——刪除數(shù)據(jù)日志

@flags=22

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep@job_id=@jobId， @step_name=NSYNCHRONIZATION_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=NTSQL，

@command=N

DECLARE @RC int

EXECUTE @RC=[P_ ORG_CONSUME_INSERT_EXCUTE] ——執(zhí)行數(shù)據(jù)同步存儲過程

GO，

@database_name=NTESTDB， ——數(shù)據(jù)庫名稱

@output_file_name=NC：＼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=NEXCUTE，

@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，——每天晚上八點(diǎn)半定時(shí)任務(wù)開始執(zhí)行

@active_end_time=210000——每天晚上九點(diǎn)定時(shí)任務(wù)執(zhí)行結(jié)束

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 EndSave

QuitWithRollback：

IF （@@TRANCOUNT > 0） ROLLBACK TRANSACTION

EndSave：

4 結(jié)語

采用存儲過程定期的將視圖中的數(shù)據(jù)同步至靜態(tài)表的數(shù)據(jù)匯總方法適合與數(shù)據(jù)量較大的業(yè)務(wù)處理上，例如銀行財(cái)務(wù)扎賬、各類成本匯總等方面均有明顯的優(yōu)勢，其充分利用了數(shù)據(jù)庫的數(shù)據(jù)處理能力，減少了應(yīng)用程序?qū)?shù)據(jù)的二次處理，同時(shí)大大提高了報(bào)表的查詢效率。對于小規(guī)模的數(shù)據(jù)統(tǒng)計(jì)，不建議采用此方法進(jìn)行數(shù)據(jù)統(tǒng)計(jì)匯總。

同時(shí)采用此方法也存在一些問題，例如：定時(shí)任務(wù)執(zhí)行過程中有業(yè)務(wù)數(shù)據(jù)產(chǎn)生，業(yè)務(wù)數(shù)據(jù)不會被統(tǒng)計(jì)到結(jié)果中；用戶不能看到實(shí)時(shí)的統(tǒng)計(jì)結(jié)果等。

參考文獻(xiàn)

[1] 明日科技.SQL Server從入門到精通[M].北京：清華大學(xué)出版社，2012.

[2] [美]Ben Forta，鐘鳴，劉曉霞.SQL必知必會[M].北京：人民郵電出版社，2013.

[3] [美]西爾伯沙茨，楊冬青.數(shù)據(jù)庫系統(tǒng)概念[M].北京：機(jī)械工業(yè)出版社，2012.

（責(zé)任編輯：黃銀芳）