Oracle paging query Stored Procedure

• table creation statement

  Create   table   'A' ('name'   char  ( 64 ) collate utf8_bin  not   null   default  ''  , 'id'   tinyint  ( 1 )  not   null   default   '  0   ' ,   Primary   key   ('id');  

• Package Description and package body for paging Query

 

 Create   Or   Replace Package paginating_a_pack Is    --  Author: Administrator    --  Created: 17:02:46    --  Purpose: query data in Table A by PAGE      --  Public type declarations Type cur Is RefCursor  ; 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: -- pagination query Table A value assignment cursor ********************************* **************************  */   Begin     Open P_cursor For          Select Row_n.id, row_n.name, row_n.rownum _--  Name into a_name        From ( Select Row _. *  , 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: -- Query Table A by PAGE ********************************* **************************  */  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 of paging query Stored Procedure

 
Call paginating_a_pack.paginating_a_app (1,10);