ORACLE分页查询存储过程

• 建表语句

CREATE TABLE `a` (

  `name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `id` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ;

• 分页查询的包声明和包体

 

create or replace package PAGINATING_A_Pack is

  -- Author  : ADMINISTRATOR
  -- Created : 2013/1/13 17:02:46
  -- Purpose : 分页查询 表A 的数据
  
  -- Public type declarations
  TYPE CUR IS REF CURSOR;
  call PAGINATING_A_Pack.PAGINATING_A_APP(1,10);
  PROCEDURE PAGINATING_A_P(
       P_CURSOR OUT PAGINATING_A_Pack.CUR,
       startno IN INTEGER ,
       getnum IN INTEGER );
  procedure PAGINATING_A_APP
       (startno IN INTEGER ,
        getnum IN INTEGER);

end PAGINATING_A_Pack;

create or replace package body PAGINATING_A_Pack is

PROCEDURE PAGINATING_A_P(
       P_CURSOR OUT PAGINATING_A_Pack.CUR,
       startno IN INTEGER ,
       getnum IN INTEGER ) IS
 /*************************************************************** 
  *NAME : [Name]
  *PURPOSE : --分页查询 A表
 赋值游标
  ************************************************************/ 
BEGIN
   OPEN p_CURSOR FOR
        select row_n.ID, row_n.name, row_n.rownum_ --name INTO a_name
      FROM (SELECT row_.*, ROWNUM rownum_
              FROM (select * from A) row_
             WHERE ROWNUM <= startno + getnum) row_n
     WHERE rownum_ >= startno;
END;


procedure PAGINATING_A_APP
       (startno IN INTEGER ,
        getnum IN INTEGER) IS
 /*************************************************************** 
  *NAME : [Name]
  *PURPOSE : --分页查询 A表
 
  分页查询************************************************************/         
c PAGINATING_A_Pack.CUR;
type A_ROW is record(
    id   A.id%type,
    name A.name%type,
    num  integer);
  a_r A_ROW;
BEGIN
       PAGINATING_A_P(c,startno,getnum);

       LOOP
       FETCH c INTO a_r;
       EXIT WHEN c%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('=NAME='||a_r.name);
       END LOOP;
       CLOSE c;
END;
end PAGINATING_A_Pack;

• 分页查询存储过程的调用

call PAGINATING_A_Pack.PAGINATING_A_APP(1,10);