56.使用编号表按日期生成刘少的案例

悠悠 2024-02-19 17:58 89阅读 0赞

--编号表
    CREATE TABLE tb_NO(
    Name char(2) NOT NULL,                 --编号种类的名称
    Days int NOT NULL,                     --保存的是该种编号那一天的当前编号
    Head nvarchar(10) NOT NULL DEFAULT '', --编号的前缀
    CurrentNo int NOT NULL DEFAULT 0,      --当前编号
    BHLen int NOT NULL DEFAULT 6,          --编号数字部分长度
    YearMoth int NOT NULL                  --上次生成编号的年月,格式YYYYMM
    	DEFAULT CONVERT(CHAR(6),GETDATE(),112),
    DESCRIPTION NVARCHAR(50),              --编号种类说明
    TableName sysname NOT NULL,            --当前编号对应的原始表名
    KeyFieldName sysname NOT NULL,         --当前编号对应的原始表编号字段名
    PRIMARY KEY(Name,Days))
    
    --这里以一种单据的7天的资料来做测试
    INSERT tb_NO SELECT 'CG',1,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',2,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',3,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',4,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',5,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',6,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    UNION  ALL   SELECT 'CG',7,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
    GO
    
    --获取新编号的存储过程
    CREATE PROC p_NextBH
    @Name char(2),            --编号种类
    @Date datetime=NULL,     --要获取的当前日期,不指定则为系统当前日期
    @BH nvarchar(20) OUTPUT --新编号
    AS
    IF @Date IS NULL SET @Date=GETDATE()
    BEGIN TRAN
    	--从编号表中获取新编号
    	UPDATE tb_NO SET 
    		@BH=Head
    			+CONVERT(CHAR(6),@Date,12)
    			+RIGHT(POWER(10,BHLen)
    				+CASE 
    					WHEN YearMoth=CONVERT(char(6),@Date,112)
    					THEN CurrentNo+1
    					ELSE 1 END
    			,BHLen),
    		CurrentNo=CASE 
    			WHEN YearMoth=CONVERT(char(6),@Date,112)
    			THEN CurrentNo+1
    			ELSE 1 END,
    		YearMoth=CONVERT(char(6),@Date,112)
    	WHERE Name=@Name 
    		AND Days=DAY(@Date)
    		AND YearMoth<=CONVERT(char(6),@Date,112)
    
    	--如果要获取的编号在编号表中已经过期,则直接从原始表中取编号
    	IF @@ROWCOUNT=0
    	BEGIN
    		DECLARE @s nvarchar(4000)
    		SELECT @s=N'SELECT @BH='
    			+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12),N'''')
    			+N'+RIGHT('+CAST(POWER(10,BHLen)+1 as varchar)
    			+N'+ISNULL(RIGHT(MAX('+QUOTENAME(KeyFieldName)
    			+N'),'+CAST(BHLen as varchar)
    			+N'),0),'+CAST(BHLen as varchar)
    			+N') FROM '+QUOTENAME(TableName)
    			+N' WITH(XLOCK,PAGLOCK) WHERE '
    			+QUOTENAME(KeyFieldName)
    			+N' like '+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12)+N'%',N'''')
    		FROM tb_NO
    		WHERE Name=@Name 
    			AND Days=DAY(@Date)
    			AND YearMoth>CONVERT(char(6),@Date,112)
    		IF @@ROWCOUNT>0
    			EXEC sp_executesql @s,N'@BH nvarchar(20) OUTPUT',@BH OUTPUT
    	END
    COMMIT TRAN
    GO
    
    CREATE TABLE tb(BH char(12))
    --获取 CG 的新编号
    DECLARE @bh char(12)
    EXEC p_NextBH 'CG','2005-1-1',@bh OUT
    SELECT @bh
    --结果: CG0501010001
    
    EXEC p_NextBH 'CG','2005-1-1',@bh OUT
    SELECT @bh
    --结果: CG0501010002
    
    EXEC p_NextBH 'CG','2005-1-2',@bh OUT
    SELECT @bh
    --结果: CG0501020001
    
    EXEC p_NextBH 'CG','2005-2-2',@bh OUT
    SELECT @bh
    --结果: CG0402020001
    
    EXEC p_NextBH 'CG','2004-2-2',@bh OUT
    SELECT @bh
    --结果: CG0402020001
    GO