存储过程翻页

1.Define package

CREATE   OR REPLACE  PACKAGE "TYPES"
AS
 TYPE cursor_type IS REF CURSOR;
END;

2.Define Procedure


CREATE  OR REPLACE  PROCEDURE   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;
BEGIN
      V_ROWSTART := USF_CALC_ROW_START(PAGE_NUMBER_IN, TOTAL_RECORD_IN);
      V_ROWEND   := USF_CALC_ROW_END(PAGE_NUMBER_IN, TOTAL_RECORD_IN);

 OPEN  REF_CURSOR FOR
  SELECT   ID,
          CD,
          NM,
          EMAIL,
          VERSION,
          TOTAL_PAGE
         FROM
         (
          SELECT  ROWNUM ROWNUMBER, VW.*
              FROM
                (
                  SELECT
                         COUNT(*) OVER () TOTAL_PAGE ,
                          U.id,
                          U.cd,
                          U.nm,
                          U.VERSION
                FROM   TBL_USR U
                WHERE U.CD like CD_IN
                ORDER  BY CD
                )VW
              WHERE ROWNUM < V_ROWEND
            )
         WHERE ROWNUMBER >= V_ROWSTART;
 
END;