sql server作业用法.docx

资源描述

sql server作业用法.docx

《sql server作业用法.docx》由会员分享，可在线阅读，更多相关《sql server作业用法.docx（12页珍藏版）》请在冰豆网上搜索。

sql server作业用法.docx

sqlserver作业用法

一．作业的运用（自动更新db）

1、打开sqlserver的企业管理器，找到管理中的作业

2、新增一个作业，将具体的设置进行设定，新增步骤以确定要处理的sp及sql语句

新增调度以确定要执行的频率!

3、例子

二．处理SP及其它sql语句以进行作业处理（运用数据仓库的模式）

1、创建要进行保存数据的空间（一般为表）

2、运用作业进行数据填充

3、通过存的数据进行数据呈现

三．具体示例

1、创建表

ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[r_Count1]'）

andOBJECTPROPERTY（id,N'IsUserTable'）=1）

droptable[dbo].[r_Count1]

CREATETABLE[dbo].[r_Count1]（

[UnitCoding][varchar]（15）COLLATEChinese_PRC_CI_ASNOTNULL,

[StatDate][varchar]（10）COLLATEChinese_PRC_CI_ASNOTNULL,

[Field1][int]NOTNULL,

[Field2][int]NOTNULL,

[Field3][int]NOTNULL,

[Field4][int]NOTNULL,

[Field5][int]NOTNULL,

[Field6][int]NOTNULL,

[Field7][int]NOTNULL,

[Field8][int]NOTNULL,

[Field9][int]NOTNULL,

[Field10][int]NOTNULL,

[Field11][int]NOTNULL,

[Field12][int]NOTNULL,

[Field13][int]NOTNULL,

[Field14][int]NOTNULL,

[Field15][int]NOTNULL,

[Field16][int]NOTNULL,

[Field17][int]NOTNULL,

[Field18][int]NOTNULL

）ON[PRIMARY]

2、处理数据（运用作业的sp）

ALTER PROCEDUREAutoExec_Count1

（

@UnitCodingvarchar（20）,

@StatDatedatetime

）

/*向临时表中插入要变更单位的新旧单位代码*/

Declare@i_SFirstdatevarchar（10）

Declare@i_EFirstdatevarchar（10）

Declare@i_SSeconddatevarchar（10）

Declare@i_ESeconddatevarchar（10）

Declare@i_getdatedatetime

Select@i_getdate=@StatDate

Select@i_SFirstdate=dbo.u_Date2Char（DATEADD（mm,DATEDIFF（mm,0,@i_getdate）,0））--当月第1天

Select@i_EFirstdate=dbo.u_Date2Char（DATEADD（dd,14,DATEADD（mm,DATEDIFF（mm,0,@i_getdate）,0）））--当月第15天

Select@i_SSeconddate=dbo.u_Date2Char（DATEADD（dd,15,DATEADD（mm,DATEDIFF（mm,0,@i_getdate）,0））） --当月第16天

Select@i_ESeconddate=dbo.u_Date2Char（DATEADD（ms,-3,DATEADD（mm,DATEDIFF（m,0,@i_getdate）+1,0））） --当月最后1天

/*启动事务*/

Begintransaction

/*创建用于保存单位代码和报表周期的临时表*/

CreateTable#TblUnitDateSwap

（

NumberIDintIDENTITY（1,1）NOTNULL,

UnitCodingvarchar（20）,

startdatevarchar（10）,

enddatevarchar（10）

）

/*向临时表中插入单位数据*/

Select@UnitCoding=RTrim（@UnitCoding）+'%' --

--插入上半月统计日期（例:

2004-1-1~2004-1-15）

InsertInto#TblUnitDateSwap（UnitCoding,startdate,enddate）

SelectUnitCoding,@i_SFirstdate,@i_EFirstdate

Fromm_UnitsWhereLen（UnitCoding）=12And（UnitCodingLike@UnitCoding）

--插入下半月统计日期（例:

2004-1-16~2004-1-31）

InsertInto#TblUnitDateSwap（UnitCoding,startdate,enddate）

SelectUnitCoding,@i_SSeconddate,@i_ESeconddate

Fromm_UnitsWhereLen（UnitCoding）=12And（UnitCodingLike@UnitCoding）

--插入整月统计日期（例:

2004-1-1~2004-1-31）

InsertInto#TblUnitDateSwap（UnitCoding,startdate,enddate）

SelectUnitCoding,@i_SSeconddate,@i_ESeconddate

Fromm_UnitsWhereLen（UnitCoding）=12And（UnitCodingLike@UnitCoding）

/*插入存储过程开始执行时间（正式执行时屏蔽此功能）*/

INSERTINTOr_ExecTime（UnitCoding,ExecName）VALUES（@UnitCoding,'START'）

Declare@errorcodeint

Declare@i_UnitCodingvarchar（20）

Declare@i_startdatevarchar（10）

Declare@i_enddatevarchar（10）

/*统计插入临时表中的记录数*/

Declare@v_TempTableCountint

Select@v_TempTableCount=count（*）From#TblUnitDateSwap

Declare@iinteger--定义一个临时循环变量

Select@i=1--初始化临时变量为1

--循环每个基层单位（单位代码长度为12），将统计值保存到r_Count对应表中

While（@i<=@v_TempTableCount）--执行循环的条件为临时变量<=临时表记录数

Begin--w01

Select@i_UnitCoding=UnitCoding,@i_startdate=startdate,@i_enddate=enddate

From#TblUnitDateSwap

WhereNumberID=@i

IfNotExists（Select*Fromr_Count1WhereUnitCoding=@i_UnitCodingAndStatDate=@i_startdate）

Begin

INSERTINTOr_Count1

EXEC（'dbo.sp_Count1_Auto_New'+@i_UnitCoding+','+@i_startdate+','+@i_enddate）

End

Else--如果存在已统计的记录，则删除该记录，重新插入最新统计记录（为提高效率，可以屏蔽此功能）

Begin

DeleteFromr_Count1WhereUnitCoding=@i_UnitCodingAndStatDate=@i_startdate

INSERTINTOr_Count1

EXEC（'dbo.sp_Count1_auto'+@i_UnitCoding+','+@i_startdate+','+@i_enddate）

End

--获得错误代码值

Select@errorcode=@@error

/***临时循环变量自增1*/

Select@i=@i+1

End --w01

/*插入存储过程开始执行时间（正式执行时屏蔽此功能）*/

INSERTINTOr_ExecTime（UnitCoding,ExecName）VALUES（@UnitCoding,'END'）

--调试执行循环次数

Declare@jjvarchar（10）

Select@jj=convert（varchar（10）,@i）

Print'执行次数:

'+@jj

/*删除临时表*/

DropTable#TblUnitDateSwap

If（@errorcode=0）

Begin

CommitTransaction

End

Else

Begin

RollbackTransaction

End

RETURN

3、挖掘数据

Create PROCEDUREdbo.sp_Count1_Auto_New

（

@Unitcodingvarchar（20）,

@startdatechar（8）,

@enddate char（8）

）

declare@lengthinteger

declare@leninteger

select@length=len（@Unitcoding）

if@length=4or@length=2

begin

select@len=2

end

elseif@length=12

begin

select@len=0

end

else

begin

select@len=3

end

select @UnitcodingAsUnitCoding,

@startdateAsStatDate,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,99,99,99,0,99,99,99,0）,

dbo.getFCount_inout（Unitcoding,@startdate,@enddate,99,99,99,0,99,99,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,99,0,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,1,0,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,99,0,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,1,0,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,99,1,0,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,1,1,0,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,99,1,0,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,1,1,0,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,99,1,1,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,1,99,99,0,1,1,1,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,99,1,1,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,99,99,0,1,1,1,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,99,99,99,1,99,99,99,0）,

dbo.getFCount_inout（Unitcoding,@startdate,@enddate,99,99,99,1,99,99,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,1,1,1,99,99,99,0）,

dbo.getFCount_inout（Unitcoding,'19000101',@enddate,0,1,1,1,1,99,99,0）

fromm_units

whereUnitcodinglike@Unitcoding+'%'andlen（Unitcoding）=len（@Unitcoding）+@len

RETURN

4、数据分析显示

Selectdbo.getUnitName（Left（UnitCoding,@v_Len））As'单位名称',

Sum（Field1）,

Sum（Field2）,

Sum（Field3）,

Sum（Field4）,

Sum（Field5）,

Sum（Field6）,

Sum（Field7）,

Sum（Field8）,

Sum（Field9）,

Sum（Field10）,

Sum（Field11）,

Sum（Field12）,

Sum（Field13）,

Sum（Field14）,

Sum（Field15）

Fromdbo.u_Count1_Report_New（@Unitcoding,@startdate,@enddate）

GroupByLeft（UnitCoding,@v_Len）,dbo.getUnitName（Left（UnitCoding,@v_Len））

OrderByLeft（UnitCoding,@v_Len）,dbo.getUnitName（Left（UnitCoding,@v_Len））

/*function表

Create FUNCTIONdbo.u_Count1_Report_New

（

@Unitcodingvarchar（20）,

@startdatechar（8）,

@enddate char（8）

）

RETURNS@r_Count1TABLE

（

UnitCodingvarchar（20）,

--StatDatevarchar（10）,

Field1intNOTNULL,

Field2intNOTNULL,

Field3intNOTNULL,

Field4intNOTNULL,

Field5intNOTNULL,

Field6intNOTNULL,

Field7intNOTNULL,

Field8intNOTNULL,

Field9intNOTNULL,

Field10intNOTNULL,

Field11intNOTNULL,

Field12intNOTNULL,

Field13intNOTNULL,

Field14intNOTNULL,

Field15intNOTNULL,

Field16intNOTNULL,

Field17intNOTNULL,

Field18intNOTNULL

）

BEGIN

declare@lengthinteger

declare@v_Leninteger

select@length=len（@Unitcoding）

Select@v_Len=12

INSERT@r_Count1

SelectLeft（UnitCoding,@v_Len）AsUnitCoding,

--StatDate,

Max（Field1）AsField1,

max（dbo.GetOneMonth1（Left（UnitCoding,@v_Len）,@startdate,@enddate,0））AsField2,

Max（Field3）AsField3,

Max（Field4）AsField4,

Max（Field5）AsField5,

Max（Field6）AsField6,

Max（Field7）AsField7,

Max（Field8）AsField8,

Max（Field9）AsField9,

Max（Field10）AsField10,

Max（Field11）AsField11,

Max（Field12）AsField12,

Max（Field13）AsField13,

Max（Field14）AsField14,

Max（Field15）AsField15,

max（dbo.GetOneMonth1（Left（UnitCoding,@v_Len）,@startdate,@enddate,1））AsField16,

Max（Field17）AsField17,

Max（Field18）AsField18

Fromr_Count1

WhereStatDate>=@startdateAndStatDate<=@enddate

AndUnitCodingLike@Unitcoding+'%'

GroupByLeft（UnitCoding,@v_Len）

OrderByLeft（UnitCoding,@v_Len）

RETURN

END

展开阅读全文