这几个存储过程写得也不错

这个存储过程比较长
用法

建立名为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

完！

posted on 2008-08-18 00:25 Sanle 阅读(446) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

博学、审问、慎思、明辨、笃行