oracle存储过程分页

CREATE OR REPLACE PACKAGE PAC_PAGEDATE IS
TYPE CU_INFO IS REF CURSOR;
END;


CREATE OR REPLACE PROCEDURE PRO_PAGEDATE
(
TABLENAME IN VARCHAR2,--表名
ORDERBYNAME IN  VARCHAR2,--排序名
ROWSIZE IN NUMBER:=10,--显示行数
PAGENOW IN NUMBER,--当前页数
PAGECOUNT OUT NUMBER,--总页数
ROWCOUNTS OUT NUMBER,--总行数
CU_NAME OUT PAC_PAGEDATE.CU_INFO--定义游标输出数据集
)  IS
  SQLSTR VARCHAR2(200);--SQL语句
  STARTROW NUMBER;  --开始的行数
  ENDROW NUMBER;  --结尾的行数
  BEGIN
     
      STARTROW:=ROWSIZE*(PAGENOW-1);
      ENDROW:=ROWSIZE*PAGENOW;
    SQLSTR:='SELECT * FROM (SELECT T.*,ROWNUM R FROM (SELECT * FROM '||TABLENAME||') T WHERE ROWNUM<='||ENDROW||') WHERE R>='||STARTROW||'ORDER BY '|| ORDERBYNAME;
    OPEN CU_NAME FOR SQLSTR;
      SELECT COUNT(*) INTO ROWCOUNTS FROM GRADE;
      IF  MOD(ROWCOUNTS,ROWSIZE)=0 THEN PAGECOUNT:=ROWCOUNTS/ROWSIZE;
      ELSE PAGECOUNT:=ROWCOUNTS/ROWSIZE+1;
      END IF;
  END;

 

转载于:https://www.cnblogs.com/LoVeSW/p/3420436.html