|
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;
|
