DB2是IBM出口的一系列關系型數據庫管理系統，分別在不同的操作系統平臺上服務。雖然DB2產品是基于UNIX的系統和個人計算機操作系統，在基于UNIX系統和微軟在windows系統下的Access方面，DB2追尋了ORACLE的數據庫產品。

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 1MODIFIES SQL DATANOT DETERMINISTICLANGUAGE SQLBEGIN/**//*----------------------------------------------------------------* Copyright (C) 2007; Huacius* 版權所有。** 存儲過程分頁**//-----------------------------------------------------------------------*/DECLARE STRSQLVARCHAR(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