DB2是IBM出口的一系列關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，分別在不同的操作系統(tǒng)平臺(tái)上服務(wù)。雖然DB2產(chǎn)品是基于UNIX的系統(tǒng)和個(gè)人計(jì)算機(jī)操作系統(tǒng)，在基于UNIX系統(tǒng)和微軟在windows系統(tǒng)下的Access方面，DB2追尋了ORACLE的數(shù)據(jù)庫(kù)產(chǎn)品。

CREATE PROCEDURE SALES.DB2PAGINATION(IN ITBNAME VARCHAR(2000),-- 表名IN ISHOWFIELD VARCHAR(1000),-- 顯示字段IN IJOIN VARCHAR(1000),-- 聯(lián)接條件（如：內(nèi)聯(lián)、外聯(lián)）IN IWHERE VARCHAR(2000),-- 查詢條件 (注意: 不要加 WHERE)IN IORDER VARCHAR(100),-- 排序條件 (注意: 不要加 ORDER BY)IN IPAGESIZE INTEGER,-- 頁(yè)尺寸 如果為0 默認(rèn)返回前一百萬(wàn)條數(shù)據(jù)可以認(rèn)為是返回所有數(shù)據(jù)INOUT IOCURRENTPAGEIX INTEGER,-- 輸入和輸出：當(dāng)前頁(yè)OUT OPAGESTARTROW INTEGER,-- 輸出：當(dāng)前開(kāi)始行OUT OPAGEENDROW INTEGER,-- 輸出：當(dāng)前結(jié)束行OUT OTOTALROWS INTEGER,-- 輸出：當(dāng)前總記錄數(shù)OUT OHASPREVIOUSPAGE INTEGER,-- 輸出：是否有上一頁(yè)OUT OHASNEXTPAGE INTEGER,-- 輸出：是否有下一頁(yè)OUT OTOTALPAGES INTEGER,-- 輸出：總頁(yè)數(shù)OUT OERROR VARCHAR(100))-- 輸出：錯(cuò)誤信息RESULT SETS 1MODIFIES SQL DATANOT DETERMINISTICLANGUAGE SQLBEGIN/**//*----------------------------------------------------------------* Copyright (C) 2007; Huacius* 版權(quán)所有。** 存儲(chǔ)過(guò)程分頁(yè)**//-----------------------------------------------------------------------*/DECLARE STRSQLVARCHAR(6000);-- 主語(yǔ)句

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;-- 總記錄數(shù)close result;

if(ipagesize = 0) thenset ipagesize = 1000000;-- 每頁(yè)顯示數(shù)end if;

set ototalpages = (ototalrows - 1) / ipagesize + 1;-- 總頁(yè)數(shù)

if(iocurrentpageix < 1) thenset iocurrentpageix = 1;-- 當(dāng)前頁(yè)elseif(iocurrentpageix > ototalpages) thenset iocurrentpageix = ototalpages;end if;end if;

set opagestartrow = ipagesize * (iocurrentpageix -1) + 1;-- 每頁(yè)開(kāi)始數(shù)if(iocurrentpageix = ototalpages) thenset opageendrow = ototalrows;-- 每頁(yè)結(jié)束數(shù)elseset opageendrow = ipagesize * iocurrentpageix;end if;

if(iocurrentpageix > 1) thenset ohaspreviouspage = 1;-- 是否有上一頁(yè)elseset ohaspreviouspage = 0;end if;

if(iocurrentpageix < ototalpages) thenset ohasnextpage = 1;-- 是否有下一頁(yè)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