千万数量级分页存储过程

武侠㿝卄清

于 2019-05-09 09:57:19 发布

阅读量247

点赞数

CC 4.0 BY-SA版权

分类专栏： java web开发编程语言

本文链接：https://blog.youkuaiyun.com/qq_30347133/article/details/90020708

web开发同时被 3 个专栏收录

611 篇文章

订阅专栏

java

574 篇文章

订阅专栏

编程语言

272 篇文章

订阅专栏

GO
/****** Object: StoredProcedure [dbo].[PR_Page] Script Date: 05/09/2019 09:53:54 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------------分页存储过程------------------------------------------------------------
ALTER PROCEDURE [dbo].[PR_Page]
/********************************************************
** 千万数量级分页存储过程 **
**************************************************************/
(
@Tables varchar(1000), --1.Tables :表名称,视图, 可为多个多个表之间用逗号分隔，别名和表名之间以一个空格 AS 别名分隔，
@PrimaryKey varchar(300), --2.PrimaryKey :主关键字对应各表的关键字，可加前缀如a.id,b.name,以逗号分隔各关键字
@LinkKey varchar(1000), --3.@LinkKey 各表之间的关联关系如 EMP a join AUTHOR b on a.id=b.id left join BOOK C ON c.id=a.id and c.id=b.id
@Fields varchar(3000) = '’, --4.Fields :显示的字段
@Filter varchar(2000) = NULL, --5.Filter :条件语句，不带Where
@Sort varchar(300) = NULL, --6.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
@Group varchar(1000) = NULL, --7.Group :Group语句,不带Group By
@CurrentPage int = 1, --8.CurrentPage :当前页码
@PageSize int = 10, --9.PageSize :分页尺寸
@RecordCount int =0 OUTPUT --10.@RecordCount 输出总记录数
)
AS
BEGIN
declare @SortTable varchar(200) --排序表
declare @Coltype varchar(200) --排序列名
declare @prec int,@SpaceAs int
declare @iCountTable int ,@iColLen int
declare @TempTables varchar(2000),@TempKeys varchar(300),@TempName varchar(100)
declare @temp varchar(100), @AliasName varchar(100),@TempKey varchar(100) ,@TempTableKEY varchar(100)
declare @StrDeclare varchar(500);
declare @StrFields varchar(3000);
declare @I INT;
declare @SCon varchar(3000);
declare @SCon1 varchar(1000), @SCon2 varchar(1000),@sTemp varchar(1000)
declare @StrOrder varchar(500)
declare @AliasName1 varchar(500);
declare @sSQL varchar(8000)
declare @sFilter varchar(2000)
declare @sOrder varchar(1000)
declare @strPageSize varchar(10)
declare @strStartRow varchar(10)
declare @sqlsCount nvarchar(4000)
declare @sGroup varchar(500)
declare @Opt int
declare @num int
CREATE TABLE #TempTable (TableName varchar(100), AliasName varchar(100))
CREATE TABLE #TempTableKEY (A1 int IDENTITY (1, 1) NOT NULL,TableName varchar(100), AliasName varchar(100),
COLName varchar(100), ColType varchar(50), ColLen integer, opt int DEFAULT (0))

/********************************该处分隔多个表名信息*******************************/
--set @TempTables=ltrim(rtrim(@Tables))
set @TempTables=ltrim(rtrim(@Tables))+','
set @prec=CHARINDEX(',', @TempTables)
set @iCountTable=1;

while @prec > 0
begin
	set @temp = rtrim(ltrim(left(@TempTables,@prec-1)));
	set @TempTables=rtrim(ltrim(right(@TempTables,len(@TempTables)-@prec)));
	/*******取出表名信息以及别名信息*****/
	set @SpaceAs= charIndex(' AS ',upper(@temp))
	if @SpaceAs>0
	begin
		set @AliasName= rtrim(ltrim(right(@temp,len(@temp)-@SpaceAs-3)));
		set @temp=rtrim(ltrim(left(@temp,@SpaceAs-1)));
	end
	else 
	begin   
		set @SpaceAs= charIndex(' ',@temp)
		if @SpaceAs>0
		begin
			set @AliasName= rtrim(ltrim(right(@temp,len(@temp)-@SpaceAs+1)));
	  		set @temp=rtrim(ltrim(left(@temp,@SpaceAs-1)));
		end
		else
			set @AliasName=@temp;
	end
	insert into #TempTable values(@temp,@AliasName);
	
	--print '@TempName ='+@temp + ' AliasName='+@AliasName
	--print '@iCountTable='+cast(@iCountTable as varchar(10));
	--继续取得下个表名信息
	set @prec=CHARINDEX(',', @TempTables)
	set @iCountTable=@iCountTable+1;
end
/**********************************该处分隔多个排序字段名信息***************************************/
if @Sort IS NOT NULL AND rtrim(ltrim(@Sort)) != ''
begin	
	--set @TempTableKEY=ltrim(rtrim(@Sort))
	set @TempTableKEY=ltrim(rtrim(@Sort))+','
	set @prec=CHARINDEX(',', @TempTableKEY)
	set @opt=0;

	while @prec > 0
	begin
		set @temp = rtrim(ltrim(left(@TempTableKEY,@prec-1)));--逗号之前部分
		set @TempTableKEY=rtrim(ltrim(right(@TempTableKEY,len(@TempTableKEY)-@prec)));--逗号之后部分
		/*******取出排序字段名信息以及表别名信息*****/
		set @SpaceAs= charIndex('.',@temp)
		if @SpaceAs>0
		begin
            set @TempKey= rtrim(ltrim(right(@temp,len(@temp)-@SpaceAs)));--字段名 含DESC 或者ASC 
			set @AliasName=rtrim(ltrim(left(@temp,@SpaceAs-1)));         --表别名
		end
		else			
		begin		
			set @TempKey= rtrim(ltrim(@temp))
			set @AliasName='';
		end
		/***********************判断正序 倒序********************/
		set @SpaceAs= charIndex(' DESC',UPPER(@TempKey))
		if @SpaceAs>0
		begin
            set @TempKey= left(rtrim(ltrim(@TempKey)),@SpaceAs-1); --去除DESC或者ASC
			set @Opt=1
			--print '@TempKey==='+@TempKey;
		end
		else			
		begin		
			set @SpaceAs= charIndex(' ASC',UPPER(@TempKey))
			if @SpaceAs>0
				set @TempKey= left(rtrim(ltrim(@TempKey)),@SpaceAs-1); --去除DESC或者ASC
			set @Opt=0
			--print '@TempKey======='+@TempKey;
		end

		select @TempName=TableName from #TempTable 
		where tablename=@AliasName or AliasName=@AliasName or (@AliasName='' and tablename=AliasName)

		SELECT @Coltype=t.name, @iColLen=c.prec
		FROM sysobjects o 
		JOIN syscolumns c on o.id=c.id
		JOIN systypes t on c.xusertype=t.xusertype
		WHERE o.name = @TempName AND c.name = @TempKey

		insert into #TempTableKEY (TableName, AliasName ,COLName, ColType, ColLen, opt)
			values(@TempName, @AliasName, @TempKey, @Coltype,@iColLen,@opt);
		
		--继续取得下个表名信息
		set @prec=CHARINDEX(',', @TempTableKEY)
	end
end


/**********************************该处分隔多个主键名信息***************************************/
--set @TempTableKEY=ltrim(rtrim(@PrimaryKey))
set @TempTableKEY=ltrim(rtrim(@PrimaryKey))+','
set @prec=CHARINDEX(',', @TempTableKEY)
set @opt=0;
while @prec > 0
begin
	set @temp = rtrim(ltrim(left(@TempTableKEY,@prec-1)));
	set @TempTableKEY=rtrim(ltrim(right(@TempTableKEY,len(@TempTableKEY)-@prec)));
	/*******取出主键名信息以及表别名信息*****/
	set @SpaceAs= charIndex('.',@temp)
	if @SpaceAs>0
	begin
        set @TempKey= rtrim(ltrim(right(@temp,len(@temp)-@SpaceAs)));
		set @AliasName=rtrim(ltrim(left(@temp,@SpaceAs-1)));
	end
	else			
	begin		
		set @TempKey= rtrim(ltrim(@temp))
		set @AliasName='';
	end

	select @TempName=TableName from #TempTable 
	where tablename=@AliasName or AliasName=@AliasName or (@AliasName='' and tablename=AliasName)
	if not exists(select * from #TempTableKEY where  TableName=@TempName and  COLName=@TempKey)
	begin
		SELECT @Coltype=t.name, @iColLen=c.prec
		FROM sysobjects o 
		JOIN syscolumns c on o.id=c.id
		JOIN systypes t on c.xusertype=t.xusertype
		WHERE o.name = @TempName AND c.name = @TempKey

		insert into #TempTableKEY (TableName, AliasName,COLName, ColType, ColLen, opt)
			values(@TempName,@AliasName, @TempKey, @Coltype,@iColLen,@opt);
	end
	--继续取得下个表名信息
	set @prec=CHARINDEX(',', @TempTableKEY)
end

/*****************************根据临时表的主键定义变量******************************/
DECLARE TempKeyCursor CURSOR FOR 
	Select a.TableName,a.COLName,a.ColType,a.ColLen,AliasName ,A.opt
	From  #TempTableKEY a 
	ORDER BY A.A1
open TempKeyCursor ;
declare @sOpt varchar(2);
SET @I=0;
set @StrFields=''
set @StrDeclare=''
set @SCon=''
set @SCon1=''
set @SCon2=''
set @Stemp=''
set @StrOrder=' '
SET @OPT=0;
FETCH NEXT FROM TempKeyCursor INTO @TempName,@TempKey, @Coltype, @iColLen, @AliasName,@OPT

WHILE @@FETCH_STATUS = 0
BEGIN
	IF CHARINDEX('char', @Coltype) > 0
		SET @Coltype = @Coltype + '(' + CAST(@iColLen AS varchar) + ')'
	set @StrDeclare=@StrDeclare+'
	declare @SORTKEY'+CAST(@I AS VARCHAR(5))+ ' '+@Coltype;
	if @AliasName=''
		set @AliasName1=@TempKey
	else
		set @AliasName1=@AliasName+'.'+@TempKey
	set @StrFields=@StrFields+'@SORTKEY'+CAST(@I AS VARCHAR(5))+'=isnull('+@AliasName1+',''''),'
	
	if @OPT=0 
	BEGIN
		set @sOPT='>'
		set @StrOrder=@StrOrder+'isnull('+@AliasName1+','''') ,'
	END
	else 
	BEGIN
		set @sOPT='<'
		set @StrOrder=@StrOrder+'isnull('+@AliasName1+','''') DESC,'
	END

	FETCH NEXT FROM TempKeyCursor INTO @TempName,@TempKey, @Coltype, @iColLen, @AliasName,@opt
	set @sTemp=@sCon1;
	if @@FETCH_STATUS<0 
	begin
		set @sCon1= ''
		if @sTemp<>''
			set @sCon2= @sTemp+' and isnull('+@AliasName1+','''')'+ @sOpt+'='+'@SORTKEY'+CAST(@I AS VARCHAR(5))
		else
			set @sCon2='isnull('+@AliasName1+','''')'+ @sOpt+'='+'@SORTKEY'+CAST(@I AS VARCHAR(5))
	end
	else
	begin
		if @sTemp<>''
		begin
			set @sCon1=@sTemp+' and isnull('+@AliasName1+','''')='+'@SORTKEY'+CAST(@I AS VARCHAR(5))
			set @sCon2=@sTemp+' and isnull('+@AliasName1+','''')'+ @sOpt+'@SORTKEY'+CAST(@I AS VARCHAR(5))
		end
		else
		begin
			set @sCon1='isnull('+@AliasName1+','''')='+'@SORTKEY'+CAST(@I AS VARCHAR(5))
			set @sCon2='isnull('+@AliasName1+','''')'+ @sOpt+'@SORTKEY'+CAST(@I AS VARCHAR(5))
		end
	end
	if @sCon='' 
		set @sCon=@sCon2
	else
		set @sCon=@Scon+' or ('+@sCon2+')'; 
	set @I=@I+1
END
set @SCon=' and ('+@SCon+')'
CLOSE TempKeyCursor
DEALLOCATE TempKeyCursor

/*设置分页参数.*/
IF @CurrentPage < 1
	SET @CurrentPage = 1	
SET @strPageSize = CAST(@PageSize AS varchar(10))
SET @strStartRow = CAST(((@CurrentPage - 1) * @PageSize + 1) AS varchar(10))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND  rtrim(ltrim(@Filter)) != ''
	SET @sFilter = ' WHERE ' + @Filter + ' '
ELSE
	SET @sFilter = ' WHERE 1=1 '
set @StrOrder=left(@StrOrder,len(@StrOrder)-1)
set @sOrder=' ORDER BY '+ @StrOrder
if @GROUP IS NOT NULL AND  rtrim(ltrim(@GROUP)) != '' 
	set @sGroup=' GROUP BY '+@GROUP
ELSE 
	set @sGroup=''

set @StrFields =left(@StrFields,len(@StrFields)-1)
set @sSQL = @StrDeclare+'
	SET ROWCOUNT ' + @strStartRow + '
	SELECT '+ @StrFields + ' from '+@LinkKey + @sFilter +@sGroup +@sOrder+' 
	SET ROWCOUNT ' + @strPageSize + '
	SELECT '+ @Fields + ' from '+@LinkKey +@sFilter + @SCon  + ' '  +@sGroup +  @sOrder + ''
--返回的结果集
--print @SSQL;
EXEC(@sSQL);
--计算记录总数量
    if @Group<>''
  begin
      set @sqlsCount=N'SELECT @Count=count(*) FROM (select count(*) a from ' + @LinkKey + @sFilter+ 'group by '+@Group+') a';
  end
    else
      begin
         set @sqlsCount=N'SELECT @Count=count(*) FROM ' + @LinkKey + @sFilter;
      end
execute sp_executesql 
	@sqlsCount,    --执行上面的sql语句
	N'@Count int OUTPUT' ,   --执行输出数据的sql语句，output出总记录数
	@RecordCount  OUTPUT
SET ROWCOUNT 0

END