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