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

执行后的查询结果如下:

博友如有其他更好的解决方案,也请不吝赐教,万分感谢。

时间: 12-26

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

SQL Server获取下一个编码字符串的实现方案分割和进位

我在前一种解决方案SQL Server获取下一个编码字符实现和后一种解决方案SQL Server获取下一个编码字符实现继续重构与增强两篇博文中均提供了一种解决编码的方案,考虑良久对比以上两种方案的,后一种方案虽然解决了其中方案的缺点,但是依然存在的编码字符串长度的限制(最多满足8位长度),本博文提供的方案将编码字符串长度增加到19位,也可以足够项目中实现这些编码. 具体的编码规则可以参看以上两种解决方案博文中的描述,也可以进入SQL Server 大V潇湘隐者的获取下一个编码字符串问题这篇博文.

sql server 获取每一个类别中值最大的一条数据

sql server 获取每一个类别中值最大的一条数据 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 /* 数据如下: name val memo a    2   a2(a的第二个值) a    1   a1--a的第一个值 a    3   a3:a的第三个值 b    1   b1--b的第一个

SQL server 获取另外一个服务器中的数据

1 select * from 2 opendatasource('msdasql','driver={sql server};server=IP地址或者主机名;uid=sa;pwd=密码;').数据库实例名.dbo.表名 3 where 你的条件

【转】sql server 获取每一个类别中值最大的一条数据

1 /* 2 数据如下: 3 name val memo 4 a 2 a2(a的第二个值) 5 a 1 a1--a的第一个值 6 a 3 a3:a的第三个值 7 b 1 b1--b的第一个值 8 b 3 b3:b的第三个值 9 b 2 b2b2b2b2 10 b 4 b4b4 11 b 5 b5b5b5b5b5 12 */ 13 --创建表并插入数据: 14 create table tb(name varchar(10),val int,memo varchar(20)) 15 insert

在sql server中创建一个只读login

在SQL Server中创建一个Login,并为这个login授予只读的权限 --create Login Name create login [domain\login] from windows; --create --UserName Create User DWReadOnly for login [domain\login] --Grant readonly ALTER ROLE [db_datareader] add MEMBER DWReadOnly Appendix 服务器角色参

第1周 SQL Server 如何执行一个查询

原文:第1周 SQL Server 如何执行一个查询 大家好,欢迎来到第1周的SQL Server性能调优培训.在我们进入SQL Server性能调优里枯燥难懂的细节内容之前,我想通过讲解SQL Server如何执行一个查询来建立基础.这个部分非常重要,因为接下来的培训中我们会以这些概念来加深我们对SQL Server的认识. 当我们执行一个查询时,在SQL Server中最重要的组件有哪些,下面这张图片可以给大家一个概观的认识. 可以看到,SQL Server内部分成了2个部分:关系引擎和存储

[译]SQL Passion Week 1: SQL Server如何执行一个查询

SQLpassion Performance Tuning Training Plan 个人学习翻译,如有谬误,请不吝指出,感谢.  Week 1: SQL Server如何执行一个查询   在我们进入SQL Server性能调优的繁杂细节之前, 我想先列举一下SQL Server如何执行一个查询(query)的结构, 这部分内容非常重要, 因为了解这些概念, 对我们以后的性能调优课程会理解的更加深刻. 下面的图为我们展示了SQL Server执行查询过程中所包含的几个主要组成部分: SQL S

SQL Server 2008下日志清理方法

每次看到暴涨的数据库日志就有些头大,于是乱搜一通找个办法把日志干掉,下次又头大,又搜半天,于是还是写篇blog,不用乱找了. 1.将数据库设置成 简单 模式 选择要收缩的数据库,点右键 属性->选项,选择 简单模式 2.选择任务->收缩->文件 3.选择日志 或者用如下语句 代码 USE [master]GOALTER DATABASE DNName SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE DNName SET RECOVERY 

SQL Server 2008下日志清理方法 2

SQL Server 2008下日志清理方法 (2011-07-14 10:30:45) 转自 http://blog.sina.com.cn/s/blog_4bdd3d0b0100wfvq.html 标签: it   每次看到暴涨的数据库日志就有些头大,于是乱搜一通找个办法把日志干掉,下次又头大,又搜半天,于是还是写篇blog,不用乱找了. 1.将数据库设置成 简单 模式 选择要收缩的数据库,点右键 属性->选项,选择 简单模式 2.选择任务->收缩->文件 3.选择日志 或者用如下语