-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 来自: 新客网(www.xker.com) 详文参考:http://www.xker.com/page/e2007/0904/33426.html版权声明: 原创作品,许可转载,转载时请务必以超链接形式标明文章 原始情由 、作者信息和本声明。否则将追查法律责任。