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