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 SQL

BEGIN

/**//*----------------------------------------------------------------

* Copyright (C) 2007  Huacius

* 版权所有。

*

* 存储过程分页

*

*

//-----------------------------------------------------------------------*/

DECLARE STRSQL   VARCHAR(6000);-- 主语句
DECLARE result CURSOR WITH RETURN TO CALLER FOR S2;
DECLARE exit handler FOR sqlexception-- 异常捕获

BEGIN

set OERROR = 'error!';

END;
-- BODY start --

if(iwhere <> '') then

set iwhere = ' where ' || iwhere;

end if;

if(iorder <> '') then

set iorder = 'order by ' || iorder;

end if;

if(ijoin <> '') then

set 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) then

set ipagesize = 1000000;-- 每页显示数

end if;
set ototalpages = (ototalrows - 1) / ipagesize + 1;-- 总页数
if(iocurrentpageix < 1) then

set iocurrentpageix = 1;-- 当前页

else

if(iocurrentpageix > ototalpages) then

set iocurrentpageix = ototalpages;

end if;

end if;
set opagestartrow = ipagesize * (iocurrentpageix -1) + 1;-- 每页开始数

if(iocurrentpageix = ototalpages) then

set opageendrow = ototalrows;-- 每页结束数

else

set opageendrow = ipagesize * iocurrentpageix;

end if;
if(iocurrentpageix > 1) then

set ohaspreviouspage = 1;-- 是否有上一页

else

set ohaspreviouspage = 0;

end if;
if(iocurrentpageix < ototalpages) then

set ohasnextpage = 1;-- 是否有下一页

else

set 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