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 = %26#39;error!%26#39;;
END;
-- BODY start --
if(iwhere %26lt;%26gt; %26#39;%26#39;) then
set iwhere = %26#39; where %26#39; || iwhere;
end if;
if(iorder %26lt;%26gt; %26#39;%26#39;) then
set iorder = %26#39;order by %26#39; || iorder;
end if;
if(ijoin %26lt;%26gt; %26#39;%26#39;) then
set ijoin = %26#39; %26#39; || ijoin;
end if;
set strsql = %26#39;select count(*) from %26#39; || 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 %26lt; 1) then
set iocurrentpageix = 1;-- 当前页
else
if(iocurrentpageix %26gt; 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 %26gt; 1) then
set ohaspreviouspage = 1;-- 是否有上一页
else
set ohaspreviouspage = 0;
end if;
if(iocurrentpageix %26lt; ototalpages) then
set ohasnextpage = 1;-- 是否有下一页
else
set ohasnextpage = 0;
end if;
set strsql = %26#39;select * from (select rownumber() over(%26#39; || iorder || %26#39;) as rownum,%26#39;
|| ishowfield
|| %26#39; from %26#39;
|| itbname
|| ijoin
|| iwhere
|| %26#39;) as temp where rownum between %26#39; || rtrim(char(opagestartrow)) || %26#39; and %26#39; || rtrim(char(opageendrow));
prepare s2 from strsql;
open result;
-- BODY end --
END
DB2中通用的存储过程分页程序
2008-02-09 10:35:42 作者
相关文章