本文介绍了使用DB2的存储过程进行分页的源代码。

CREATE PROCEDURE SALES.DB2PAGINATION(IN ITBNAME VARCHAR(2000),-- 表名IN ISHOWFIELD VARCHAR(1000),-- 显示字段IN IJOIN VARCHAR(1000),-- 联接条件（如：内联、外联）IN IWHERE VARCHAR(2000),-- 查询条件 (注意: 不要加 WHERE)IN IORDER VARCHAR(100),-- 排序条件 (注意: 不要加 ORDER BY)IN IPAGESIZE INTEGER,-- 页尺寸 如果为0 默认返回前一百万条数据可以认为是返回所有数据INOUT IOCURRENTPAGEIX INTEGER,-- 输入和输出：当前页OUT OPAGESTARTROW INTEGER,-- 输出：当前开始行OUT OPAGEENDROW INTEGER,-- 输出：当前结束行 OUT OTOTALROWS INTEGER,-- 输出：当前总记录数OUT OHASPREVIOUSPAGE INTEGER,-- 输出：是否有上一页 OUT OHASNEXTPAGE INTEGER,-- 输出：是否有下一页 OUT OTOTALPAGES INTEGER,-- 输出：总页数 OUT OERROR VARCHAR(100))-- 输出：错误信息 RESULT SETS 1 MODIFIES SQL DATA NOT DETERMINISTIC LANGUAGE SQLBEGIN/*----------------------------------------------------------------* Copyright (C) 2007 Huacius* 版权所有。** 存储过程分页**//-----------------------------------------------------------------------*/DECLARE STRSQL VARCHAR(6000);-- 主语句

DECLARE result CURSOR WITH RETURN TO CALLER FOR S2;

DECLARE exit handler FOR sqlexception-- 异常捕获BEGINset OERROR = 'error!';END;

-- BODY start --if(iwhere <> '') thenset iwhere = ' where ' || iwhere;end if;if(iorder <> '') thenset iorder = 'order by ' || iorder;end if;if(ijoin <> '') thenset ijoin = ' ' || ijoin;end if;set strsql = 'select count(*) from ' || itbname || ijoin || iwhere;prepare s2 from strsql;open result; fetch result into ototalrows;-- 总记录数 close result;

if(ipagesize = 0) thenset ipagesize = 1000000;-- 每页显示数end if;

set ototalpages = (ototalrows - 1) / ipagesize + 1;-- 总页数

if(iocurrentpageix < 1) thenset iocurrentpageix = 1;-- 当前页elseif(iocurrentpageix > ototalpages) thenset iocurrentpageix = ototalpages;end if;end if;

set opagestartrow = ipagesize * (iocurrentpageix -1) + 1;-- 每页开始数if(iocurrentpageix = ototalpages) thenset opageendrow = ototalrows;-- 每页结束数elseset opageendrow = ipagesize * iocurrentpageix;end if;

if(iocurrentpageix > 1) thenset ohaspreviouspage = 1;-- 是否有上一页elseset ohaspreviouspage = 0;end if;

if(iocurrentpageix < ototalpages) thenset ohasnextpage = 1;-- 是否有下一页elseset ohasnextpage = 0;end if;

set strsql = 'select * from (select rownumber() over(' || iorder || ') as rownum,'|| ishowfield|| ' from '|| itbname|| ijoin|| iwhere|| ') as temp where rownum between ' || rtrim(char(opagestartrow)) || ' and ' || rtrim(char(opageendrow));prepare s2 from strsql;open result;-- BODY end --

END

本文由翔宇亭IT乐园（http://）提供，有什么意见或建议请留言评论。