SQL Server获取下一个编码字符实现继续重构与增强

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

 

执行后的查询结果如下：   博友如有其他更好的解决方案，也请不吝赐教，万分感谢。

SQL Server获取下一个编码字符实现继续重构与增强

标签：