在网上众多翻页存储过程中,选择了一个通用的存储过程,以下是改成DB2中的翻页存储过程:
class="dp-sql">
- CREATE PROCEDURE KYJT.USP_A_FY(IN PTBLNAME VARCHAR(1000), --表名
- IN PSHOWNAME VARCHAR(1000), -- 需要显示字段名
- IN PFLDNAME VARCHAR(1000), --字段名(主表主键,不可重复)
- IN PPAGESIZE INTEGER, -- 页尺寸 如果为0 默认返回前一千万条数据 可以认为是返回所有数据
- IN PPAGEINDEX INTEGER,--页码
- IN PORDERTYPE INTEGER, --设置排序类型, 非 0 值则降序(按主键排序)
- IN PSTRWHERE VARCHAR(1000) --查询条件 (注意: 不要加 WHERE)
- )
- MODIFIES SQL DATA
- DETERMINISTIC
- LANGUAGE SQL
- BEGIN
- /**//*----------------------------------------------------------------
- * Copyright (C) 2006 笑疯
- * 版权所有。
- *
- * 过程功能描述:
- * 多功能通用翻页查询语句(DB2)
- *
- * 创建标识:longping520@126.com(2006-5-1修改)
- *
- //-----------------------------------------------------------------------*/
- DECLARE C_STRSQL VARCHAR(6000); -- 主语句
- DECLARE C_STRTMP VARCHAR(100); -- 临时变量
- DECLARE C_STRORDER VARCHAR(400); -- 排序类型
- DECLARE bill_task CURSOR WITH RETURN TO CALLER FOR S1;
- --SET PSHOWNAME = PSHOWNAME || ',' || PFLDNAME;
- IF (PPAGEINDEX=0) THEN
- SET PPAGEINDEX = 1;
- END IF;
- IF (PPAGESIZE=0) THEN
- SET PPAGESIZE = 10000000;
- END IF;
- -- 设置排序规则
- IF (PORDERTYPE<>0) THEN
- SET C_STRTMP = '<(SELECT MIN';
- SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' DESC';
- ELSE
- SET C_STRTMP = '>(SELECT MAX';
- SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' ASC';
- END IF;
- --
- SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM '|| PTBLNAME || ' WHERE ' || PFLDNAME || ' ' || C_STRTMP || '( '|| 'TBLTMP.TTT ) FROM (SELECT ' || PFLDNAME || ' AS TTT FROM ' || PTBLNAME || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR((PPAGEINDEX-1)*PPAGESIZE) || ' ROWS ONLY) AS TBLTMP)' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';
- --
- IF (PSTRWHERE <> '') THEN
- SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM '|| PTBLNAME || ' WHERE ' || PFLDNAME || ' ' || C_STRTMP || '( '|| 'TBLTMP.TTT ) FROM (SELECT ' || PFLDNAME || ' AS TTT FROM ' || PTBLNAME || ' WHERE ' || PSTRWHERE || ' '|| C_STRORDER || ' FETCH FIRST ' || CHAR((PPAGEINDEX-1)*PPAGESIZE) || ' ROWS ONLY) AS TBLTMP) AND ' || PSTRWHERE || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';
- END IF;
- --
- IF (PPAGEINDEX=1) THEN
- SET C_STRTMP = '';
- IF (PSTRWHERE <> '') THEN
- SET C_STRTMP = ' WHERE ' || PSTRWHERE;
- END IF;
- SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM '|| PTBLNAME || ' ' || C_STRTMP || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';
- END IF;
- PREPARE S1 FROM C_STRSQL;
- --OPEN C_STRSQL;
- --EXECUTE S1;
- OPEN bill_task;
- END