实例讲解"Oracle"数据库的分页显示

3/18/2008来源:Oracle教程人气:6607


Create PROCEDURE pageTest --用于翻页的测试

--需要把排序字段放在第一列

(

@FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值

@LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值

@isNext bit=null, --true 1 :下一页;false 0:上一页

@allCount int output, --返回总记录数

@pageSize int output, --返回一页的记录数

@CurPage int --页号(第几页)0:第一页;-1最后一页。

)


AS


if @CurPage=0

begin

--统计总记录数

select @allCount=count(ProductId) from Product_test


set @pageSize=10

--返回第一页的数据

select top 10

ProductId,

ProductName,

Introduction

from Product_test order by ProductId

end


else if @CurPage=-1


select * from

(select top 10 ProductId,

ProductName,

Introduction


from Product_test order by ProductId desc ) as aa

order by ProductId

else


begin

if @isNext=1

--翻到下一页

select top 10 ProductId,

ProductName,

Introduction

from Product_test where ProductId > @LastID order by ProductId


else

--翻到上一页

select * from

(select top 10 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 varchar2, --the talbe name

                       v_order_field varchar2,--the order field

                       v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank.

                       --v_sql_select  varchar2, --the select sql for procedure

                       --v_sql_count  varchar2, --the count sql for procedure

                       --v_out_recordcount OUT int, --the num of return rows

                       p_cursor OUT refcursor_pkg.return_cursor) as

 v_sql     varchar2(3000); --the sql for select all rows of list

 v_sql_count  varchar2(3000); --the count sql for procedure

 v_sql_order  varchar2(2000); --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-1)*v_page_size;

  v_startrownum:=v_endrownum - v_page_size + 1;

 else

  v_endrownum:= v_current_page * v_page_size;

  v_startrownum := v_endrownum - v_page_size + 1;

 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;