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_sizeint 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_fieldvarcharthe 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_countvarchar 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
ifv_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;
