DB2中通用的存储过程分页程序

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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) 2007Huacius

* 版权所有。

*

* 存储过程分页

*

*

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

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航