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