PLSQL实现分页查询

  PROCEDURE SHOWLIST(PAGESIZE in integer,
                     PAGENOW  in integer,
                     KEYWORD  varchar2,
                     
                     ROWCOUNT_ out integer,
                     PAGECOUNT out integer,
                     P_RESULT  OUT PLAT_CONSTANT.RESULTSET)
  
   IS
    EXESQL VARCHAR2(1000);
    DOWN   INTEGER := (PAGENOW - 1) * PAGESIZE + 1;
    TOP    INTEGER := PAGENOW * PAGESIZE;
    HOW    VARCHAR2(100);
  BEGIN
  
    HOW := '1=1';
  
    IF KEYWORD IS NOT NULL AND KEYWORD <> '-1' THEN
      HOW := ' NAME LIKE ''%' || KEYWORD || '%'' ';
    END IF;
  
    EXESQL := ' SELECT * FROM 
                       (SELECT  A.* ,ROWNUM RN  FROM 
                                (SELECT ID , NAME , REMARK  FROM  EX_SYS_ROLE WHERE YXBZ=1 AND ' || HOW ||
              '  ) A      
                       WHERE ROWNUM <= ' || TOP || ' ) 
                WHERE RN >= ' || DOWN || '';
  
    --把游标和sql语句关联
    OPEN P_RESULT FOR EXESQL;
  
    --计算 rowCount_ 和 pageCount
  
    EXESQL := 'SELECT COUNT(*) FROM  EX_SYS_ROLE  WHERE YXBZ=1 AND ' || HOW;
  
    --执行并赋值
    EXECUTE IMMEDIATE EXESQL
      INTO ROWCOUNT_;
  
    --计算pageCount
    IF MOD(ROWCOUNT_, PAGESIZE) = 0 THEN
      PAGECOUNT := ROWCOUNT_ / PAGESIZE;
    ELSE
      PAGECOUNT := trunc(ROWCOUNT_ / PAGESIZE) + 1;
    END IF;
  END;

 

转载于:https://www.cnblogs.com/jyyjava/p/5009244.html