DB2分页存储过程

CREATE PROCEDURE INTIER_COMMONALITY_PAGE_SELECT_LIST_01 ( 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,默认为20 INOUT IOCURRENTPAGEIX INTEGER, -- 输入和输出：当前页 OUT OPAGESTARTROW INTEGER, -- 输出：当前开始行 OUT OPAGEENDROW INTEGER, -- 输出：当前结束行 OUT OTOTALROWS INTEGER, -- 输出：当前总记录数 OUT OHASPREVIOUSPAGE INTEGER, -- 输出：是否有上一页 OUT OHASNEXTPAGE INTEGER, -- 输出：是否有下一页 OUT OTOTALPAGES INTEGER, -- 输出：总页数 OUT OERROR VARCHAR(1000) -- 输出：错误信息 ) RESULT SETS 1 MODIFIES SQL DATA NOT DETERMINISTIC LANGUAGE SQL P1: BEGIN ------------------------------------------------------------------------ -- SQL 存储过程 ------------------------------------------------------------------------ DECLARE STRSQL VARCHAR(6000); -- 声明游标 DECLARE RESULT CURSOR WITH RETURN TO CALLER FOR S2; DECLARE EXIT HANDLER FOR SQLEXCEPTION-- 异常捕获 BEGIN SET OERROR = 'ERROR:' || STRSQL; END; -- 查询条件 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; -- 如果每页显示大小参数为NULL时,把每页大小设置为0 SET IPAGESIZE = COALESCE(IPAGESIZE, 0); -- 如果每页显示大小为0时，设置每页显示大小为20 IF(IPAGESIZE = 0) THEN SET IPAGESIZE = 20; END IF; -- 设置总页数 SET OTOTALPAGES = (OTOTALROWS - 1) / IPAGESIZE + 1; -- 如果当前页参数为NULL时,把当前页设置为0 SET IOCURRENTPAGEIX = COALESCE(IOCURRENTPAGEIX, 0); -- 设置当前页 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)); set OERROR = STRSQL; PREPARE S2 FROM STRSQL; -- 游标对调用者保持打开 OPEN RESULT; END P1