DB2中翻页存储过程实例

使用DB2数据库的过程中，存储过程是一定会遇到的问题，本文将为您详细解疑DB2数据库中翻页存储过程，供您参考，希望能对您有所启迪。

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)
 *
 * 创建标识：[email protected](2006-5-1修改)
 *   #p#
//———————————————————————–*/

  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;#p#

—
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