通用数据表整理

1. 系统类别代码表 - 通常应用于下拉列表等基础信息

  1.1 类别代码表定义

CREATE TABLE [dbo].[SysTypeCode](
	[Code] [varchar](3) NOT NULL,
	[NameCN] [nvarchar](50) NULL,
	[NameEn] [varchar](100) NULL,
	[Enabled] [char](1) NOT NULL,
	[Remark] [nvarchar](200) NULL,
 CONSTRAINT [PK_BAS_CodeProfile] PRIMARY KEY NONCLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别代码[代码ID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode', @level2type=N'COLUMN',@level2name=N'Code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'中文名称[代码名称]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode', @level2type=N'COLUMN',@level2name=N'NameCN'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'英文名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode', @level2type=N'COLUMN',@level2name=N'NameEn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'启用状态(Y/N)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode', @level2type=N'COLUMN',@level2name=N'Enabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'类别管理信息表[系统代码选项类别]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCode'
GO

ALTER TABLE [dbo].[SysTypeCode] ADD  CONSTRAINT [DF_BAS_CodeProfile_Modify_Flag]  DEFAULT ('1') FOR [Enabled]
GO

  1.2 类别代码项表定义

CREATE TABLE [dbo].[SysTypeCodeItem](
	[TypeCode] [varchar](3) NOT NULL,
	[Code] [varchar](3) NOT NULL,
	[NameCn] [nvarchar](200) NOT NULL,
	[NameEn] [varchar](200) NULL,
	[Sequence] [int] NOT NULL,
	[Enabled] [char](1) NOT NULL,
	[Remark] [nvarchar](200) NULL,
 CONSTRAINT [PK_BAS_Codes] PRIMARY KEY CLUSTERED 
(
	[TypeCode] ASC,
	[Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别代码(对应类别代码表中的TypeCode)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'TypeCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别项代码(一般用于下拉中的选项值)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'Code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'中文名称[选项内容]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'NameCn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'英文名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'NameEn'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'展示序号(从小到大排序)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'Sequence'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'有效状态(Y/N)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'Enabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'类别详细信息表【选项内容】' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysTypeCodeItem'
GO

ALTER TABLE [dbo].[SysTypeCodeItem] ADD  CONSTRAINT [DF__BAS_Codes__UDF_O__70A8B9AE]  DEFAULT ('1') FOR [Enabled]
GO

2.生成流水号的表

CREATE TABLE [dbo].[SysRulSequence](
[SeqCode] [varchar](60) NOT NULL,
[Descr] [varchar](60) NULL,
[SeqType] [char](2) NULL,
[NowSeqValue] [bigint] NULL,
[CreateTime] [datetime] NOT NULL,
[MaxValue] [bigint] NULL,
[CreateBy] [varchar](50) NULL,
[EditTime] [datetime] NOT NULL,
[EditBy] [varchar](50) NULL,
[Length] [int] NULL,
[DataFormat] [varchar](50) NULL,
[DateMax] [varchar](10) NULL,
[Status] [varchar](1) NULL,
[InitValue] [varchar](10) NULL,
[ResetType] [varchar](50) NULL,
[IsRunning] [char](1) NULL,
 CONSTRAINT [PK_SYS_Sequence] PRIMARY KEY NONCLUSTERED 
(
[SeqCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号规则代码[规则代码]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'SeqCode'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'描述[编码规则描述]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'Descr'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'代码类型(暂时不用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'SeqType'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前值[当前编号]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'NowSeqValue'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大值(暂时不用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'MaxValue'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'长度(暂时不用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'Length'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规则格式[规则设定]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'DataFormat'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期最大值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'DateMax'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可编辑（0 - 系统代码，不可编辑，1 - 可编辑）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'Status'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'归零值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'InitValue'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'归零方式 (1 不归零 2 按日归零   3 按月归零   4按年归零)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'ResetType'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'正在运行标记，控制并发(1-正在运行，2-没在运行)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence', @level2type=N'COLUMN',@level2name=N'IsRunning'
GO


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'编号规则' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysRulSequence'
GO


ALTER TABLE [dbo].[SysRulSequence] ADD  CONSTRAINT [DF_RUL_Sequence_IsRunning]  DEFAULT ((1)) FOR [IsRunning]
GO

使用流水号辅助的存储过程：

CREATE PROCEDURE [dbo].[ProcGetSequence]
		  @SeqCode varchar(60),				-- 规则代码
		  @ReturnNum Varchar(40) OUTPUT,	-- 返回的流水号
		  @MessageCode varchar(800) OUTPUT	-- 异常消息等

AS
/* Exec ProcGetSequence 'ReportNo','',''
*****************************************************************
功能描述：	获取数据表的主键流水号(INV, ASN, SO...)
	
主要思路：	1.取得最新流水号信息
			2.把所有固定的规则信息替换成具体值，其他保持不变
			eg:
			规则为：		ASN<YYYY><YY><MM><XXX>ASN
			当前日期为：	20130630
			当前流水号为：12
			最终流水号为：ASN201306013ASN
			
******************************************************************
*/


/*
* SET NOCOUNT ON 的作用:
* 不返回受影响行数
* 存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
* */
SET NOCOUNT ON

DECLARE @SeqNowNumStr VARCHAR(20)	--当前值字符类型	
DECLARE @SeqNowNum BIGINT			--当前值	
DECLARE @year CHAR(4)				--年 YYYY
DECLARE @month CHAR(2)				--月 MM
DECLARE @day CHAR(2)				--日 DD
DECLARE @Length INT					--流水号长度
DECLARE @DataFormat VARCHAR(50)		--流水号规则
DECLARE @IniValue INT				--归零值
DECLARE @ResetType VARCHAR(10)		--归零方式
DECLARE @LastDate	CHAR(8)			--日期最大值			
DECLARE @WorkFLowStr VARCHAR(20) 	--前一次调用流水号时的日期值
DECLARE @DataNow CHAR(8)			--当前日期
DECLARE @i INT						--转换变量,作用参照代码上下文
	

/*
* SET XACT_ABORT ON 的作用:
* 存储中的某个地方出了问题，整个事务中的语句都会回滚
* */
SET XACT_ABORT ON
BEGIN TRY

	/* 初始化变量 */
	 SET @MessageCode='999'
	 SET @ReturnNum = '0'
	 SET @Length=0
	 SET @SeqNowNum =0;
	 SET @DataNow=CONVERT(CHAR(8),GETDATE(),112) --得到 20130704 的时间格式
	 SET @year=SUBSTRING(@DataNow,1,4)
	 SET @month =SUBSTRING(@DataNow,5,2)
	 SET @day =SUBSTRING(@DataNow,7,2)
	 
	 Set @i=1 
	 
	 /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 Start*******/           
	 BEGIN TRANSACTION 
		 wait:
		 Update dbo.SysRulSequence Set [IsRunning]='2' where SeqCode=@SeqCode and IsRunning='1'
		If @@Rowcount=0	
		Begin
			Waitfor Delay '00:00:01'
			Set @i=@i+1
			If @i<6 goto wait
		End
	  
	 COMMIT TRANSACTION   
	 /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 End*******/           

	Select @Length = [Length],@SeqNowNum=NowSeqValue,@LastDate=DateMax,@DataFormat=DataFormat
		,@ResetType=ResetType,@IniValue =InitValue
			From dbo.SysRulSequence where SeqCode=@SeqCode
						if @SeqNowNum=0  --当前值正常情况下不可能是0
						begin
						   Set @MessageCode='100'  --当前值 错误代码
						   select @MessageCode
						   return
						END
						--@ResetType=1 不归零 2 按日归零   3 按月归零   4按年归零
	If (@ResetType=2 and @DataNow<>@LastDate  AND @IniValue>0)
		OR (@ResetType=3 and @year+@month<>SUBSTRING(@LastDate,1,6) AND @IniValue>0)
		OR (@ResetType=4 and @year<>SUBSTRING(@LastDate,1,4) AND @IniValue>0 )
	   BEGIN
   		SET @SeqNowNum=@IniValue
	   END 
	 SET  @i=@Length --@i 此时表示流水号的总长度
	      
	 /***********拼流水号格式 Start*******/           
	 SET @WorkFLowStr='<'
	 WHILE @Length>0 
	 BEGIN
 		SET @WorkFLowStr=@WorkFLowStr+'X'
 		SET @Length=@Length-1
	 END  
	 SET @WorkFLowStr=@WorkFLowStr+'>' 
	 /***********拼流水号格式 End*******/
	 
	 set @SeqNowNumStr=CONVERT(VARCHAR(20),@SeqNowNum)
	 SET @Length=@i-len(@SeqNowNumStr)  --@Length 要补零的位数(eg:@SeqNowNumStr=148 当前流水号是五位，最后流水号为00148，00 就是需要补的两位)
	 
	 /***********补零操作 Start*******/           
	 WHILE @Length>0 
	 BEGIN
 		SET @SeqNowNumStr='0'+@SeqNowNumStr
 		SET @Length=@Length-1
	 END
	 /***********补零操作 End*******/           
	 
	 SET @ReturnNum=REPLACE( @DataFormat,'<YYYY>',@year);			-- 把规则中<YYYY>替换成相应年
	 SET @ReturnNum=REPLACE( @ReturnNum,'<MM>',@month);				-- 把规则中<MM>替换成相应月
	 SET @ReturnNum=REPLACE( @ReturnNum,'<DD>',@day);				-- 把规则中<DD>替换成相应日
	 SET @ReturnNum=REPLACE( @ReturnNum,@WorkFLowStr,@SeqNowNumStr);-- 把规则中的形如<XXX>的替换成相应流水号，
	 
	 
	 PRINT @ReturnNum
	 /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) Start*******/
	Begin transaction
	 UPDATE SysRulSequence SET NowSeqValue=@SeqNowNum+1,DateMax=@DataNow,ISRUNNING='1', EditTime=Getdate()
	 WHERE IsRunning='2' AND  SeqCode=@SeqCode
	 --SELECT * FROM SysRulSequence WHERE IsRunning='2' AND SeqCode=@SeqCode
	Commit transaction 
	 /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) End*******/
	RETURN

END TRY

--错误捕获
BEGIN   CATCH   
     ROLLBACK TRANSACTION  
  set @MessageCode='行号='+cast(ERROR_LINE() as varchar(10))+'错误信息'+ERROR_MESSAGE() 
  	+'['+ERROR_PROCEDURE()+']'
   IF @@ROWcount<=0 
      set @MessageCode='无此编号规则'+@MessageCode
    SELECT  @MessageCode
END   CATCH


GO