DB2存储过程分页测试
CREATE PROCEDURE TEST ( IN IN_START SMALLINT,
IN IN_SIZE SMALLINT )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- DB2下通过传递参数控制显示记录的范围
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;
-- 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
OPEN cursor1;
END P1
本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/40042，如需转载请自行联系原作者
