现在的位置: 首页 > 综合 > 正文

SQL 2005下千万数量级分页存储过程

2018年01月30日 ⁄ 综合 ⁄ 共 5565字 ⁄ 字号 评论关闭

随着信息时代的发展信息系统的使用越来越多,信息量越来越大;当信息量越来越大,这在数据
展示,特别是报表这块对系统展现效率要求越来越高,对于千万级数据量的展示必须得使用分页来展示。

 

If object_id('Middle_Table_SP_Pagination')is not null
drop proc Middle_Table_SP_Pagination
go
Create  PROCEDURE Middle_Table_SP_Pagination
/**//*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:     
1.Tables :表名称,视图(试图这边目前还有点小问题)
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where 
7.Group :Group语句,不带Group By
8.RecordCount : 返回记录总数
***************************************************************/
(
@Tables varchar(2000),
@PrimaryKey varchar(500),
@Sort varchar(500) = NULL,
@CurrentPage int = 1,
@PageSize int = 5,
@Fields varchar(2000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@RecordCount int OUTPUT
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(1000)
DECLARE @SortName varchar(1000)
DECLARE @strSortColumn varchar(1000)
DECLARE @operator char(2)
DECLARE @type varchar(1000)
DECLARE @prec int 
DECLARE @str NVarchar(600) 

/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
print '1'
print REPLACE(@Sort, 'ASC', '')
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
print @strSortColumn
SET @operator = '>='
print @operator
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
print @SortTable
print @SortName
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
--print @type
--print @prec
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(500)
DECLARE @strStartRow varchar(500)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/**//*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/**//*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(500))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(500))
/**//*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*print @type
print @strStartRow
print @strSortColumn
print @Tables
print @strFilter
print @strGroup
print @Sort*/
 

/*执行查询语句 获取总记录数 */
select @str=N'select @recordcount=count(*) from '+@Tables+ @strFilter
Execute sp_executesql @str,N'@recordcount int OUTPUT',@RecordCount OUTPUT

EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)

下面是在数据库查询分析器里面怎么调用这个存储过程的实例。如图所示为ProDetailTable原表查询的结果集。

begin
    DECLARE @strFields NVARCHAR(4000);	
    DECLARE @strWhere NVARCHAR(4000);	 
     --字段
   	set @strFields='ID,Pro001,Pro002,Pro003,Pro004,Pro005,Pro006 '
    --条件
   set @strWhere='1=1' 	 
   set @strWhere=@strWhere+' AND pro0010 like ''2014%'''
    --分页部分
     declare @RecordCount int		 
exec Middle_Table_SP_Pagination 'ProDetailTable','ID','ID asc',1,100,'*',@strWhere,'',@RecordCount output
     select @RecordCount as pagecount
end

 

以ProDetailTable表为例执行分页存储过程脚本:exec SP_Pagination 'ProDetailTable','Id','Id asc',1,5,'*','',''
执行上述SQL结果如图所示:

 

 

通过上面分页存储过程,把千万级数据从SQL 2005中写入到Oracle中

调用代码如下关于SQL 2005访问Oracle方法请参照网上资料

下面是调用代码

Begin
    DECLARE @strFields NVARCHAR(4000);	
    DECLARE @strWhere NVARCHAR(4000);	

    DECLARE @recordCount int  --记录总数
    DECLARE @pageCount int  --总页数
    DECLARE @pageSize int -- 每页记录数
    SET @recordCount = 0
	SET @pageSize = 2000
    SET @pageCount = 1
     --CONVERT(varchar(12) , getdate(), 112 )
    -- 需要输出的字段
    SET @strFields='ID, Pro002, Pro004, Pro005, Pro006, Pro001, substring(Pro002,5,4) as charge'
	+', 1, CONVERT(varchar(12) , getdate(), 112 ) '
    
			
    -- 查询条件
    set @strWhere='1=1 ' 	 
   set @strWhere=@strWhere+' AND Pro0017 = ''2'' AND (LEFT(Pro0010, 5) = ''2013-'' OR LEFT(Pro0010, 5) = ''2014-'')  ' ----AND LEFT(Pro002, 1) != ''2''   
   update dbo.ProDetailTable set Pro0017 = '2' where Pro0017 = '已审核' AND (LEFT(Pro0010, 5) = '2013-' OR LEFT(Pro0010, 5) = '2014-'); ----- AND LEFT(Pro002, 1) != '2' 
    --查总记录数
     
    SELECT @recordCount = count(*) FROM ProDetailTable WHERE Pro0017 = '2' AND (LEFT(Pro0010, 5) = '2013-' OR LEFT(Pro0010, 5) = '2014-'); ----WHERE LEFT(Pro002, 1) != '2' 
    IF (@recordCount / @pageSize) > 0
		begin
			set @pageCount = (@recordCount / @pageSize) + 1 
		end
    ELSE 
		begin
			set @pageCount = (@recordCount / @pageSize)  
		end 
    ---- 开始数据操作,先更新需要操作的数据状态值
    ---事务开始,
    --set xact_abort on ---防止NUll异常
    --begin tran
    WHILE @pageCount > 0
    BEGIN  
        --采用分页读取数据插入到临时表中
	--允许将显式值插入表的标识列中 ON-允许  OFF-不允许
	
	delete from dbo.temp_ProDetailTable;
	set identity_insert temp_ProDetailTable ON--打开
	INSERT INTO dbo.temp_ProDetailTable (ID, Pro002, Pro004, Pro005, Pro006, Pro001, charge, [status], create_date) 
	exec Middle_Table_SP_Pagination 'ProDetailTable','ID','ID asc',@pageCount, @pageSize, @strFields,@strWhere,'';
	/*
	if @@error <> 0  
		begin rollback tran   
		goto labend   
	end       
	*/

	INSERT OPENQUERY (MYSAP,
	 'SELECT bar_code,m_barcode,b_barcode,short_code,p_barcode,charg,status,create_date  
	FROM ZSDSS_BAR_CODE_DATA')  
	SELECT Pro002, Pro004, Pro005, Pro006, Pro001, charge, [status],Create_Date FROM dbo.temp_ProDetailTable;
    ---根据临时表来更新主表 
	update ProDetailTable set Pro0017 = '1' where ID IN (select ID from dbo.temp_ProDetailTable);
     
	
	---delete from dbo.temp_ProDetailTable
		/*
	if @@error <> 0  
		begin rollback tran   
		goto labend   
	end       
	*/
	

	SET @pageCount = @pageCount - 1
 
    END 

     /*
		update dbo.ProDetailTable set Pro0017 = '1' where Pro0017 = '2' and Pro002 not like '2%'
		if @@error <> 0  
		begin rollback tran   
		goto labend   
		end   
	
   commit tran   
   labend:
*/
END

 

抱歉!评论已关闭.