快捷搜索:

带排序的oracle分页存储过程 - 存储过程

输入order by 的sqeuence是,应该为“ 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_startrownum)||' '||v_sql_order;

open p_cursor for v_sql;

END TABLEPAGE_SELECT;

您可能还会对下面的文章感兴趣: