我來分享個:带排序的oracle分页存储过程 /* 带排序的oracle分页存储过程 豆豆网 技术应用频道 2007年05月13日 社区交流本文详细介绍带排序的oracle分页存储过程 输入order by 的sequence是,应该为“ desc”或者“ asc” 若输入两个order by则,v_order_field=" a[sequence] ,order by b " */ 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; --责编:豆豆技术应用
/*
带排序的oracle分页存储过程
豆豆网 技术应用频道 2007年05月13日 社区交流本文详细介绍带排序的oracle分页存储过程
输入order by 的sequence是,应该为“ desc”或者“ asc” 若输入两个order by则,v_order_field=" a[sequence] ,order by b "
*/
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; --责编:豆豆技术应用