这几个存储过程写得也不错
要不要说版权所有呢?
这个存储过程比较长
用法
建立名为AA的数据库,其中生成的函数和存储过程用于建立时间矢集快照
SQL codeEXEC [Helper].[p_CreateLDateDb]
@DatabaseName = N'AA'
记住是SqlServer2005的
1、
SQL code-- =============================================
-- 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, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
2、
SQL code --创建标量值函数，返回查询矢集快照矢轴采样值的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, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
3、
SQL code --存储过程,创建时间矢集快照
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,1),
[@TableID] @TableIDType NOT NULL,
[Last] [dbo].[LDate] NOT NULL,
[First] [dbo].[LDate] NOT NULL,
[@Character] @CharacterType,
CONSTRAINT [PK_@Table] PRIMARY KEY CLUSTERED
(
[@TableID] ASC,
[Last] ASC
)WITH (PAD_INDEX = OFF, 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, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
4、
SQL code --创建存储过程，对时间矢集快照进行数据行更新
SET @SQL= N'
USE @DatabaseName
EXEC sp_executesql N''CREATE PROCEDURE [dbo].[CreateLDateTableRowUpdate]
@Database nvarchar(50)
,@Namespace nvarchar(50)= N''''dbo''''
,@Table nvarchar(50)
,@TableID nvarchar(50)
,@TableIDType varchar(20)
,@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%, Last, First, %Character%)
VALUES(@%TableID%, ''''''''''''''''Current'''''''''''''''', @LDate, @%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%'''', @TableIDType)
SET @SQL = REPLACE(@SQL, N''''%TableID%'''', @TableID)
SET @SQL = REPLACE(@SQL, N''''%Table%'''', @Table)
SET @SQL = REPLACE(@SQL, N''''%Namespace%'''', @Namespace)
SET @SQL = REPLACE(@SQL, N''''%Database%'''', @Database)
EXEC sp_executesql @SQL
END'''
SET @SQL = REPLACE(@SQL, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
5、
SQL code --创建存储过程，可以在本地数据库创建时间矢集快照，及其查询函数，数据行更新存储过程
SET @SQL= N'
USE @DatabaseName
EXEC dbo.sp_executesql N''CREATE PROCEDURE [dbo].[CurrentDbCreateLDateTable]
@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
@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
,@Namespace
,@Table
,@TableID)
EXEC(@SQL)
IF NOT @Character IS NULL
EXEC [dbo].[CreateLDateTableRowUpdate]
@Database,
@Namespace,
@Table,
@TableID,
@TableIDType,
@Character,
@CharacterType
END'''
SET @SQL = REPLACE(@SQL, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
6、
SQL code --创建函数,将时间转换为时间矢轴上的点值
SET @SQL= N'
USE @DatabaseName
EXEC sp_executesql N''-- =================================================================
-- Author: LzmTW
-- Create date: 20080105
-- Description: 将时间转换为时间矢轴上的点值
-- @Date: 采样时间
-- @Add: 增加值
-- @Unit: 矢轴单位，年、月、日、时、分、秒(yy,mm,dd,hh,mi,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, @Add, @Date)
WHEN ''''dd'''' THEN DATEADD(dd, @Add, @Date)
WHEN ''''hh'''' THEN DATEADD(hh, @Add, @Date)
WHEN ''''mi'''' THEN DATEADD(mi, @Add, @Date)
WHEN ''''ss'''' THEN DATEADD(ss, @Add, @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), @NowDate, 120), ''''-'''', '''''''')
IF @Unit IN (''''hh'''', ''''mi'''')
SET @Result = STUFF(@Result, 16, 2, ''''00'''')
IF @Unit IN (''''hh'''')
SET @Result = STUFF(@Result, 13, 2, ''''00'''')
RETURN @Result
END'''
SET @SQL = REPLACE(@SQL, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
--创建函数,将时间矢轴上的点值转换为时间
SET @SQL= N'
USE @DatabaseName
EXEC sp_executesql N''-- =================================================================
-- Author: LzmTW
-- Create date: 20080105
-- Description: 将时间矢轴上的点值转换为时间
-- @LDate: 矢轴点值
-- @Add: 增加值
-- @Unit: 矢轴单位，年、月、日、时、分、秒(yy,mm,dd,hh,mi,ss)
-- =================================================================
CREATE FUNCTION [dbo].[LDateToDate]
(
@LDate varchar(19)
,@Add int = 0
,@Unit char(2) = ''''dd''''
)
RETURNS datetime
AS
BEGIN
DECLARE
@Result datetime
SET @Result =
CASE @Unit
WHEN ''''yy'''' THEN DATEADD(yy, @Add, @LDate)
WHEN ''''mm'''' THEN DATEADD(mm, @Add, @LDate)
WHEN ''''dd'''' THEN DATEADD(dd, @Add, @LDate)
WHEN ''''hh'''' THEN DATEADD(hh, @Add, @LDate)
WHEN ''''mi'''' THEN DATEADD(mi, @Add, @LDate)
WHEN ''''ss'''' THEN DATEADD(ss, @Add, @LDate)
ELSE ''''Current'''' /*非有效值发出错误*/
END
RETURN @Result
END'''
SET @SQL = REPLACE(@SQL, N'@DatabaseName', @DatabaseName)
EXEC sp_executesql @SQL
END
完！
