我在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
2BEGIN
3 DROP FUNCTION dbo.ufn_GetCharTable;
4END
5GO
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 --==================================
16CREATE FUNCTION dbo.ufn_GetCharTable
17(
18) RETURNS @tblChar TABLE (
19 [Char] CHAR(1) NOT NULL,
20 Value TINYINT NOT NULL
21)
22 --$Encode$--
23AS
24BEGIN
25 DECLARE
26 @intStartIndexID AS TINYINT,
27 @intEndIndexID AS TINYINT;
2829 SELECT
30 @intStartIndexID = 0,
31 @intEndIndexID = 0;
3233 -- 初始化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);
4142 SET @intStartIndexID = @intStartIndexID + 1;
43 END
4445 -- 初始化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);
5354 SET @intStartIndexID = @intStartIndexID + 1;
55 END
5657 -- 修改每个字符对应的10进制整数值
58 ;WITH tCodeData AS (
59 SELECT [Char], ROW_NUMBER() OVER (ORDER BY [Char] ASC) AS RowNum
60 FROM @tblChar
61 )
6263 UPDATE T2
64 SET T2.Value = T.RowNum - 165 FROM tCodeData AS T
66 INNER JOIN @tblChar AS T2
67 ON T.[Char] = T2.[Char];
6869 RETURN;
70END
71GO
72
获取编码字符串整数值标量函数
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N‘dbo.ufn_GetCodeCharsValue‘, ‘FN‘) IS NOT NULL
2BEGIN
3 DROP FUNCTION dbo.ufn_GetCodeCharsValue;
4END
5GO
6 7 --==================================
8 -- 功能: 获取编码字符串对应的10进制整数数值
9 -- 说明: 具体实现阐述
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SELECT dbo.ufn_GetCodeIntegerValue(‘A0000‘)
14 --==================================
15CREATE FUNCTION dbo.ufn_GetCodeCharsValue
16(
17 @chvCodeChars VARCHAR(8) -- 编码字符串
1819) RETURNS INT
20 --$Encode$--
21AS
22BEGIN
23 SET @chvCodeChars = ISNULL(@chvCodeChars, ‘‘);
24 SET @chvCodeChars = UPPER(@chvCodeChars);
2526 DECLARE @intCodeCharsValue AS BIGINT;
27 SET @intCodeCharsValue = 0;
2829 DECLARE @tintLength AS TINYINT;
30 SET @tintLength = LEN(@chvCodeChars);
3132 IF @tintLength = 033 BEGIN
34 RETURN @intCodeCharsValue;
35 END
3637 DECLARE @tblChar TABLE(
38 [Char] CHAR(1) NOT NULL,
39 Value TINYINT NOT NULL
40 );
4142 INSERT INTO @tblChar ([Char], Value)
43 SELECT [Char], Value
44 FROM dbo.ufn_GetCharTable();
4546 -- 编码字符串的首字母必须是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
5152 WHILE @tintLength >= 153 BEGIN
54 SELECT @intCodeCharsValue = @intCodeCharsValue + CAST(Value * POWER(10, @tintLength - 1) AS BIGINT)
55 FROM @tblChar
56 WHERE [Char] = SUBSTRING(@chvCodeChars, 1, 1);
5758 SET @chvCodeChars = STUFF(@chvCodeChars, 1, 1, ‘‘);
5960 SET @tintLength = @tintLength - 1;
61 END
6263 RETURN @intCodeCharsValue;
64END
65 GO
获取编码字符串长度和整数值范围表函数
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N‘dbo.ufn_GetCodeCharsValueTable‘, ‘TF‘) IS NOT NULL
2BEGIN
3 DROP FUNCTION dbo.ufn_GetCodeCharsValueTable;
4END
5GO
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 --==================================
15CREATE FUNCTION dbo.ufn_GetCodeCharsValueTable
16(
1718) RETURNS @tblCodeCharValue TABLE (
19 StartValue INT NOT NULL,
20 EndValue INT NOT NULL,
21 FixLength TINYINT NOT NULL
22)
23AS
24BEGIN
25 DECLARE
26 @tintLength AS TINYINT,
27 @tintMaxLength AS TINYINT;
28 SELECT
29 @tintLength = 1,
30 @tintMaxLength = 8;
3132 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);
3637 SET @tintLength = @tintLength + 1;
38 END
3940 RETURN;
41END
42 GO
获取整数值对应的编码字符串变量函数。
该函数的T-SQL代码如下：
1 IF OBJECT_ID(N‘dbo.ufn_GetCodeChars‘, ‘FN‘) IS NOT NULL
2BEGIN
3 DROP FUNCTION dbo.ufn_GetCodeChars;
4END
5GO
6 7 --==================================
8 -- 功能: 获取一个整数值对应的编码字符串
9 -- 说明: 具体实现阐述
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 --==================================
14CREATE FUNCTION dbo.ufn_GetCodeChars
15(
16 @intCodeCharsValue INT -- 编码字符串整数值
17 ) RETURNS VARCHAR(8)
18 --$Encode$--
19AS
20BEGIN
21 SET @intCodeCharsValue = ISNULL(@intCodeCharsValue, 0);
22 DECLARE @chvCodeChars AS VARCHAR(8);
23 SET @chvCodeChars = ‘‘;
2425 -- 整数值长度变量
26 DECLARE @tintFixLength AS TINYINT;
27 SET @tintFixLength = 0;
2829 SELECT @tintFixLength = FixLength
30 FROM dbo.ufn_GetCodeCharsValueTable()
31 WHERE @intCodeCharsValue BETWEEN StartValue AND EndValue;
3233 -- 整数值范围的逻辑检查
34 IF @tintFixLength = 035 BEGIN
36 RETURN @chvCodeChars;
37 END
3839 DECLARE @tblChar TABLE(
40 [Char] CHAR(1) NOT NULL,
41 Value TINYINT NOT NULL
42 );
4344 INSERT INTO @tblChar ([Char], Value)
45 SELECT [Char], Value
46 FROM dbo.ufn_GetCharTable();
474849 DECLARE @tintPerCodeValue TINYINT;
50 SET @tintPerCodeValue = 0;
5152 WHILE @tintFixLength >= 153 BEGIN
54 SET @tintPerCodeValue = @intCodeCharsValue / POWER(10, @tintFixLength - 1);
5556 SELECT TOP 1 @chvCodeChars = @chvCodeChars + [Char], @tintPerCodeValue = Value
57 FROM @tblChar
58 WHERE Value <= @tintPerCodeValue
59 ORDER BY Value DESC;
6061 SET @intCodeCharsValue = @intCodeCharsValue - @tintPerCodeValue * POWER(10, @tintFixLength - 1);
6263 SET @tintFixLength = @tintFixLength - 1;
64 END
6566 RETURN @chvCodeChars;
67END
68 GO
测试实现效果
测试T-SQL代码如下：
1 DECLARE @chvCodeChars AS VARCHAR(8);
2 SET @chvCodeChars = CONCAT(‘A‘, REPLICATE(‘0‘, 7 - 1));
3DECLARE @intCodeCharsValue AS INT;
4 SET @intCodeCharsValue = dbo.ufn_GetCodeCharsValue(@chvCodeChars);
56 SELECT @chvCodeChars AS CurrentCodeChars, @intCodeCharsValue AS CurrentCodeCharsValue, dbo.ufn_GetCodeChars(@intCodeCharsValue + 1) AS NextCodeChars
7 GO
执行后的查询结果如下：
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
原文：http://www.cnblogs.com/dzy863/p/5085182.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！