其实很多非业务逻辑的功能,比如分页,数据过滤,可以在程序上面节省很多内存和CPU时间,但往往又找不到一个比较通用有效的方法,花了点时间,终于把我想要的在数据库中分页和过滤的功能写了出来,在这里分享。
第一期望:我希望输入页码(pageIndex),每页显示的记录数(pageSize),然后这个存储过程就可以给我一个当前页的数据集。
首先,我假设有个表叫Configuration,里面就3个字段,Id, Key, Value,就是一个简单的数据表,定义如下:
CREATE TABLE [dbo].[Configuration]
(
[Id] int NOT NULL IDENTITY (1,1),
[Key] varchar(20) NOT NULL UNIQUE,
[Value] nvarchar(max) NULL
)
然后,就可以写存储过程了,这里想要说下,我对存储过程命名的一些习惯,我会以"sp_"开头,为了标明这个存储过程是CRUD中的哪个操作,我会再附加一个缩写,比如这个分页的主要操作时READ,所以我的名字应该是"sp_r_",然后为了说明它的主要功能是分页,就有了"sp_r_p_",“p”表示paginate,最后跟上操作的主要的表名,"sp_r_p_configuration"。很多同学都喜欢用比如bypage等等,也可以,但是我更喜欢这种命名法,在特定的约定下,这种简洁性的命名可可读性。
再者,一个良好的代码书写规范是非常必要的,该注释的地方千万别省,在多数情况下,代码的可读性是非常重要的,除非因性能因素做必须得让步。
/*
PROCEDURE: [dbo].[sp_r_p_configuration]
OPERATION: READ
FEATURES: Paginate
PURPOSE: provide a paginated list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE PROCEDURE [dbo].[sp_r_p_configuration]
@pageIndex int = 1, /*which page of the total page*/
@pageSize int=10, /*how many records show in one page*/
@recordCount int OUTPUT, /*output: return the count of the total records in all pages*/
@pageCount int OUTPUT /*output: return the count of the total pages*/
AS
declare @startRow int; -- the row number of the first record in the page
declare @endRow int; -- the row number of the last record in the page
set @startRow = (@pageIndex - 1) * @pageSize + 1;
set @endRow= @startRow + @pageSize - 1;
;with tmp as
(select (ROW_NUMBER() over(order by Id)) as row_id, [Id], [Key], [Value]
from dbo.Configuration)
select [Id], [Key], [Value] from tmp
where row_id between @startRow and @endRow
-- calculate the record count in all pages
select @recordCount=COUNT(*) from dbo.Configuration
-- calculate the page count
set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))
RETURN 0
这里用的ROW_NUMBER()来实现分页,性能必拼SQL的方法好,WITH...AS可以省下一个表变量。最后计算总记录数和页数很简单,算下就好了。
第二期望:这样的,似乎差不多了,但是我还希望能够有一套规则,来过滤结果集,并且还要有类似AND和OR的运算功能,而且我仍旧不希望用inline-script去实现,能不用尽量不要用。
为了实现这个期望,我需要先定义怎么传入过滤规则,我最终决定用的是xml数据类型而不是普通字符串,因为普通字符串有长度限制(如果你说可以用text,那我只能说太奢侈,小弟用不起),定短了可能被截断出现错误输出,定长了,最多也就8000个字符,也浪费,我不喜欢不确定因素,然而xml不一样,既是结构化数据,又有索引支持,可长可短,客户端也可直接序列化成字符串传入,非常方便。于是剩下的是,约定一个规则,确定过滤规则的数据结构,在这里我专门写了一个function来将xml转换成table已被后用。P.S. 如果想传一个数组到存储过程,也可以用xml类型,比字符串加分隔符更安全。
/*
FUNCTION: [dbo].[fun_xmlfilter_parse]
OPERATION: READ
FEATURES:
PURPOSE: parse a table from a xml with the specific column name
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE FUNCTION [dbo].[fun_xmlfilter_parse]
(
@filter xml, /* The format should be:
<filter>
<rule
column="COLUMN_NAME" -- the column name which the rule should be applied
match="xxx" -- the key which the rule defines to filter and the result should match
required="0|1" -- if 1, the result collection has to meet this rule or return nothing
/>
<rule ....
</filter>
*/
@column varchar(100)
)
RETURNS @returntable TABLE
(
[Match] nvarchar(100),
[Required] bit
)
AS
BEGIN
;with tmp as
(
select
[T].[RULE].value('@column','varchar(100)') as [Column],
[T].[RULE].value('@match','nvarchar(100)') as [Match],
[T].[RULE].value('@required','bit') as [Required]
from @filter.nodes('filter//rule') [T]([RULE])
)
INSERT @returntable
select [Match],[Required] from tmp where [Column] = @column
RETURN
END
xml的结构由一个根节点<filter>开始,里面是多个<rule>节点,具体的规则定义在rule的属性列表中。column表示具体要过滤哪列的数据,match表示要匹配的数据值,比如我想要过滤列"Value"下,值为"sample"的数据,这样的话我只需要把xml写成<filter><rule column="value" match="sample" required="1" /></filter>,最后的哪个required就是来实现AND和OR的操作的,如果是1表示必须匹配,类似AND,0的话可选匹配,类似OR,输出的表示由COLUMN的值过滤过的,因为比较是以COLUMN为维度的。
最后,新建一个存储过程,叫"sp_r_fp_configuration",这里比上一个多了一个"f",表示带有过滤功能。
/*
PROCEDURE: [dbo].[sp_r_fp_configuration]
OPERATION: READ
FEATURES: Paginate, Filter
PURPOSE: provide a paginated & filterable list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE PROCEDURE [dbo].[sp_r_fp_configuration]
@pageIndex int = 1, /*which page of the total page*/
@pageSize int=10, /*how many records show in one page*/
@filter xml, /*filter string for the records*/
@recordCount int OUTPUT, /*output: return the count of the total records in all pages*/
@pageCount int OUTPUT /*output: return the count of the total pages*/
AS
if(@filter is null)
begin
exec dbo.sp_r_p_configuration
@pageIndex,
@pageSize,
@pageCount=@pageCount OUTPUT,
@recordCount=@recordCount OUTPUT
end
else
begin
declare @tmptbl table (row_id int, Id int, [Key] varchar(100), [Value] varchar(200))
declare @startRow int; -- the row number of the first record in the page
declare @endRow int; -- the row number of the last record in the page
set @startRow = (@pageIndex - 1) * @pageSize + 1;
set @endRow= @startRow + @pageSize - 1;
insert into @tmptbl
select (ROW_NUMBER() over (order by m.Id)) as row_id, m.[Id], m.[Key], m.[Value]
from dbo.Configuration as m
outer apply fun_xmlfilter_parse(@filter,'key') as k
outer apply fun_xmlfilter_parse(@filter,'value') as v
where
(
not (isnull(k.[Required],0)=0 and isnull(v.[Required],0)=0) and
(isnull(k.[Required],0)=0 or (k.[Required] = 1 and isnull(m.[Key],'') = isnull(k.[Match],''))) and
(isnull(v.[Required],0)=0 or (v.[Required] = 1 and isnull(m.[Value],'') = isnull(v.[Match],'')))
) or
k.[Required] = 0 and isnull(m.[Key],'') = isnull(k.[Match],'') or
v.[Required] = 0 and isnull(m.[Value],'') = isnull(v.[Match],'')
select [Id], [Key], [Value] from @tmptbl
where row_id between @startRow and @endRow
-- calculate the record count in all pages
select @recordCount=COUNT(*) from @tmptbl
-- calculate the page count
set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))
end
RETURN 0
这里用了一个表变量,注意表变量是可被缓存的,临时表没有哦,用表变量不用with...as是因为后面在计算过滤后的总记录数的时候,还需用一次过滤后的集合。这里我希望列Key和列Value都可以被过滤,于是用了两个outer apply来将刚才那个函数的输出表附加到每行上,在where比较的地方,先比较Required是1的必要条件,当然如果针对某个column没有rule的话,也就是required is null的情况,是需要排除的,最后再附加连个required=0的可选条件,这样的话,一个select就可以把各种条件过滤出来了。不过,有个问题是,如果需要过滤的列比较多,那比较语句也要一条条加上去。
最后,来测试下:
我给表填充了这么些数据:
SET IDENTITY_INSERT [dbo].[Configuration] ON
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (1, N'test1', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (2, N'test2', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (3, N'test3', N'567')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (4, N'test4', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (5, N'test5', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (6, N'test6', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (7, N'test7', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (8, N'test8', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (9, N'test9', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (10, N'test10', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (11, N'test11', NULL)
SET IDENTITY_INSERT [dbo].[Configuration] OFF
测试脚本:
declare @pageIndex int; set @pageIndex=1;
declare @pageSize int; set @pageSize=5;
declare @filter xml;
set @filter='<filter><rule column="key" match="test3" required="1"/><rule column="value" match="123" required="0"/></filter>'
declare @pageCount int;
declare @recordCount int;
exec dbo.sp_r_fp_configuration @pageIndex,@pageSize,@filter,@pageCount=@pageCount OUTPUT, @recordCount=@recordCount OUTPUT
select @pageCount as [PAGECOUNT], @recordCount as [RECORDCOUNT]
第一期望:我希望输入页码(pageIndex),每页显示的记录数(pageSize),然后这个存储过程就可以给我一个当前页的数据集。
首先,我假设有个表叫Configuration,里面就3个字段,Id, Key, Value,就是一个简单的数据表,定义如下:
CREATE TABLE [dbo].[Configuration]
(
[Id] int NOT NULL IDENTITY (1,1),
[Key] varchar(20) NOT NULL UNIQUE,
[Value] nvarchar(max) NULL
)
然后,就可以写存储过程了,这里想要说下,我对存储过程命名的一些习惯,我会以"sp_"开头,为了标明这个存储过程是CRUD中的哪个操作,我会再附加一个缩写,比如这个分页的主要操作时READ,所以我的名字应该是"sp_r_",然后为了说明它的主要功能是分页,就有了"sp_r_p_",“p”表示paginate,最后跟上操作的主要的表名,"sp_r_p_configuration"。很多同学都喜欢用比如bypage等等,也可以,但是我更喜欢这种命名法,在特定的约定下,这种简洁性的命名可可读性。
再者,一个良好的代码书写规范是非常必要的,该注释的地方千万别省,在多数情况下,代码的可读性是非常重要的,除非因性能因素做必须得让步。
/*
PROCEDURE: [dbo].[sp_r_p_configuration]
OPERATION: READ
FEATURES: Paginate
PURPOSE: provide a paginated list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE PROCEDURE [dbo].[sp_r_p_configuration]
@pageIndex int = 1, /*which page of the total page*/
@pageSize int=10, /*how many records show in one page*/
@recordCount int OUTPUT, /*output: return the count of the total records in all pages*/
@pageCount int OUTPUT /*output: return the count of the total pages*/
AS
declare @startRow int; -- the row number of the first record in the page
declare @endRow int; -- the row number of the last record in the page
set @startRow = (@pageIndex - 1) * @pageSize + 1;
set @endRow= @startRow + @pageSize - 1;
;with tmp as
(select (ROW_NUMBER() over(order by Id)) as row_id, [Id], [Key], [Value]
from dbo.Configuration)
select [Id], [Key], [Value] from tmp
where row_id between @startRow and @endRow
-- calculate the record count in all pages
select @recordCount=COUNT(*) from dbo.Configuration
-- calculate the page count
set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))
RETURN 0
这里用的ROW_NUMBER()来实现分页,性能必拼SQL的方法好,WITH...AS可以省下一个表变量。最后计算总记录数和页数很简单,算下就好了。
第二期望:这样的,似乎差不多了,但是我还希望能够有一套规则,来过滤结果集,并且还要有类似AND和OR的运算功能,而且我仍旧不希望用inline-script去实现,能不用尽量不要用。
为了实现这个期望,我需要先定义怎么传入过滤规则,我最终决定用的是xml数据类型而不是普通字符串,因为普通字符串有长度限制(如果你说可以用text,那我只能说太奢侈,小弟用不起),定短了可能被截断出现错误输出,定长了,最多也就8000个字符,也浪费,我不喜欢不确定因素,然而xml不一样,既是结构化数据,又有索引支持,可长可短,客户端也可直接序列化成字符串传入,非常方便。于是剩下的是,约定一个规则,确定过滤规则的数据结构,在这里我专门写了一个function来将xml转换成table已被后用。P.S. 如果想传一个数组到存储过程,也可以用xml类型,比字符串加分隔符更安全。
/*
FUNCTION: [dbo].[fun_xmlfilter_parse]
OPERATION: READ
FEATURES:
PURPOSE: parse a table from a xml with the specific column name
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE FUNCTION [dbo].[fun_xmlfilter_parse]
(
@filter xml, /* The format should be:
<filter>
<rule
column="COLUMN_NAME" -- the column name which the rule should be applied
match="xxx" -- the key which the rule defines to filter and the result should match
required="0|1" -- if 1, the result collection has to meet this rule or return nothing
/>
<rule ....
</filter>
*/
@column varchar(100)
)
RETURNS @returntable TABLE
(
[Match] nvarchar(100),
[Required] bit
)
AS
BEGIN
;with tmp as
(
select
[T].[RULE].value('@column','varchar(100)') as [Column],
[T].[RULE].value('@match','nvarchar(100)') as [Match],
[T].[RULE].value('@required','bit') as [Required]
from @filter.nodes('filter//rule') [T]([RULE])
)
INSERT @returntable
select [Match],[Required] from tmp where [Column] = @column
RETURN
END
xml的结构由一个根节点<filter>开始,里面是多个<rule>节点,具体的规则定义在rule的属性列表中。column表示具体要过滤哪列的数据,match表示要匹配的数据值,比如我想要过滤列"Value"下,值为"sample"的数据,这样的话我只需要把xml写成<filter><rule column="value" match="sample" required="1" /></filter>,最后的哪个required就是来实现AND和OR的操作的,如果是1表示必须匹配,类似AND,0的话可选匹配,类似OR,输出的表示由COLUMN的值过滤过的,因为比较是以COLUMN为维度的。
最后,新建一个存储过程,叫"sp_r_fp_configuration",这里比上一个多了一个"f",表示带有过滤功能。
/*
PROCEDURE: [dbo].[sp_r_fp_configuration]
OPERATION: READ
FEATURES: Paginate, Filter
PURPOSE: provide a paginated & filterable list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0
*/
CREATE PROCEDURE [dbo].[sp_r_fp_configuration]
@pageIndex int = 1, /*which page of the total page*/
@pageSize int=10, /*how many records show in one page*/
@filter xml, /*filter string for the records*/
@recordCount int OUTPUT, /*output: return the count of the total records in all pages*/
@pageCount int OUTPUT /*output: return the count of the total pages*/
AS
if(@filter is null)
begin
exec dbo.sp_r_p_configuration
@pageIndex,
@pageSize,
@pageCount=@pageCount OUTPUT,
@recordCount=@recordCount OUTPUT
end
else
begin
declare @tmptbl table (row_id int, Id int, [Key] varchar(100), [Value] varchar(200))
declare @startRow int; -- the row number of the first record in the page
declare @endRow int; -- the row number of the last record in the page
set @startRow = (@pageIndex - 1) * @pageSize + 1;
set @endRow= @startRow + @pageSize - 1;
insert into @tmptbl
select (ROW_NUMBER() over (order by m.Id)) as row_id, m.[Id], m.[Key], m.[Value]
from dbo.Configuration as m
outer apply fun_xmlfilter_parse(@filter,'key') as k
outer apply fun_xmlfilter_parse(@filter,'value') as v
where
(
not (isnull(k.[Required],0)=0 and isnull(v.[Required],0)=0) and
(isnull(k.[Required],0)=0 or (k.[Required] = 1 and isnull(m.[Key],'') = isnull(k.[Match],''))) and
(isnull(v.[Required],0)=0 or (v.[Required] = 1 and isnull(m.[Value],'') = isnull(v.[Match],'')))
) or
k.[Required] = 0 and isnull(m.[Key],'') = isnull(k.[Match],'') or
v.[Required] = 0 and isnull(m.[Value],'') = isnull(v.[Match],'')
select [Id], [Key], [Value] from @tmptbl
where row_id between @startRow and @endRow
-- calculate the record count in all pages
select @recordCount=COUNT(*) from @tmptbl
-- calculate the page count
set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))
end
RETURN 0
这里用了一个表变量,注意表变量是可被缓存的,临时表没有哦,用表变量不用with...as是因为后面在计算过滤后的总记录数的时候,还需用一次过滤后的集合。这里我希望列Key和列Value都可以被过滤,于是用了两个outer apply来将刚才那个函数的输出表附加到每行上,在where比较的地方,先比较Required是1的必要条件,当然如果针对某个column没有rule的话,也就是required is null的情况,是需要排除的,最后再附加连个required=0的可选条件,这样的话,一个select就可以把各种条件过滤出来了。不过,有个问题是,如果需要过滤的列比较多,那比较语句也要一条条加上去。
最后,来测试下:
我给表填充了这么些数据:
SET IDENTITY_INSERT [dbo].[Configuration] ON
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (1, N'test1', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (2, N'test2', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (3, N'test3', N'567')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (4, N'test4', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (5, N'test5', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (6, N'test6', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (7, N'test7', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (8, N'test8', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (9, N'test9', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (10, N'test10', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (11, N'test11', NULL)
SET IDENTITY_INSERT [dbo].[Configuration] OFF
测试脚本:
declare @pageIndex int; set @pageIndex=1;
declare @pageSize int; set @pageSize=5;
declare @filter xml;
set @filter='<filter><rule column="key" match="test3" required="1"/><rule column="value" match="123" required="0"/></filter>'
declare @pageCount int;
declare @recordCount int;
exec dbo.sp_r_fp_configuration @pageIndex,@pageSize,@filter,@pageCount=@pageCount OUTPUT, @recordCount=@recordCount OUTPUT
select @pageCount as [PAGECOUNT], @recordCount as [RECORDCOUNT]