實例講解Oracle數據庫的分頁顯示

　　Create PROCEDURE pageTest 用於翻頁的測試
需要把排序字段放在第一列
(
@FirstID nvarchar()=null 當前頁面裡的第一條記錄的排序字段的值
@LastID nvarchar()=null 當前頁面裡的最後一條記錄的排序字段的值
@isNext bit=null true :下一頁false :上一頁
@allCount int output 返回總記錄數
@pageSize int output 返回一頁的記錄數
@CurPage int 頁號（第幾頁）第一頁最後一頁
)
AS
if @CurPage=
begin
統計總記錄數
select @allCount=count(ProductId) from Product_test
set @pageSize=
返回第一頁的數據
select top
ProductId
ProductName
Introduction
from Product_test order by ProductId
end
else if @CurPage=
select * from
(select top ProductId
ProductName
Introduction
from Product_test order by ProductId desc ) as aa
order by ProductId
else
begin
if @isNext=
翻到下一頁
select top ProductId
ProductName
Introduction
from Product_test where ProductId > @LastID order by ProductId
else
翻到上一頁
select * from
(select top ProductId
ProductName
Introduction
from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId
end
**************************************************************************
CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size　　int the size of a page of list
v_current_page int the current page of list
v_table_name varchar the talbe name
v_order_field　varcharthe order field
v_order_sequence varcharthe order sequence should by _descor _asc_is blank
v_sql_select　 varchar the select sql for procedure
v_sql_count　　varchar the count sql for procedure
v_out_recordcount OUT int the num of return rows
p_cursor OUT refcursor_pkgreturn_cursor) as
　v_sql　　　　 varchar(); the sql for select all rows of list
　v_sql_count　 varchar(); the count sql for procedure
　v_sql_order　 varchar(); the order of list
　v_count　　　 int; the amount rows fo original list
　v_endrownum　 int; the end row num of the current page
　v_startrownum int; the start row num of the current page
BEGIN
　set the order of list
　if　v_order_field!=NO then
　　v_sql_order := ORDER BY || v_order_field || ||v_order_sequence;
　else
　　 v_sql_order :=;
　end if;
　catch the amount rows of list
　v_sql_count:=SELECT COUNT(ROWNUM) FROM ||v_table_name;
　execute immediate v_sql_count into v_count;
　 v_out_recordcount := v_count;
　set the value of start and end row
　if v_order_sequence=desc then
　　v_endrownum:=v_count(v_current_page)*v_page_size;
　　v_startrownum:=v_endrownum v_page_size + ;
　else
　　v_endrownum:= v_current_page * v_page_size;
　　v_startrownum := v_endrownum v_page_size + ;
　end if;
　the sql for page slide
　v_sql := SELECT * FROM (SELECT ||v_table_name||* rownum rn FROM ||v_table_name|| WHERE rownum <= ||
　　　　　 to_char(v_endrownum) || || v_sql_order||)　WHERE rn >= ||
　　　　　 to_char(v_startrownum)|| ||v_sql_order;
　open p_cursor for v_sql;
END TABLEPAGE_SELECT;

From:http://tw.wingwit.com/Article/program/Oracle/201311/17677.html