存储过程翻页

最新推荐文章于 2021-04-09 00:21:23 发布

steven__zhao

最新推荐文章于 2021-04-09 00:21:23 发布

阅读量404

点赞数

分类专栏： DB 文章标签：存储 types email

本文链接：https://blog.csdn.net/steven__zhao/article/details/1373252

版权

DB 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.Define package

CREATE1 OR REPLACE1 PACKAGE "TYPES"
AS
TYPE cursor_type IS REF CURSOR;
END;

2.Define Procedure

CREATE1 OR REPLACE1 PROCEDURE 1 P_USR_S

(
ID_IN              IN      TBL_USR.ID%TYPE,
CD_IN     IN      TBL_USR.CD%TYPE,
REF_CURSOR         OUT     TYPES.CURSOR_TYPE,
PAGE_NUMBER_IN     IN      NUMBER,
TOTAL_RECORD_IN IN      NUMBER
)
AS
V_ROWSTART          NUMBER;
V_ROWEND            NUMBER;
BEGIN1
      V_ROWSTART := USF_CALC_ROW_START(PAGE_NUMBER_IN, TOTAL_RECORD_IN);
      V_ROWEND   := USF_CALC_ROW_END(PAGE_NUMBER_IN, TOTAL_RECORD_IN);

OPEN1 REF_CURSOR FOR
SELECT1 ID,
          CD,
          NM,
          EMAIL,
          VERSION,
          TOTAL_PAGE
         FROM1
         (
          SELECT1 ROWNUM ROWNUMBER, VW.*
              FROM1
                (
                  SELECT1
                         COUNT(*) OVER () TOTAL_PAGE ,
                          U.id,
                          U.cd,
                          U.nm,
                          U.VERSION
                FROM1 TBL_USR U
                WHERE1 U.CD like CD_IN
                ORDER1 BY CD
                )VW
              WHERE1 ROWNUM < V_ROWEND
            )
         WHERE1 ROWNUMBER >= V_ROWSTART;

END;