SQLServer2005 创建矢集快照数据库基本要素

发布时间：2020-12-12 16:00:17 所属栏目：MsSql教程来源：网络整理

导读：Author: 水如烟矢集快照 -- ?============================================= -- ?Author:LzmTW -- ?Create?date:20080101 -- ?Description:创建矢集快照数据库基本要素 -- ?============================================= CREATE ? PROCEDURE ? [ Helper

Author:水如烟

矢集快照

-- ?=============================================
-- ?Author:LzmTW
-- ?Create?date:20080101
-- ?Description:创建矢集快照数据库基本要素
-- ?=============================================
CREATE ? PROCEDURE ? [ Helper ] . [ p_CreateLDateDb ] ?
???? @DatabaseName ? nvarchar ( 20 )
AS
BEGIN
???? SET ?NOCOUNT? ON ;

???? DECLARE ?
????????????? @SQL ? nvarchar ( MAX )
????????????, @IsExists ? bit

???? -- 库若存在，则返回
???? SET ? @SQL ? = ?N '
????USE?[master];
????SELECT?@IsExists?=?
????????CASE
????????????WHEN?[name]?IS?NULL?THEN?0
????????????ELSE?1
????????END
????FROM?sys.databases?WHERE?name?=?@DatabaseName '

???? EXEC ?sp_executesql? @SQL ,?N ' @IsExists?bit?OUT,?@DatabaseName?nvarchar(20) ' ,? @IsExists ?OUT,? @DatabaseName

???? IF ? @IsExists ? = ? 1 ? RETURN ?

???? -- 生成库
???? SET ? @DatabaseName ? = ? QUOTENAME ( @DatabaseName )

???? SET ? @SQL ? = ?N '
????CREATE?DATABASE?@DatabaseName '
????
???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @DatabaseName ' ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL

???? -- 添加[Private]、[Helper]命名空间
???? -- 添加[LDateDefault]默认值
???? -- 添加[LDate]数据类型并设默认值为[LDateDefault]

???? SET ? @SQL = ?N '
????USE?@DatabaseName
????EXEC?sp_executesql?N '' CREATE?SCHEMA?[Private]?AUTHORIZATION?[dbo] ''
????EXEC?sp_executesql?N '' CREATE?SCHEMA?[Helper]?AUTHORIZATION?[dbo] ''
????EXEC?sp_executesql?N '' CREATE?DEFAULT?[dbo].[LDateDefault]?AS?N '''' Current ''''''
????EXEC?sp_executesql?N '' CREATE?TYPE?[dbo].[LDate]?FROM?[nvarchar](19)?NOT?NULL ''
????EXEC?sp_bindefault?@defname=N '' [dbo].[LDateDefault] '' ,?@objname=N '' [dbo].[LDate] '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL

???? -- 创建标量值函数，返回查询矢集快照矢轴采样值的SQL语句
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?sp_executesql?N '' CREATE?FUNCTION?[dbo].[GetCreateLdateFunctionStatement]
(
?????@FunctionNamespace?nvarchar(50)=?N '''' dbo ''''
????,@FunctionName?nvarchar(50)
????,@LDateTableNamespace?nvarchar(50)=?N '''' dbo ''''
????,@LDateTable?nvarchar(50)
????,@LDateTableID?nvarchar(50)
)
RETURNS?nvarchar(MAX)
AS
BEGIN
????DECLARE?@SQL?nvarchar(MAX)
????SET?@SQL?=N ''''
CREATE?FUNCTION?[@FunctionNamespace].[@FunctionName]?
(????
?????@QueryTime????[dbo].[LDate]?=?N '''''''' Current ''''''''
)
RETURNS?TABLE?
AS
RETURN?
(
????SELECT?a.*
????FROM?[@LDateTableNamespace].[@LDateTable]?a
????RIGHT?OUTER?JOIN
????(
????????SELECT?????[@LDateTableID]
????????????????,MIN([Last])AS?QueryTime
????????FROM????[@LDateTableNamespace].[@LDateTable]
????????WHERE???@QueryTime?BETWEEN?[First]?AND?[Last]
????????GROUP?BY?[First],?[Last],?[@LDateTableID]
????)?b
????ON?a.[@LDateTableID]=?b.[@LDateTableID]?AND?a.Last?=?b.QueryTime
)
''''

????SET?@SQL?=?REPLACE(@SQL,?N '''' @FunctionNamespace '''' ,?@FunctionNamespace)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @FunctionName '''' ,?@FunctionName)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @LDateTableNamespace '''' ,?@LDateTableNamespace)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @LDateTableID '''' ,?@LDateTableID)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @LDateTable '''' ,?@LDateTable)

????RETURN?@SQL
END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL
????
???? -- 存储过程,创建时间矢集快照
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?sp_executesql?N '' CREATE?PROCEDURE?[dbo].[CreateLDateTable]
?????@Database?nvarchar(50)
????,@Namespace?nvarchar(50)=?N '''' dbo ''''
????,@Table?nvarchar(50)
????,@TableID?nvarchar(50)????????????????/*属性*/
????,@TableIDType?varchar(20)
????,@Character????nvarchar(50)?=?NULL????????/*特性*/
????,@CharacterType?varchar(20)?=?NULL
AS
BEGIN
????DECLARE?@SQL?nvarchar(MAX)
????SET?@SQL?=N ''''
USE?@Database ''''

????IF?@Character?IS?NULL?OR?LEN(@Character)?=?0
????????SET?@SQL?=?@SQL?+?N ''''

????CREATE?TABLE?[@Namespace].[@Table](
????????[ID]?int?IDENTITY(1,1),
????????[@TableID]?@TableIDType?NOT?NULL,
????????[Last]?[dbo].[LDate]?NOT?NULL,
????????[First]?[dbo].[LDate]?NOT?NULL,
?????CONSTRAINT?[PK_@Table]?PRIMARY?KEY?CLUSTERED?
????(
????????[@TableID]?ASC,
????????[Last]?ASC
????)WITH?(PAD_INDEX??=?OFF,?IGNORE_DUP_KEY?=?OFF)?ON?[PRIMARY]
????)?ON?[PRIMARY] ''''
????ELSE
????????SET?@SQL?=?@SQL?+?N ''''

????CREATE?TABLE?[@Namespace].[@Table](
????????[ID]?int?IDENTITY(1,
????????[@Character]?@CharacterType,?IGNORE_DUP_KEY?=?OFF)?ON?[PRIMARY]
????)?ON?[PRIMARY] ''''

????SET?@SQL?=?@SQL?+?N ''''

EXEC?sys.sp_bindefault?@defname=N '''''''' [dbo].[LDateDefault] '''''''' ,?@objname=N '''''''' [@Namespace].[@Table].[Last] '''''''' ?,?@futureonly= '''''''' futureonly ''''''''

EXEC?sys.sp_bindefault?@defname=N '''''''' [dbo].[LDateDefault] '''''''' ,?@objname=N '''''''' [@Namespace].[@Table].[First] '''''''' ?,?@futureonly= '''''''' futureonly ''''''''

ALTER?TABLE?[@Namespace].[@Table]??WITH?CHECK?ADD??CONSTRAINT?[CK_@Table_First]?CHECK??((isdate([First])=(1)))

ALTER?TABLE?[@Namespace].[@Table]?CHECK?CONSTRAINT?[CK_@Table_First]

ALTER?TABLE?[@Namespace].[@Table]??WITH?CHECK?ADD??CONSTRAINT?[CK_@Table_Last]?CHECK??((isdate([Last])=(1)?OR?[Last]= '''''''' Current '''''''' ?AND?[Last]?>=?[First]?))

ALTER?TABLE?[@Namespace].[@Table]?CHECK?CONSTRAINT?[CK_@Table_Last]
''''
????SET?@SQL?=?REPLACE(@SQL,?N '''' @Database '''' ,?@Database)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @Namespace '''' ,?@Namespace)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @TableIDType '''' ,?@TableIDType)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @TableID '''' ,?@TableID)
????SET?@SQL?=?REPLACE(@SQL,?N '''' @Table '''' ,?@Table)

????IF?NOT(?@Character?IS?NULL?OR?LEN(@Character)?=?0)
????BEGIN
????????SET?@SQL?=?REPLACE(@SQL,?N '''' @CharacterType '''' ,?@CharacterType)
????????SET?@SQL?=?REPLACE(@SQL,?N '''' @Character '''' ,?@Character)
????END

????EXEC(@SQL)
END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL

???? -- 创建存储过程，对时间矢集快照进行数据行更新
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?sp_executesql?N '' CREATE?PROCEDURE?[dbo].[CreateLDateTableRowUpdate]
?????@Database?nvarchar(50)
????,@TableID?nvarchar(50)
????,@Character?nvarchar(20)
????,@CharacterType?varchar(20)

AS
BEGIN
????DECLARE?
????????@SQL?nvarchar(MAX)

????SET?@SQL?=?N ''''
USE?%Database%

EXEC?sp_executesql?N ''''''''
--?=====================================================================
--?Author:????LzmTW
--?Create?date:?20080105
--?Description:????时间矢集快照单行数据的更新
--?@%TableID%:属性
--?@%Character%:属性特性值
--?@Date:采样时间
--?@IsBreak:是否撤销属性
--?@LDateUnit:时间矢量轴单位,年、月、日、时、分、秒(yy,mm,dd,hh,mi,ss)
--?=====================================================================
CREATE?PROCEDURE?[%Namespace%].[p_%Table%RowUpdate]
?????@%TableID%?%TableIDType%
????,@%Character%?%CharacterType%?=?NULL
????,@Date?Datetime
????,@IsBreak?bit?=?0
????,@LDateUnit?char(2)?=? '''''''''''''''' dd ''''''''''''''''
AS
BEGIN
????SET?NOCOUNT?ON;

????DECLARE
?????????@MaxFirst?LDate
????????,@LDate?LDate

????????,@Current%Character%?%CharacterType%
????????,@CurrentFirst?varchar(19)
????????,@CurrentID?int

????????,@PreLDate?LDate
????????,@Pre%Character%?%CharacterType%
????????,@PreID?int
????????
????SET?@LDate=?[dbo].[DateToLDate](@Date,?0,?@LDateUnit)
????SET?@PreLDate?=?[dbo].[DateToLDate](@Date,?-1,?@LDateUnit)

????--当前要更新的数据其采样点时间不能小于数据集中最后的采样点时间
????SELECT?@MaxFirst?=?MAX(First)
????FROM?[%Namespace%].[%Table%]

????IF?NOT?@MaxFirst?IS?NULL?AND?@MaxFirst?>?@LDate
????????RETURN?-1?????

????--撤销属性
????IF?@IsBreak?=?1
????BEGIN
????????SET?@CurrentID?=?NULL

????????SELECT
????????????@CurrentID?=?[ID]
????????FROM?[%Namespace%].[f_%Table%](@LDate)
????????WHERE?%TableID%?=?@%TableID%

????????IF?@CurrentID?IS?NULL
????????????RETURN?1

????????UPDATE?[%Namespace%].[%Table%]
????????SET?Last?=?@PreLDate
????????WHERE?[ID]?=?@CurrentID

????????RETURN?0
????END

????--如果是新属性，先检查是否回滚上次的撤销属性(即维持上一特性值)，若不是回滚，则插入新属性，包括其特性值
????IF?NOT?EXISTS(
????????SELECT?%TableID%?
????????FROM?[%Namespace%].[f_%Table%](@LDate)?
????????WHERE?%TableID%?=?@%TableID%?)
????BEGIN
????????SET?@Pre%Character%?=?NULL
????????SET?@PreID?=?NULL

????????SELECT
?????????????@Pre%Character%?=?%Character%
????????????,@PreID?=?[ID]
????????FROM?[%Namespace%].[f_%Table%](@PreLDate)
????????WHERE?%TableID%?=?@%TableID%

????????IF?(NOT?@PreID?IS?NULL)?AND?(@Pre%Character%?=?@%Character%?OR
????????(@Pre%Character%?IS?NULL?AND?@%Character%?IS?NULL))
????????BEGIN
????????????UPDATE?[%Namespace%].[%Table%]?/*回滚上次的撤销属性*/
????????????SET?Last?=? '''''''''''''''' Current ''''''''''''''''
????????????WHERE?[ID]?=?@PreID

????????????RETURN?0
????????END

????????INSERT?INTO?[%Namespace%].[%Table%]?/*插入新属性*/
????????(%TableID%,?Last,?First,?%Character%)
????????VALUES(@%TableID%,? '''''''''''''''' Current '''''''''''''''' ,?@LDate,?@%Character%)

????????RETURN?0
????END
????
????--以下允许更新最新的特性值
????SET?@CurrentID?=?NULL
????SET?@Current%Character%?=?NULL
????SET?@CurrentFirst?=?NULL

????SELECT
?????????@Current%Character%?=?%Character%
????????,@CurrentFirst?=?First
????????,@CurrentID?=?[ID]
????FROM?[%Namespace%].[f_%Table%](@LDate)
????WHERE?%TableID%?=?@%TableID%

????IF?@Current%Character%?=?@%Character%?OR?
????????(@Current%Character%?IS?NULL?AND?@%Character%?IS?NULL)
????????RETURN?0????/*如果特性值相等，不作处理*/

????SET?@Pre%Character%?=?NULL
????SET?@PreID?=?NULL

????SELECT
?????????@Pre%Character%?=?%Character%
????????,@PreID?=?[ID]
????FROM?[%Namespace%].[f_%Table%](@PreLDate)
????WHERE?%TableID%?=?@%TableID%????

????IF?(NOT?@PreID?IS?NULL)?AND?@LDate?=?@CurrentFirst?AND?(@Pre%Character%?=?@%Character%?OR
????(@Pre%Character%?IS?NULL?AND?@%Character%?IS?NULL))
????BEGIN?/*重新维持上一采样点的特性值*/
????????DELETE?FROM?[%Namespace%].[%Table%]
????????WHERE?[ID]?=?@CurrentID

????????UPDATE?[%Namespace%].[%Table%]?
????????SET?Last?=? '''''''''''''''' Current ''''''''''''''''
????????WHERE?[ID]?=?@PreID

????????RETURN?0
????END

????IF?@LDate?=?@CurrentFirst?/*时间相同，更新最新的特性值*/
????BEGIN
????????UPDATE?[%Namespace%].[%Table%]?
????????SET?%Character%?=?@%Character%
????????WHERE?[ID]?=?@CurrentID

????????RETURN?0
????END

????IF?@LDate?<>?@CurrentFirst?/*时间不同，插入新属性*/
????BEGIN
????????UPDATE?[%Namespace%].[%Table%]?
????????SET?Last?=?@PreLDate
????????WHERE?[ID]?=?@CurrentID

????????INSERT?INTO?[%Namespace%].[%Table%]?
????????(%TableID%,?@%Character%)????????

????????RETURN?0
????END
END ''''''''''''

????SET?@SQL?=?REPLACE(@SQL,?N '''' %CharacterType% '''' ,?@CharacterType)
????SET?@SQL?=?REPLACE(@SQL,?N '''' %Character% '''' ,?@Character)
????SET?@SQL?=?REPLACE(@SQL,?N '''' %TableIDType% '''' ,?N '''' %TableID% '''' ,?N '''' %Table% '''' ,?@Table)
????SET?@SQL?=?REPLACE(@SQL,?N '''' %Namespace% '''' ,?N '''' %Database% '''' ,?@Database)

????EXEC?sp_executesql?@SQL
END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL

???? -- 创建存储过程，可以在本地数据库创建时间矢集快照，及其查询函数，数据行更新存储过程
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?dbo.sp_executesql?N '' CREATE?PROCEDURE?[dbo].[CurrentDbCreateLDateTable]
?????@Namespace?nvarchar(50)=?N '''' dbo ''''
????,@Character?nvarchar(50)?=?NULL
????,@CharacterType?varchar(20)?=?NULL
AS
BEGIN
????DECLARE?
?????????@Database?nvarchar(50)
????????,@SQL?nvarchar(MAX)

????SET?@Database?=?N '''' @DatabaseName ''''

????EXEC?[dbo].[CreateLDateTable]?
?????????@Database
????????,@Namespace
????????,@Table
????????,@TableID
????????,@TableIDType
????????,@Character
????????,@CharacterType

????SELECT?@SQL?=?[dbo].[GetCreateLdateFunctionStatement]?(
?????????@Namespace
????????,N '''' f_ '''' ?+?@Table
????????,@TableID)
????
????EXEC(@SQL)

????IF?NOT?@Character?IS?NULL
????EXEC????[dbo].[CreateLDateTableRowUpdate]
????????????@Database,
????????????@Namespace,
????????????@Table,
????????????@TableID,
????????????@TableIDType,
????????????@Character,
????????????@CharacterType?

END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL

???? -- 创建函数,将时间转换为时间矢轴上的点值
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?sp_executesql?N '' --?=================================================================
--?Author:????????LzmTW
--?Create?date:?20080105
--?Description:????将时间转换为时间矢轴上的点值
--?@Date:?采样时间
--?@Add:?增加值
--?@Unit:?矢轴单位，年、月、日、时、分、秒(yy,ss)
--?=================================================================
CREATE?FUNCTION?[dbo].[DateToLDate]
(
?????@Date?datetime
????,@Add?int?=?0
????,@Unit?char(2)?=? '''' dd ''''
)
RETURNS?varchar(19)
AS
BEGIN
????DECLARE?
?????????@Result?varchar(19)
????????,@NowDate?datetime

????SET?@NowDate?=
????????CASE?@Unit
????????????WHEN? '''' yy '''' ?THEN?DATEADD(yy,?@Add,?@Date)
????????????WHEN? '''' mm '''' ?THEN?DATEADD(mm,?@Date)
????????????WHEN? '''' dd '''' ?THEN?DATEADD(dd,?@Date)
????????????WHEN? '''' hh '''' ?THEN?DATEADD(hh,?@Date)
????????????WHEN? '''' mi '''' ?THEN?DATEADD(mi,?@Date)
????????????WHEN? '''' ss '''' ?THEN?DATEADD(ss,?@Date)
????????????ELSE?NULL
????????END

????IF?@Unit?IN?( '''' yy '''' ,? '''' mm '''' ,? '''' dd '''' )
????????SET?@Result?=?CONVERT(varchar(8),?@NowDate,?112)

????IF?@Unit?IN?( '''' hh '''' ,? '''' mi '''' ,? '''' ss '''' )
????????SET?@Result?=REPLACE(CONVERT(varchar(19),?120),? '''' - '''' ,? '''''''' )

????IF?@Unit?IN?( '''' hh '''' ,? '''' mi '''' )
????????SET?@Result?=?STUFF(@Result,?16,?2,? '''' 00 '''' )

????IF?@Unit?IN?( '''' hh '''' )
????????SET?@Result?=?STUFF(@Result,?13,? '''' 00 '''' )

????RETURN?@Result
END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,将时间矢轴上的点值转换为时间
???? SET ? @SQL = ?N '
????USE?@DatabaseName

EXEC?sp_executesql?N '' --?=================================================================
--?Author:????????LzmTW
--?Create?date:?20080105
--?Description:????将时间矢轴上的点值转换为时间
--?@LDate:?矢轴点值
--?@Add:?增加值
--?@Unit:?矢轴单位，年、月、日、时、分、秒(yy,ss)
--?=================================================================
CREATE?FUNCTION?[dbo].[LDateToDate]
(
?????@LDate?varchar(19)
????,@Unit?char(2)?=? '''' dd ''''
)
RETURNS?datetime
AS
BEGIN
????DECLARE?
?????????@Result?datetime

????SET?@Result?=
????????CASE?@Unit
????????????WHEN? '''' yy '''' ?THEN?DATEADD(yy,?@LDate)
????????????WHEN? '''' mm '''' ?THEN?DATEADD(mm,?@LDate)
????????????WHEN? '''' dd '''' ?THEN?DATEADD(dd,?@LDate)
????????????WHEN? '''' hh '''' ?THEN?DATEADD(hh,?@LDate)
????????????WHEN? '''' mi '''' ?THEN?DATEADD(mi,?@LDate)
????????????WHEN? '''' ss '''' ?THEN?DATEADD(ss,?@LDate)
????????????ELSE? '''' Current '''' ?/*非有效值发出错误*/
????????END

????RETURN?@Result
END '''

???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @DatabaseName )

???? EXEC ?sp_executesql? @SQL
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!