SQL Server时间粒度系列----第8节位运算以及设置日历数据表节假日标志详解
本文目录列表：
SQL Server支持的按位运算符有三个，分别为：按位与（&）、按位或（|）、按位异或（^)。位运算符用于 int、smallint 或 tinyint 数据，目前SQL Server能支持的按位运算的最大整数类型为Int（4字节整数）。有关以上三个按位运算符的详细使用，请参考微软的SQL Server的联机帮助。
上篇博文（日历数据表详解）中，总结出来一个日历数据表，该表的字段列（WorkDayFlag bit)表示是否工作日。默认设置周一到周五为工作日，周六和周日为非工作日的。但是国家法定节假日有时候会占用默认工作日（周一到周五中一天或若干天）的，这就要涉及设置工作日的功能。这个大家很显然能想到一条日历数据记录的进行设置，也是基于迭代或游标的方法。
基于迭代或游标的方法仔细想想确实存在性能问题的，一个月至少有28天，最多的有31天，如果设置一个月中每天的工作日标志字段列值，需要很多次的数据库连接资源的，即便共享一次数据库连接，多次执行的也是存在性能问题的。那如何解决常规方式存在的性能问题呢？
我们先不说解决方案，我们先从每月包含的最大天数31来说起。我们再来看SQL Server 提供的数据类型int，这是个有符号的4字节整数，共计32位，第32位为符号位，对于非负数该符号位为0，对于负数该符号位为1。非负整数的int只有31位来存储数据的；每个月最大天数是31天，这两者确实很巧合吧。如果我们将一个月的每一天分别对应一个int的每一位，从第一天到最后一天分别对应int的第一位到第31位，如以下表格：
| 月内日索引（从1开始计数） | int位索引（从0开始计数） |
|
1
|
0
|
|
2
|
1
|
|
3
|
2
|
| …… |
……
|
|
28
|
28
|
|
29
|
28 |
|
30
|
29
|
| 31 |
30
|
既然知道了月内日索引( 我们设位变量DayOfMonth)和int位索引(我们设置变量为BitOfInt)的关系，两者之间的关系使用变量表示为：DayOfMonth - 1 = BitOfInt，那么我们就是用一个int值来保存一个月所有的工作日标志值（每个工作日标志值：1*power(2，DayOfMonth-1)， 整数的位值：1*power(2, BitOfInt)，这两者是相等的）之和（我们设置变量为WorkDayValueOfMonth），。这样就可以基于集合的方法来设置一个月的工作日标志。有了工作日标志值之和的int数值，那么如何分别设置每一天的工作日标志值呢？这个就要使用SQL Server提供的按位与运算符，如果(1*power(2，DayOfMonth-1)) & WorkDayValueOfMonth 的按位与的结果值为1，那么DayOfMonth所对应的的天则设置为了工作日；如果其结果为0，则设置为了非工作日。
基于集合的方法来设置一个月的每天的工作日标志，也不是每一天都可以设置的，我们设置工作日标志有关前提，所设置的工作日必须大于当前日（今天）的，小于当前日的我们不做设置，只能按照默认工作日设置（周一到周五为工作日，周六和周日为非工作日）。
既然有了设置工作日标志的方法，当然也有获取指定月的工作日值和和当前月的工作日的数量。根据以上我们提供两个存储过程，分别对应设置工作日标志或获取工作日标志的功能。
设置工作日标志的存储过程，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.usp_Calendar_WorkDaySet', 'P') IS NOT NULL 2 BEGIN 3 DROP PROCEDURE dbo.usp_Calendar_WorkDaySet; 4 END 5 GO 6 7 --================================== 8 -- 功能: 设置指定月份的工作日标志 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 15 CREATE PROCEDURE usp_Calendar_WorkDaySet 16 ( 17 @intMonths INT, -- 指定的日期月数 18 @intWorkDayValueSum INT, -- 指定的日期月数的所有工作日标志值之和 19 @bitIsUseDefault BIT = 0, -- 是否使用默认设置，1：使用默认设置（周一到周五为工作日，周六和周日非工作日），0：基于指定的日期月数的所有工作日标志值之和来设置 20 21 -- 方便记录用户操作日志 22 --@chvnUser NVARCHAR(20), -- 指定的用户 23 --@intUserID INT, -- 指定的用户ID 24 --@chvUserIP VARCHAR(40), -- 指定的用户IP 25 --@chvnUserFrom NVARCHAR(30), -- 指定的用户位置 26 27 @chvnErrMsg NVARCHAR(100) OUTPUT -- 错误异常消息字符串 28 ) 29 --$Encode$-- 30 AS 31 BEGIN 32 SET NOCOUNT ON; 33 34 SET @intMonths = dbo.ufn_GetValidDateNum(@intMonths); 35 36 IF @intWorkDayValueSum IS NULL OR @intWorkDayValueSum < 0 37 BEGIN 38 SET @intWorkDayValueSum = 0; 39 END 40 41 SET @chvnErrMsg = N''; 42 43 DECLARE @tintResultValue AS TINYINT; 44 SET @tintResultValue = 1; -- 默认存在错误 45 46 DECLARE 47 @dtmNow AS DATETIME, 48 @intDays AS INT; 49 SELECT 50 @dtmNow = GETDATE(), 51 @intDays = dbo.ufn_Days(@dtmNow); 52 53 IF @intMonths < dbo.ufn_Months(@dtmNow) 54 BEGIN 55 SET @chvnErrMsg = N'不能设置小于当前月份的工作日标志。'; 56 57 RETURN @tintResultValue; 58 END 59 60 DECLARE 61 @WorkDayValueSum AS INT, 62 @DayCount AS INT; 63 SELECT 64 @WorkDayValueSum = 0, 65 @DayCount = 0; 66 67 SELECT 68 @WorkDayValueSum = SUM(POWER(2, [DayOfMonth] - 1)) 69 ,@DayCount = COUNT(1) 70 FROM dbo.Calendar 71 WHERE Months = @intMonths 72 AND [Days] >= @intDays + 1; 73 74 IF @DayCount = 0 OR @WorkDayValueSum = 0 75 BEGIN 76 SET @chvnErrMsg = N'日历数据表不存在满足条件的数据。'; 77 78 RETURN @tintResultValue; 79 END 80 81 IF @intWorkDayValueSum = @WorkDayValueSum 82 BEGIN 83 SET @tintResultValue = 0; 84 85 RETURN @tintResultValue; 86 END 87 88 DECLARE @intRowCount AS INT; 89 SELECT @intRowCount = 0; 90 91 BEGIN TRY 92 IF @bitIsUseDefault = 0 93 BEGIN 94 UPDATE Calendar 95 SET WorkdayFlag = POWER(2, [DayOfMonth] - 1) & @intWorkDayValueSum 96 WHERE Months = @intMonths 97 AND [Days] >= @intDays + 1; 98 END 99 ELSE 100 BEGIN 101 UPDATE Calendar 102 SET WorkdayFlag = CASE WHEN dbo.ufn_DayOfWeek(CalendarDate) <= 5 THEN 1 ELSE 0 END 103 WHERE Months = @intMonths 104 AND [Days] >= @intDays + 1; 105 END 106 107 SET @intRowCount = @@ROWCOUNT; 108 109 SET @tintResultValue = 0; 110 END TRY 111 BEGIN CATCH 112 SET @chvnErrMsg = N'设置指定月的工作日标志发生错误。'; 113 114 RETURN @tintResultValue; 115 END CATCH 116 117 RETURN @tintResultValue; 118 END 119 GO 120 121 -- Test Code 122 DECLARE 123 @intMonths AS INT, 124 @intWorkDayValueSum AS INT, 125 @bitIsUseDefault AS BIT, 126 @chvnErrMsg AS NVARCHAR(100), 127 @tintResultVaule AS TINYINT; 128 SELECT 129 @intMonths = 0, -- int 130 @intWorkDayValueSum = 0, -- int 131 @bitIsUseDefault = NULL, -- bit 132 @chvnErrMsg = N'', -- nvarchar(100) 133 @tintResultVaule = 1; -- tinyint 134 135 EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet 136 @intMonths = @intMonths, -- int 137 @intWorkDayValueSum = @intWorkDayValueSum, -- int 138 @bitIsUseDefault = @bitIsUseDefault, -- bit 139 @chvnErrMsg = @chvnErrMsg OUTPUT -- nvarchar(100) 140 141 SELECT @chvnErrMsg AS 'Error Message' 142 ,@tintResultVaule AS 'Return Value'; 143 GO 144 145 -- Test Code 146 -- 2016-02月份 147 -- 根据国家节假日获取的工作日标志值和以及工作日总数 148 SELECT 149 WorkDayValueSum = SUM(T.WorkDayFlag2 * POWER(2, T.[DayOfMonth] - 1)) 150 ,WorkDayCount = SUM(T.WorkDayFlag2 * 1) 151 FROM ( 152 SELECT 153 Months 154 ,[DayOfMonth] 155 ,WorkDayFlag 156 ,WorkDayFlag2 = CASE 157 WHEN [DayOfMonth] = 6 THEN 1 158 WHEN [DayOfMonth] BETWEEN 7 AND 12 THEN 0 159 WHEN [DayOfMonth] = 14 THEN 1 160 ELSE WorkDayFlag END 161 FROM dbo.Calendar 162 WHERE Months = dbo.ufn_Months('2016-02-01') 163 ) AS T 164 GO 165 166 DECLARE 167 @intMonths AS INT, 168 @intWorkDayValueSum AS INT, 169 @bitIsUseDefault AS BIT, 170 @chvnErrMsg AS NVARCHAR(100), 171 @tintResultVaule AS TINYINT; 172 SELECT 173 @intMonths = dbo.ufn_Months('2016-02-01'), -- int 174 @intWorkDayValueSum = 333963327, -- int 175 @bitIsUseDefault = 0, -- bit 176 @chvnErrMsg = N'', -- nvarchar(100) 177 @tintResultVaule = 1; -- tinyint 178 179 EXEC @tintResultVaule = dbo.usp_Calendar_WorkDaySet 180 @intMonths = @intMonths, -- int 181 @intWorkDayValueSum = @intWorkDayValueSum, -- int 182 @bitIsUseDefault = @bitIsUseDefault, -- bit 183 @chvnErrMsg = @chvnErrMsg OUTPUT -- nvarchar(100) 184 185 SELECT @chvnErrMsg AS 'Error Message' 186 ,@tintResultVaule AS 'Return Value'; 187 GO 188 189
测试以上存储的功能效果，如下图：
查询201602月份的日历数据，如下图：
获取工作日标志的存储过程，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.usp_Calendar_WeekDayGet', 'P') IS NOT NULL 2 BEGIN 3 DROP PROCEDURE dbo.usp_Calendar_WeekDayGet 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取满足条件的日期月数的工作日值和工作日总数 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 CREATE PROCEDURE dbo.usp_Calendar_WeekDayGet 15 ( 16 @intStartMonths INT, -- 指定的开始日期月数 17 @intEndMonths INT -- 指定的结束日期月数 18 ) 19 --$Encode$-- 20 AS 21 BEGIN 22 SET NOCOUNT ON; 23 24 SET @intStartMonths = dbo.ufn_GetValidDateNum(@intStartMonths); 25 SET @intEndMonths = dbo.ufn_GetValidDateNum(@intEndMonths); 26 27 IF @intStartMonths > @intEndMonths 28 BEGIN 29 DECLARE @intTemp AS INT; 30 SET @intTemp = @intStartMonths; 31 SET @intStartMonths = @intEndMonths; 32 SET @intEndMonths = @intTemp; 33 END 34 35 SELECT 36 Months 37 ,WorkDayValueSum = ISNULL(SUM(WorkDayFlag * POWER(2, [DayOfMonth] - 1)), 0) 38 ,WorkDayCount = ISNULL(SUM(WorkDayFlag * 1), 0) 39 FROM dbo.Calendar 40 WHERE Months BETWEEN @intStartMonths AND @intEndMonths 41 GROUP BY Months; 42 END 43 GO 44 45 -- Test Code 46 DECLARE 47 @intStartMonths AS INT, 48 @intEndMonths AS INT, 49 @tintResultValue AS TINYINT; 50 51 SELECT 52 @intStartMonths = dbo.ufn_Months('2015-06-01'), 53 @intEndMonths = dbo.ufn_Months('2016-03-02'), 54 @tintResultValue = 1; -- 默认范围值 55 56 EXEC @tintResultValue = dbo.usp_Calendar_WeekDayGet 57 @intStartMonths = @intStartMonths, -- int 58 @intEndMonths = @intEndMonths; -- int 59 60 SELECT @tintResultValue AS 'Return Value (1:Have Error,0:No Error)' 61 GO
测试以上存储的功能效果，如下图：
本文提起了SQL Server的按位运算符，重点讲解了日历数据表中的工作日标志的设置的处理方法，基于集合的处理方法，结合按位与运算符来处理的方法。
