一．作业的运用（自动更新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