[Oracle]pl/sql---Pagination process Demo

This SQL can be used to query a table for a specific location in the record

--Query method gets pagination of the statement select *from  

--Using paging to write stored procedures

--1 first to create a package that defines the cursor type

Create or replace package fenyepackage as  

--2 the basic process

Create or Replace procedure Fenye (tablename in Varchar2,--  table name pagesize in    number,--how much per page pagenow in  number,< c3/>--Current page myrows out number,     --How many records mypageconnt out numbers,-   -How many pages l_cursor out Fenyepackage.fenye_cursor-- Cursor) is--the position where the beginning and end of the record is obtained V_begin number:= (pagenow-1) *pagesize+1; V_end number:=pagenow*pagesize; V_sql varchar (1000); Begin v_sql:= ' Select Bookid,bookname,pubhouse from (select T1.*,rownum rn from (select *from ' | | tablename| | ') T1 where rownum<= ' | | v_end| | ') where rn >= ' | | V_begin; Open l_cursor for V_sql; V_sql:= ' SELECT COUNT (*) from ' | | TableName Execute immediate v_sql into myrows; If mod (myrows,pagesize) =0 then   mypageconnt:=myrows/pagesize; else    mypageconnt:=myrows/pagesize+1; end If;   

--3 using procedure Test

Create or Replace procedure Fenyeceshi (tablename in varchar2,pagesize in Number,pagenow on number) is v_myrows number; V_mypagecount number; V_bookid Books.bookid%type; V_bookname Books.bookname%type; V_pubhouse Books.pubhouse%type; L_cursor Fenyepackage.fenye_cursor; Begin    Fenye (tablename,pagesize,pagenow,v_myrows,v_mypagecount,l_cursor);--use a loop to read a record of a specific page  loop      fetch L _cursor into V_bookid,v_bookname,v_pubhouse;     --not found to be joined together to write     exit when L_cursor%notfound;     Dbms_output.put_line (' book ID is: ' | | v_bookid| | '  The name is: ' | | v_bookname| | '  Publishing house is ' | | V_pubhouse);   End Loop;   Dbms_output.put_line (' Total record is: ' | | v_myrows| | ' Article ');   

sql> exec Fenyeceshi (' books ', 2,2);   The book ID is: 3 the  name is: Angle  publishing house is 20 book ID is: 4  name is: Anglele  publishing House is 10 Total records: 4 Total Pages: 2 pages   

？？ : This will get the result, but the main process is very confused about whether the cursor is closed.
It's normal if it's not closed, and it'll go wrong if it's closed.
This problem, to be solved later?

sql> exec Fenyeceshi (' books ', 2,2);   Begin Fenyeceshi (' books ', 2,2); End;   ORA-01001: Invalid cursor ORA-06512: In "SCOTT." Fenyeceshi ", line ORA-06512: On line 2

This article is derived from "Orangleliu notebook " Blog, be sure to keep this source http://blog.csdn.net/orangleliu/article/details/38309427

[Oracle]pl/sql---Pagination process Demo