DB2存储过程分页测试

 

CREATE PROCEDURE TEST ( IN IN_START SMALLINT,
                        IN IN_SIZE SMALLINT )
        DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- DB2下通过传递参数控制显示记录的范围

-- SQL 存储过程
        -- IN_START 记录起点
        -- IN_SIZE  记录条数
------------------------------------------------------------------------
P1: BEGIN
        -- 声明游标
        DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT *
          FROM (SELECT ROW_NUMBER() OVER() as ROW_NO, T.* FROM TOPICIS.DM_HY AS T) AS TEMP
         WHERE TEMP.ROW_NO > IN_START
           AND TEMP.ROW_NO < IN_START + IN_SIZE;

        -- 游标对客户机应用程序保持打开
        OPEN cursor1;
END P1