注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Computer Science

I Pressed My Words Here. Will You Read ?

 
 
 

日志

 
 

SQL Server 中模拟 Oracle下的Sequence(表序列)  

2011-09-08 09:31:46|  分类: My Projects |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
这里,我们通过创建表的序列表来实现,序列表的名称为表名后加上$sequence字段。
首先我们需要准备一个序列表的自定义表类型,利用其中的自动增长列来记录表的序列长度:
USE [databaseName]
GO

/****** Object:  UserDefinedTableType [dbo].[t_sequence_table]    Script Date: 08/18/2011 10:42:47 ******/
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N't_sequence_table' AND ss.name = N'dbo')
DROP TYPE [dbo].[t_sequence_table]
GO

USE [databaseName]
GO

/****** Object:  UserDefinedTableType [dbo].[t_sequence_table]    Script Date: 08/18/2011 10:42:47 ******/
CREATE TYPE [dbo].[t_sequence_table] AS TABLE(
    [id] [numeric](38, 0) IDENTITY(0,1) NOT NULL,
    [nvarchar] [nvarchar](max) COLLATE Chinese_PRC_CS_AI NULL,
    PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO


然后,我们需要一个检查目标表对应的的sequence表是否存在,如果不存在就创建它。所以,实现这个逻辑的存储过程命名为:
p_sequence_table_must_be_exist
USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_sequence_table_must_be_exist]    Script Date: 08/18/2011 10:52:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_sequence_table_must_be_exist]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[p_sequence_table_must_be_exist]
GO

USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_sequence_table_must_be_exist]    Script Date: 08/18/2011 10:52:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Will
-- Create date: 09072011
-- Description:    check if the sequence table of the target table is exist
-- =============================================
CREATE PROCEDURE [dbo].[p_sequence_table_must_be_exist]
    @tableName NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @count int = 0
        SET @tableName = UPPER(LTRIM(RTRIM(@tableName)) + '$sequence')
        SELECT @count = COUNT(*)
            FROM INFORMATION_SCHEMA.TABLES
                WHERE (UPPER(TABLE_CATALOG) + '.' + UPPER(TABLE_SCHEMA) + '.' + TABLE_NAME = @tableName
                    OR UPPER(TABLE_SCHEMA) +'.'+ TABLE_NAME = @tableName
                        OR TABLE_NAME = @tableName )
        IF @count IS NULL OR @count = 0
            EXEC('DECLARE @seqTable t_sequence_table SELECT * INTO ' + @tableName + ' FROM @seqTable
                INSERT INTO ' + @tableName + ' VALUES(1) ')
        RETURN @count
    END TRY
    BEGIN CATCH
        RETURN -1
    END CATCH
END

GO


 接着,提供一个初始化序列表的存储过程,用于在序列表已经存在时,将序列设置为表中数据行数:
USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_init_sequence_table_id_value]    Script Date: 08/18/2011 10:25:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_init_sequence_table_id_value]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[p_init_sequence_table_id_value]
GO

USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_init_sequence_table_id_value]    Script Date: 08/18/2011 10:25:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Will
-- Create date: 09072011
-- Description:    init sequence table id value
-- =============================================
CREATE PROCEDURE [dbo].[p_init_sequence_table_id_value]
    @tableName NVARCHAR(MAX)
    ,@idColName NVARCHAR(MAX)
AS
BEGIN

    SET NOCOUNT ON;
    SET @tableName = UPPER(LTRIM(RTRIM(@tableName)))
    DECLARE @count NUMERIC(38,0)
        ,@seqTableName NVARCHAR(MAX) = UPPER(@tableName + '$sequence')
        ,@countSql NVARCHAR(MAX) = ' SELECT @v_out = MAX(' + @idColName + ') FROM ' + @tableName + ' '
    BEGIN TRY
        /**计算序列表起始id值**/
        EXEC sp_executesql @countSql, N'@v_out NUMERIC(38,0) OUTPUT', @count OUTPUT
        --PRINT @count
        IF @count IS NULL
            SET @count = 0
        EXEC('DROP TABLE ' + @seqTableName + '')
        EXEC [dbo].[p_sequence_table_must_be_exist] @tableName
        EXEC('SET IDENTITY_INSERT ' + @seqTableName + ' ON
            INSERT INTO ' + @seqTableName + ' (id, nvarchar) VALUES(' + @count + ', 1)
             DELET FROM TABLE ' + @seqTableName + ' WITH (READPAST)'
                SET IDENTITY_INSERT ' + @seqTableName + ' OFF ')
        RETURN 0
    END TRY
    BEGIN CATCH
        RETURN -1
    END CATCH
END

GO


最后是获取表的序列值的存储过程:
USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_get_sequence_table_next_value]    Script Date: 08/18/2011 11:18:02 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_get_sequence_table_next_value]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[p_get_sequence_table_next_value]
GO

USE [databaseName]
GO

/****** Object:  StoredProcedure [dbo].[p_get_sequence_table_next_value]    Script Date: 08/18/2011 11:18:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Will
-- Create date: 09082011
-- Description:    get sequence table next value
-- =============================================
CREATE PROCEDURE [dbo].[p_get_sequence_table_next_value]
    @tableName NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @nextValue NUMERIC(38,0) = -1
    SET @tableName = UPPER(LTRIM(RTRIM(@tableName + '$sequence')))
    BEGIN TRY
        EXECUTE('INSERT INTO ' + @tableName + ' VALUES (1); DELETE FROM ' + @tableName + ' WITH (READPAST)')
        SELECT @nextValue = IDENT_CURRENT(@tableName)
        RETURN @nextValue
    END TRY
    BEGIN CATCH
        RETURN @nextValue
    END CATCH
END

GO


这样,便通过建立序列表来实现了表的序列功能。

----------------------------
2011-9-9 补充
由于这是一个存储过程模拟表序列。在SELECT 这样语句中无法嵌入。为此提供了一个获取序列表当前序列值的函数,与获取下一个值的存储过程配合使用,能够异步模拟SELECT sequenceTable.nextVal ... 的情景。
现,介绍f_get_sequence_table_current_value函数:
USE [databaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[f_get_sequence_table_current_value]    Script Date: 09/09/2011 16:47:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_get_sequence_table_current_value]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[f_get_sequence_table_current_value]
GO

USE [databaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[f_get_sequence_table_current_value]    Script Date: 09/09/2011 16:47:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Will
-- Create date: 09092001
-- Description:    Get Current Sequence Value
-- =============================================
CREATE FUNCTION [dbo].[f_get_sequence_table_current_value]
(
    @tableName NVARCHAR(MAX) -- table name
)
RETURNS NUMERIC(38,0)
AS
BEGIN
    SET @tableName = UPPER(LTRIM(RTRIM(@tableName)) + '$sequence')
    RETURN IDENT_CURRENT(@tableName)
END

GO



参考资料:
NEXTVAL 和 CURRVAL 运算符(IDS)http://hi.baidu.com/yanguang1670/blog/item/06d090450ac82925cffca31e.html

Get sequence next value http://www.java2s.com/Code/PostgreSQL/Sequence/Getsequencenextvalue.htm
  评论这张
 
阅读(1626)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017