我恨天低
移舟泊烟渚，日暮客愁新。野旷天低树，江清月近人。
查看文章
 

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

2008-02-26 14:37:22
要不要说版权所有呢?

这个存储过程比较长
用法

建立名为AA的数据库,其中生成的函数和存储过程用于建立时间矢集快照
SQL code 
EXEC    [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

类别：默认分类 | 浏览(179)
最近读者
网友评论
2
我恨天低
2008-02-26 14:39:49
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


完！
1
我恨天低
2008-02-26 14:38:56
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
发表评论
用户名： 密码： 　会员注册
内　容：
验证码： 请输入下面图中的四位验证码，不区分大小写。
　看不清?