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 isPROCEDURE 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);