SQL Server获取下一个编码字符实现继续重构与增强
我在SQL Server获取下一个编码字符实现的博文中，虽然实现了这个问题，但是感觉维护起来比较麻烦，例如如果调整编码字符串的固定长度，就需要变更三个函数，这样的为何成本确实比较大。面向对象编程很重视讲究开放封闭原则，我认为数据库对象特别函数、存储等对象也要尽量封装成实现单一功能，维护起来简单，也方便后续人员的维护，便利别人也是便利自己。
针对编码字符串的规则，继续延伸总结如下：
1、第一个字符必须是字母A-Z中任意一个字符，其长度可以为1位、2位、3位，……，6位、7位、8位、……；
2、编码字符串满足递进增加。如果编码字符串长度为5位，则编码字符串是A0000，下一个是A0001，直到A9999，其下一个是B0000，直到B9999，其下一个是C0000，……，如果编码是ZB999，下一个是ZC000，……，直到ZZZZZ；无论其长度为1位到N位中的任何一个长度，均要满足递进增加。
我的实现方案是将编码字符串中每一位的字符对应的整数值加工转换为一个10进制整数值，再实现将一个将整数值转换为一个编码字符串，也就是将编码字符串和整数值相互转换。由于方案中是使用了POWER函数，受限其结果值的长度，此方案最多支持8位长度的编码字符串和整数值间的相互转换。
通过以上规则分析，不同长度的编码字符均对应不同的最小和最大编码字符串，我的方案是编码字符串和整数相互转换，也就是不同长度的编码字符对应的不同的相对应的最小和最大整数值，也就是不同的长度对应的不同的整数范围，且这些不同长度对应的整数范围之间是不连续，比如长度1位对应的最大整数值和长度2位对应的最小整数不是相等。
方案的具体实现思路如下：
1、由于使用到数字0-9和字母[A-Z]（大写）字母这两类字符，将以上字符和10进制整数值之间的硬编码对应，将其封装为一个表值函数。
2、实现将一个编码字符串转换为一个整数值，此功能封装为一个标量函数。
3、针对不同长度的编码字符串对应的不同最小和最大整数，这个功能也可以检查一个整数值是否在满足转换为一个编码字符串，也封装为一个表值函数。
4、将一个整数值转换为一个编码字符串，此功能也封装在一个标量函数中。
针对上实现方案主要重构的部分包括函数命名，针对表（表值或内联表）函数和标量函数尽量通过命名标识符来区分。还解决了编码字符串穿不同长度的实现来强制修改所涉及三个函数的缺陷。为了讲解方便，我根据该方案的实现思路的4个步骤，将其对应的部分分为：字符映射表值函数、获取编码字符串整数值标量函数、获取编码字符串长度和整数值范围表函数和获取整数值对应的编码字符串变量函数。
字符映射表值函数
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_GetCharTable', 'TF') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_GetCharTable; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取字符映射表表值函数 9 -- 说明: 编码字符只包含0-9和A-Z这两类字符 10 -- 将以上字符映射到对应十进制数值。 11 -- 作者: XXX 12 -- 创建: yyyy-MM-dd 13 -- 修改: yyyy-MM-dd XXX 修改内容描述 14 -- 调用: SELECT CodeChar, CodeValue FROM dbo.ufn_GetCharTable(); 15 --================================== 16 CREATE FUNCTION dbo.ufn_GetCharTable 17 ( 18 ) RETURNS @tblChar TABLE ( 19 [Char] CHAR(1) NOT NULL, 20 Value TINYINT NOT NULL 21 ) 22 --$Encode$-- 23 AS 24 BEGIN 25 DECLARE 26 @intStartIndexID AS TINYINT, 27 @intEndIndexID AS TINYINT; 28 29 SELECT 30 @intStartIndexID = 0, 31 @intEndIndexID = 0; 32 33 -- 初始化0-9数字字符 34 SELECT 35 @intStartIndexID = ASCII('0'), 36 @intEndIndexID = ASCII('9'); 37 WHILE @intStartIndexID <= @intEndIndexID 38 BEGIN 39 INSERT INTO @tblChar ([Char], Value) 40 VALUES (CHAR(@intStartIndexID), 0); 41 42 SET @intStartIndexID = @intStartIndexID + 1; 43 END 44 45 -- 初始化A-Z字母字符 46 SELECT 47 @intStartIndexID = ASCII('A'), 48 @intEndIndexID = ASCII('Z'); 49 WHILE @intStartIndexID <= @intEndIndexID 50 BEGIN 51 INSERT INTO @tblChar ([Char], Value) 52 VALUES (CHAR(@intStartIndexID), 0); 53 54 SET @intStartIndexID = @intStartIndexID + 1; 55 END 56 57 -- 修改每个字符对应的10进制整数值 58 ;WITH tCodeData AS ( 59 SELECT [Char], ROW_NUMBER() OVER (ORDER BY [Char] ASC) AS RowNum 60 FROM @tblChar 61 ) 62 63 UPDATE T2 64 SET T2.Value = T.RowNum - 1 65 FROM tCodeData AS T 66 INNER JOIN @tblChar AS T2 67 ON T.[Char] = T2.[Char]; 68 69 RETURN; 70 END 71 GO 72
获取编码字符串整数值标量函数
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_GetCodeCharsValue', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_GetCodeCharsValue; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取编码字符串对应的10进制整数数值 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 -- 调用: SELECT dbo.ufn_GetCodeIntegerValue('A0000') 14 --================================== 15 CREATE FUNCTION dbo.ufn_GetCodeCharsValue 16 ( 17 @chvCodeChars VARCHAR(8) -- 编码字符串 18 19 ) RETURNS INT 20 --$Encode$-- 21 AS 22 BEGIN 23 SET @chvCodeChars = ISNULL(@chvCodeChars, ''); 24 SET @chvCodeChars = UPPER(@chvCodeChars); 25 26 DECLARE @intCodeCharsValue AS BIGINT; 27 SET @intCodeCharsValue = 0; 28 29 DECLARE @tintLength AS TINYINT; 30 SET @tintLength = LEN(@chvCodeChars); 31 32 IF @tintLength = 0 33 BEGIN 34 RETURN @intCodeCharsValue; 35 END 36 37 DECLARE @tblChar TABLE( 38 [Char] CHAR(1) NOT NULL, 39 Value TINYINT NOT NULL 40 ); 41 42 INSERT INTO @tblChar ([Char], Value) 43 SELECT [Char], Value 44 FROM dbo.ufn_GetCharTable(); 45 46 -- 编码字符串的首字母必须是A-Z字母字符的逻辑检查 47 IF NOT EXISTS (SELECT 1 FROM @tblChar WHERE [Char] = SUBSTRING(@chvCodeChars, 1, 1) AND Value >= 10) 48 BEGIN 49 RETURN @intCodeCharsValue; 50 END 51 52 WHILE @tintLength >= 1 53 BEGIN 54 SELECT @intCodeCharsValue = @intCodeCharsValue + CAST(Value * POWER(10, @tintLength - 1) AS BIGINT) 55 FROM @tblChar 56 WHERE [Char] = SUBSTRING(@chvCodeChars, 1, 1); 57 58 SET @chvCodeChars = STUFF(@chvCodeChars, 1, 1, ''); 59 60 SET @tintLength = @tintLength - 1; 61 END 62 63 RETURN @intCodeCharsValue; 64 END 65 GO
获取编码字符串长度和整数值范围表函数
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_GetCodeCharsValueTable', 'TF') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_GetCodeCharsValueTable; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取编码字符串不同长度对应的整数值范围 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 -- 调用: SELECT StartValue, EndValue, FixLength FROM dbo.ufn_GetCodeCharsValueTable(); 14 --================================== 15 CREATE FUNCTION dbo.ufn_GetCodeCharsValueTable 16 ( 17 18 ) RETURNS @tblCodeCharValue TABLE ( 19 StartValue INT NOT NULL, 20 EndValue INT NOT NULL, 21 FixLength TINYINT NOT NULL 22 ) 23 AS 24 BEGIN 25 DECLARE 26 @tintLength AS TINYINT, 27 @tintMaxLength AS TINYINT; 28 SELECT 29 @tintLength = 1, 30 @tintMaxLength = 8; 31 32 WHILE @tintLength <= @tintMaxLength 33 BEGIN 34 INSERT INTO @tblCodeCharValue (StartValue, EndValue, FixLength) 35 VALUES (dbo.ufn_GetCodeCharsValue(CONCAT('A', REPLICATE('0', @tintLength - 1))), dbo.ufn_GetCodeCharsValue(CONCAT('Z', REPLICATE('Z', @tintLength - 1))), @tintLength); 36 37 SET @tintLength = @tintLength + 1; 38 END 39 40 RETURN; 41 END 42 GO
获取整数值对应的编码字符串变量函数。
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_GetCodeChars', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_GetCodeChars; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取一个整数值对应的编码字符串 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 CREATE FUNCTION dbo.ufn_GetCodeChars 15 ( 16 @intCodeCharsValue INT -- 编码字符串整数值 17 ) RETURNS VARCHAR(8) 18 --$Encode$-- 19 AS 20 BEGIN 21 SET @intCodeCharsValue = ISNULL(@intCodeCharsValue, 0); 22 DECLARE @chvCodeChars AS VARCHAR(8); 23 SET @chvCodeChars = ''; 24 25 -- 整数值长度变量 26 DECLARE @tintFixLength AS TINYINT; 27 SET @tintFixLength = 0; 28 29 SELECT @tintFixLength = FixLength 30 FROM dbo.ufn_GetCodeCharsValueTable() 31 WHERE @intCodeCharsValue BETWEEN StartValue AND EndValue; 32 33 -- 整数值范围的逻辑检查 34 IF @tintFixLength = 0 35 BEGIN 36 RETURN @chvCodeChars; 37 END 38 39 DECLARE @tblChar TABLE( 40 [Char] CHAR(1) NOT NULL, 41 Value TINYINT NOT NULL 42 ); 43 44 INSERT INTO @tblChar ([Char], Value) 45 SELECT [Char], Value 46 FROM dbo.ufn_GetCharTable(); 47 48 49 DECLARE @tintPerCodeValue TINYINT; 50 SET @tintPerCodeValue = 0; 51 52 WHILE @tintFixLength >= 1 53 BEGIN 54 SET @tintPerCodeValue = @intCodeCharsValue / POWER(10, @tintFixLength - 1); 55 56 SELECT TOP 1 @chvCodeChars = @chvCodeChars + [Char], @tintPerCodeValue = Value 57 FROM @tblChar 58 WHERE Value <= @tintPerCodeValue 59 ORDER BY Value DESC; 60 61 SET @intCodeCharsValue = @intCodeCharsValue - @tintPerCodeValue * POWER(10, @tintFixLength - 1); 62 63 SET @tintFixLength = @tintFixLength - 1; 64 END 65 66 RETURN @chvCodeChars; 67 END 68 GO
测试实现效果
测试T-SQL代码如下：
1 DECLARE @chvCodeChars AS VARCHAR(8); 2 SET @chvCodeChars = CONCAT('A', REPLICATE('0', 7 - 1)); 3 DECLARE @intCodeCharsValue AS INT; 4 SET @intCodeCharsValue = dbo.ufn_GetCodeCharsValue(@chvCodeChars); 5 6 SELECT @chvCodeChars AS CurrentCodeChars, @intCodeCharsValue AS CurrentCodeCharsValue, dbo.ufn_GetCodeChars(@intCodeCharsValue + 1) AS NextCodeChars 7 GO
执行后的查询结果如下：
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
