sql server作业用法

 

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

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

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

新增调度以确定要执行的频率!
3、例子

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

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

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

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

三．具体示例

1、 创建表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[r_Count1]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[r_Count1]

GO

CREATE TABLE [dbo].[r_Count1] (

       [UnitCoding] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,

       [StatDate] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,

       [Field1] [int] NOT NULL ,

       [Field2] [int] NOT NULL ,

       [Field3] [int] NOT NULL ,

       [Field4] [int] NOT NULL ,

       [Field5] [int] NOT NULL ,

       [Field6] [int] NOT NULL ,

       [Field7] [int] NOT NULL ,

       [Field8] [int] NOT NULL ,

       [Field9] [int] NOT NULL ,

       [Field10] [int] NOT NULL ,

       [Field11] [int] NOT NULL ,

       [Field12] [int] NOT NULL ,

       [Field13] [int] NOT NULL ,

       [Field14] [int] NOT NULL ,

       [Field15] [int] NOT NULL ,

       [Field16] [int] NOT NULL ,

       [Field17] [int] NOT NULL ,

       [Field18] [int] NOT NULL

) ON [PRIMARY]

GO

2、 处理数据(运用作业的sp)

ALTER     PROCEDURE AutoExec_Count1

(

@UnitCoding varchar(20),

@StatDate datetime

)

AS

 

 

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

Declare @i_SFirstdate varchar(10)

Declare @i_EFirstdate varchar(10)

Declare @i_SSeconddate varchar(10)

Declare @i_ESeconddate varchar(10)

 

Declare @i_getdate datetime

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天

/* 启动事务 */

Begin transaction

 

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

Create Table #TblUnitDateSwap

(

NumberID int IDENTITY (1, 1) NOT NULL ,

UnitCoding varchar(20),

startdate varchar(10),

enddate varchar(10)

)

 

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

Select @UnitCoding = RTrim(@UnitCoding) + '%' --

--插入上半月统计日期(例:2004-1-1~2004-1-15)

Insert Into #TblUnitDateSwap(UnitCoding,startdate,enddate)

Select UnitCoding,@i_SFirstdate,@i_EFirstdate

From m_Units Where Len(UnitCoding)=12 And (UnitCoding Like @UnitCoding)

--插入下半月统计日期(例:2004-1-16~2004-1-31)

Insert Into #TblUnitDateSwap(UnitCoding,startdate,enddate)

Select UnitCoding,@i_SSeconddate,@i_ESeconddate

From m_Units Where Len(UnitCoding)=12 And (UnitCoding Like @UnitCoding)

--插入整月统计日期(例:2004-1-1~2004-1-31)

Insert Into #TblUnitDateSwap(UnitCoding,startdate,enddate)

Select UnitCoding,@i_SSeconddate,@i_ESeconddate

From m_Units Where Len(UnitCoding)=12 And (UnitCoding Like @UnitCoding)

 

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

INSERT INTO r_ExecTime(UnitCoding,ExecName) VALUES(@UnitCoding,'START')

 

Declare @errorcode int

Declare @i_UnitCoding varchar(20)

Declare @i_startdate varchar(10)

Declare @i_enddate varchar(10)

 

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

Declare @v_TempTableCount int

Select @v_TempTableCount = count(*) From #TblUnitDateSwap

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

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

 Where NumberID = @i

        If Not Exists (Select * From r_Count1 Where UnitCoding = @i_UnitCoding And StatDate = @i_startdate)

               Begin

                      INSERT INTO r_Count1

                      EXEC ('dbo.sp_Count1_Auto_New '+@i_UnitCoding+','+@i_startdate+','+@i_enddate)

               End

 

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

               Begin

                      Delete From r_Count1 Where UnitCoding = @i_UnitCoding And StatDate = @i_startdate

                      INSERT INTO r_Count1

                      EXEC ('dbo.sp_Count1_auto '+@i_UnitCoding+','+@i_startdate+','+@i_enddate)

               End

        --获得错误代码值

        Select @errorcode=@@error

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

        Select @i=@i+1

End   -- w01

 

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

INSERT INTO r_ExecTime(UnitCoding,ExecName) VALUES(@UnitCoding,'END')

/*

--调试执行循环次数

Declare @jj varchar(10)

Select @jj = convert(varchar(10),@i)

Print '执行次数:'+@jj

*/

 

/* 删除临时表 */

Drop Table #TblUnitDateSwap

 

If(@errorcode = 0)

 Begin

    Commit Transaction

 End

Else

 Begin

    Rollback Transaction

 End

RETURN

3、 挖掘数据

Create   PROCEDURE dbo.sp_Count1_Auto_New

(

        @Unitcoding varchar(20),

        @startdate char(8) ,

        @enddate   char(8)

)

AS

declare @length integer

 

declare @len integer

select @length=len(@Unitcoding)

if @length = 4 or @length = 2

        begin

               select @len=2

        end

else if @length = 12

        begin

               select @len=0

        end

else

        begin

               select @len=3

        end

select @Unitcoding As UnitCoding,

        @startdate As StatDate,

        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)

from m_units

where Unitcoding like @Unitcoding + '%' and len(Unitcoding) = len(@Unitcoding) + @len

RETURN

4、 数据分析显示

Select dbo.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)

From dbo.u_Count1_Report_New(@Unitcoding,@startdate,@enddate)

Group By Left(UnitCoding,@v_Len),dbo.getUnitName(Left(UnitCoding,@v_Len))

Order By Left(UnitCoding,@v_Len),dbo.getUnitName(Left(UnitCoding,@v_Len))

/*function表

Create FUNCTION dbo.u_Count1_Report_New

(

@Unitcoding varchar(20),

@startdate char(8) ,

@enddate   char(8)

)

RETURNS @r_Count1 TABLE

(

UnitCoding varchar (20),

--StatDate varchar(10),

Field1 int NOT NULL ,

Field2 int NOT NULL ,

Field3 int NOT NULL ,

Field4 int NOT NULL ,

Field5 int NOT NULL ,

Field6 int NOT NULL ,

Field7 int NOT NULL ,

Field8 int NOT NULL ,

Field9 int NOT NULL ,

Field10 int NOT NULL ,

Field11 int NOT NULL ,

Field12 int NOT NULL ,

Field13 int NOT NULL ,

Field14 int NOT NULL ,

Field15 int NOT NULL ,

Field16 int NOT NULL ,

Field17 int NOT NULL ,

Field18 int NOT NULL

)

AS

BEGIN

declare @length integer

declare @v_Len integer

select @length=len(@Unitcoding)

 Select @v_Len = 12

 INSERT @r_Count1

        Select Left(UnitCoding,@v_Len) As UnitCoding,

        --StatDate,

        Max(Field1) As Field1,

        max(dbo.GetOneMonth1(Left(UnitCoding,@v_Len),@startdate,@enddate,0)) As Field2,

        Max(Field3) As Field3,

        Max(Field4) As Field4,

        Max(Field5) As Field5,

        Max(Field6) As Field6,

        Max(Field7) As Field7,

        Max(Field8) As Field8,

        Max(Field9) As Field9,

        Max(Field10) As Field10,

        Max(Field11) As Field11,

        Max(Field12) As Field12,

        Max(Field13) As Field13,

        Max(Field14) As Field14,

        Max(Field15) As Field15,

        max(dbo.GetOneMonth1(Left(UnitCoding,@v_Len),@startdate,@enddate,1)) As Field16,

        Max(Field17) As Field17,

        Max(Field18) As Field18

        From r_Count1

        Where StatDate >=@startdate And StatDate <=@enddate

        And UnitCoding Like @Unitcoding + '%'

        Group By Left(UnitCoding,@v_Len)

        Order By Left(UnitCoding,@v_Len)

   RETURN

END