本文介紹了使用DB2的存儲(chǔ)過程進(jì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,-- 頁尺寸 如果為0 默認(rèn)返回前一百萬條數(shù)據(jù)可以認(rèn)為是返回所有數(shù)據(jù)
         INOUT IOCURRENTPAGEIX INTEGER,-- 輸入和輸出：當(dāng)前頁
         OUT OPAGESTARTROW INTEGER,-- 輸出：當(dāng)前開始行
         OUT OPAGEENDROW INTEGER,-- 輸出：當(dāng)前結(jié)束行
         OUT OTOTALROWS INTEGER,-- 輸出：當(dāng)前總記錄數(shù)
         OUT OHASPREVIOUSPAGE INTEGER,-- 輸出：是否有上一頁
         OUT OHASNEXTPAGE INTEGER,-- 輸出：是否有下一頁
         OUT OTOTALPAGES INTEGER,-- 輸出：總頁數(shù)
         OUT OERROR VARCHAR(100))-- 輸出：錯(cuò)誤信息
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
BEGIN
/**//*----------------------------------------------------------------
 * Copyright (C) 2007  Huacius
 * 版權(quán)所有。
 *
 * 存儲(chǔ)過程分頁
 *
 *
//-----------------------------------------------------------------------*/
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;-- 總記錄數(shù)
    close result;

if(ipagesize = 0) then
set ipagesize = 1000000;-- 每頁顯示數(shù)
end if;

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

if(iocurrentpageix < 1) then
set iocurrentpageix = 1;-- 當(dāng)前頁
else
if(iocurrentpageix > ototalpages) then
set iocurrentpageix = ototalpages;
end if;
end if;

set opagestartrow = ipagesize * (iocurrentpageix -1) + 1;-- 每頁開始數(shù)
if(iocurrentpageix = ototalpages) then
set opageendrow = ototalrows;-- 每頁結(jié)束數(shù)
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