熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 16:01:04  來源: 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.