orcale _分页查询存储过程

---分页
DECLARE
  RNUM      NUMBER := 12;--用户输入每页显示数量
  YEMA      NUMBER := 4;---第几页
  S_UM      NUMBER;---截止行
  S_TAR     NUMBER;---开始行
  num_ber   NUMBER;---行数
  
  V_BOX_NO  APP_WMS.ST_SUMMARY_BARCODE.BOX_NO%TYPE;
  V_NAME_NO APP_WMS.ST_SUMMARY_BARCODE.ITEM_NO%TYPE;
  CURSOR FENYE IS
    SELECT BOX_NO,
           ITEM_NO,
            rownum
      FROM (SELECT ROWNUM RN,
                   DE.*
              FROM (SELECT *
                      FROM APP_WMS.ST_SUMMARY_BARCODE B
                     WHERE ROWNUM <= S_UM) DE)
     WHERE RN >= S_TAR;
BEGIN
  S_UM  := RNUM * YEMA;
  S_TAR := (RNUM * (YEMA - 1))+1;
   OPEN FENYE;
   
  LOOP
     FETCH FENYE
      INTO V_BOX_NO,
           V_NAME_NO,
           num_ber ; 
    EXIT WHEN FENYE%NOTFOUND; --全部取出就退出循环
    DBMS_OUTPUT.PUT_LINE(num_ber ||' '||V_BOX_NO   ||'  '||    V_NAME_NO||' ');
  END LOOP;
  CLOSE FENYE;
END;